What are the SQL integrity constraints?

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!