oracle约束

1.NOT NULL：非空约束
SQL> CREATE TABLE person(pid  NUMBER, name VARCHAR(30) NOT NULL);


Table created.


SQL> INSERT INTO person(pid,name) VALUES (11,'张三');


1 row created.


SQL> INSERT INTO person(pid,name) VALUES (11,''); 
INSERT INTO person(pid,name) VALUES (11,'')
                                        *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."PERSON"."NAME")




2.PRIMARY KEY：主键约束,不能重复，不能为空
SQL> DROP TABLE person; 


Table dropped.


SQL> CREATE TABLE person(pid NUMBER PRIMARY KEY, name VARCHAR(30) NOT NULL);


Table created.


SQL> INSERT INTO person(pid,name) VALUES (11,'张三');


1 row created.


SQL> INSERT INTO person(pid,name) VALUES (11,'李四');
INSERT INTO person(pid,name) VALUES (11,'李四')
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C0011314) violated




SQL> INSERT INTO person(pid,name) VALUES ('','张三');
INSERT INTO person(pid,name) VALUES ('','张三')
                                     *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."PERSON"."PID")




3.UNIQUE：唯一约束，值不能重复（空值除外）
SQL> DROP TABLE person; 


Table dropped.


SQL> CREATE TABLE person(pid NUMBER PRIMARY KEY NOT NULL,name VARCHAR(30) NOT NULL,tel VARCHAR(50) UNIQUE);


Table created.


SQL> INSERT INTO person(pid,name,tel) VALUES (11,'张三','1234567');


1 row created.


SQL> INSERT INTO person(pid,name,tel) VALUES (12,'李四','1234567');
INSERT INTO person(pid,name,tel) VALUES (12,'李四','1234567')
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C0011318) violated




SQL> INSERT INTO person(pid,name,tel) VALUES (13,'李四','');


1 row created.


SQL> INSERT INTO person(pid,name,tel) VALUES (14,'王麻子',''); 


1 row created.




4.CHECK：条件约束，插入的数据必须满足某些条件 


SQL> DROP TABLE person; 


Table dropped.


SQL> CREATE TABLE person(pid  NUMBER PRIMARY KEY NOT NULL, name VARCHAR(30) NOT NULL , tel VARCHAR(50) NOT NULL UNIQUE ,age NUMBER CHECK(age BETWEEN 0 AND 150));


Table created.


SQL> INSERT INTO person(pid,name,tel,age) VALUES (11,'张三','1234567',30);


1 row created.


SQL> INSERT INTO person(pid,name,tel,age) VALUES (11,'张三','1234567',200);
INSERT INTO person(pid,name,tel,age) VALUES (11,'张三','1234567',200)
*
ERROR at line 1:
ORA-02290: check constraint (SYS.SYS_C0011322) violated


5.Foreign Key：外键 
book 中的每一条记录表示一本书的信息，一本书的信息属一个人


SQL> CREATE TABLE book 
( 
 bid  NUMBER PRIMARY KEY NOT NULL , 
 name VARCHAR(50) , 
 pid  NUMBER REFERENCES person(pid) ON DELETE CASCADE 
);   2    3    4    5    6  


Table created.


SQL> INSERT INTO book(bid,name,pid) VALUES(1001,'JAVA',11) ; 


1 row created.


SQL> INSERT INTO book(bid,name,pid) VALUES(1001,'JAVA',12) ;
INSERT INTO book(bid,name,pid) VALUES(1001,'JAVA',12)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C0011326) violated