SQL integrity constraints include: 1. "NOT NULL" non-null constraint, the specified column is not allowed to have null values; 2. "UNIQUE" unique constraint, there are no duplicate values in the specified column; 3. "PRIMARY" KEY" primary key constraint, uniquely identifies each row of the table; 4. "FOREIGN KEY" foreign key constraint; 5. "CHECK" conditional constraint.
Integrity constraints are a means to ensure that the modifications made by users will not destroy the consistency of the data and to protect the correctness and compatibility of the data.
Five common constraints:
NOT NULL: non-null constraint C, the specified column does not allow null values
UNIQUE: unique constraint U, there are no duplicate values in the specified column, or each value or each group of values in the table will be unique
PRIMARY KEY: Primary key constraint P, uniquely identifies each row of the table, and NULL values are not allowed. A table can only have one primary key constraint
FOREIGN KEY: Foreign key constraint R. Columns in one table refer to columns in other tables, causing dependencies to exist and can point to references to themselves. Column
CHECK: Conditional constraint C, specifies whether the column satisfies a certain condition
Non-null constraint NK
create table member( mid number, name varchar2(200) not null );
Insert null error:
SQL> insert into member(mid,name) values(1,null); * 第 1 行出现错误: ORA-01400: 无法将 NULL 插入 ("SCOTT"."MEMBER"."NAME")
Unique constraint UK
drop table member purge;create table member( mid number, name varchar2(200) not null, email varchar2(50) unique );
Insertion error
SQL> insert into member(mid,name,email) values (1,'scott','scott@163.com');
1 row has been created.
SQL> insert into member(mid,name,email) values (2,'jack','scott@163.com'); insert into member(mid,name,email) values (2,'jack','scott@163.com') * 第 1 行出现错误: ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C0010891)
Specify a name for the constraint
This error does not accurately tell the user which field has a problem like the previous non-null constraint. We can specify a name for this constraint. A name
create table member( mid number, name varchar2(200) not null, email varchar2(50), constraint uk_email unique(email) );
Primary key constraint PK
Primary key constraint = non-empty constraint Unique constraint
create table member( mid number, name varchar2(200) not null, email varchar2(50), constraint pk_number primary key(mid), constraint uk_email unique(email) );
Inserting wrong data will report an error:
SQL> insert into member(mid,name,email) values (1,'jack','scott@163.com');
1 row created
SQL> insert into member(mid,name,email) values (1,'jack','jack@163.com'); insert into member(mid,name,email) values (1,'jack','jack@163.com') * 第 1 行出现错误: ORA-00001: 违反唯一约束条件 (SCOTT.PK_NUMBER)
Check constraint CK
create table member( mid number, name varchar2(200) not null, age number check(age between 0 and 200), sex varchar2(10), email varchar2(50), constraint pk_number primary key(mid), constraint uk_email unique(email), constraint ck_sex check(sex in('男','女')) );
Insert wrong data error:
SQL> insert into member(mid,name,age,sex,email) values (1,'Jack','300','无','jack@163.com'); insert into member(mid,name,age,sex,email) values (1,'Jack','300','无','jack@163.com') * 第 1 行出现错误: ORA-02290: 违反检查约束条件 (SCOTT.CK_SEX)
Primary foreign key constraint FK
create table member( mid number, name varchar2(200) not null, constraint pk_mid primary key(mid) ); create table advice( adid number, content clob not null, mid number, constraint pk_adid primary key(adid), constraint fk_mid foreign key(mid) references member(mid) );
Insert data correctly, mid already exists in the attached table
insert into member(mid,name) values (1,'Scott'); insert into member(mid,name) values (2,'Jack'); insert into advice(adid,content,mid) values (1,'test',1); insert into advice(adid,content,mid) values (2,'test',2); commit;
Insert a mid data that is not in the parent table
SQL> insert into advice(adid,content,mid) values (3,'test',3); insert into advice(adid,content,mid) values (3,'test',3) * 第 1 行出现错误: ORA-02291: 违反完整约束条件 (SCOTT.FK_MID) - 未找到父项关键字
Adopt Primary and foreign key constraints will cause new problems in table deletion and data deletion
To delete the parent table, you must first delete the child table
SQL> drop table member;drop table member * 第 1 行出现错误: ORA-02449: 表中的唯一/主键被外键引用
Forcibly delete the parent table, so you cannot use the purge option , and the primary and foreign key constraints in the word table will no longer exist
SQL> drop table member cascade constraint; 表已删除。 SQL> insert into advice(adid,content,mid) values (3,'test',3)；
1 row has been created
To delete the data of the parent table, first delete the data in the word table
SQL> delete from member where mid=1; delete from member where mid=1 * 第 1 行出现错误: ORA-02292: 违反完整约束条件 (SCOTT.FK_MID) - 已找到子记录
In addition to this method, you can also set cascade operation clauses
on delete cascade
create table advice( adid number, content clob not null, mid number, constraint pk_adid primary key(adid), constraint fk_mid foreign key(mid) references member(mid) on delete cascade );
In this case, deleting the appendix data will delete the word table The data in is also deleted.
on delete set null
create table advice( adid number, content clob not null, mid number, constraint pk_adid primary key(adid), constraint fk_mid foreign key(mid) references member(mid) on delete set null );
If you delete the data in the parent table, the corresponding associated fields in the child table will be set to null
SQL> delete from member where mid=1; 已删除 1 行。 SQL> select * from advice; ADID CONTENT MID ---------- ---------- ---------- test test 2
View constraints
SQL> select constraint_name,constraint_type,table_name from user_constraints; CONSTRAINT_NAME C TABLE_NAME ------------------------------ - ------------------------------ FK_DEPTNO R EMP PK_EMP P EMP PK_DEPT P DEPT
R foreign key constraints, C check constraints, Q unique constraints, P primary key constraints
Modify constraints
Add constraints
alter table member add constraint pk_mid primary key(mid); alter table member add constraint ck_age check(age between 0 and 200); alter table member modify (name varchar2(50) not null);
It should be noted that non-null constraints use the modify method
Disable/enable constraints
Non-foreign key
alter table member disable constraint ck_age; alter table member enable constraint ck_age;
Foreign Key
alter table member disable constraint pk_mid cascade;
Delete Constraint
alter table member drop constraint pk_mid cascade; alter table member drop constraint ck_age;
The above is the detailed content of What are the SQL integrity constraints?. For more information, please follow other related articles on the PHP Chinese website!