十四、ORACLE五种约束的使用

心已赠人 2022-04-14 04:55 154阅读 0赞

#                                ORACLE五种约束的使用 #

### 1、唯一性约束 ###

添加惟一性约束
    ALTER TABLE USERS ADD CONSTRAINT UQ_EMAIL UNIQUE (EMAIL)
    
    删除惟一性约束
    ALTER TABLE USERS DROP CONSTRAINT UQ_EMAIL
    
    重命名惟一性约束
    ALTER TABLE USERS RENAME CONSTRAINT SYS_C005188 TO UQ_EMAIL
    
    禁用/启用惟一性约束
    ALTER TABLE USERS DISABLE CONSTRAINT UQ_EMAIL

### 2、非空约束 ###

方式1.在创建表的时候就约束非空，如下：
    
    create table student (
    
    id number(5) primary key,
    
    name varchar2(20) not null
    
    );
    
    方式2.对于已存在的表添加非空约束，如下：
    
    alter table student modify name not null;

### 3、主键约束 ###

为表添加多列主键
    
    ALTER TABLE EMP ADD CONSTRAINT PK_EMP PRIMARY KEY(EMP_ID,EMP_NAME)

### 4、外键约束 ###

CREATE TABLE CUSTOMERS
    (
           CUSTOMER_ID NUMBER,
           CUSTOMER_NAME VARCHAR(20),
           CONSTRAINT PK_CUSTOMERS PRIMARY KEY(CUSTOMER_ID)
    )
    CREATE TABLE ORDERS
    (
           ORDER_ID NUMBER,
           CUSTOMER_ID NUMBER,
           PRODUCT_NAME VARCHAR(20),
           QUANTITY NUMBER,
           CONSTRAINT PK_ORDERS PRIMARY KEY(ORDER_ID)
    )
    
    ALTER TABLE ORDERS ADD CONSTRAINT FK_ORDERS_CUSTOMERS FOREIGN KEY(ORDER_ID ) REFERENCES CUSTOMERS(CUSTOMER_ID)

### 5、检查约束 ###

添加检查约束
    
    ALTER TABLE STUDENTS ADD CONSTRAINT CK_STUDENT_NAME CHECK (LENGTH(STUDENT_NAME) <= 4)
    
    ALTER TABLE STUDENTS ADD CONSTRAINT CK_SEX CHECK (SEX IN ('男','女'))

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center]: /images/20220414/68e2423e74fb4bf7bb96c0dd80fb4df9.png