十四、ORACLE五种约束的使用

                               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 ('男','女'))