What are the SQL integrity constraints?-Mysql Tutorial-php.cn

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.

What are the SQL integrity constraints?

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
    );

Copy after login

Insert null error:

SQL> insert into member(mid,name) values(1,null);
*
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SCOTT"."MEMBER"."NAME")

Copy after login

Unique constraint UK

drop table member purge;create table member(
    mid number,
    name varchar2(200) not null,
    email varchar2(50) unique
    );

Copy after login

Insertion error

SQL> insert into member(mid,name,email) values (1,&#39;scott&#39;,&#39;scott@163.com&#39;);

Copy after login

1 row has been created.

SQL> insert into member(mid,name,email) values (2,&#39;jack&#39;,&#39;scott@163.com&#39;);
insert into member(mid,name,email) values (2,&#39;jack&#39;,&#39;scott@163.com&#39;)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C0010891)

Copy after login

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)
    );

Copy after login

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)
    );

Copy after login

Inserting wrong data will report an error:

SQL> insert into member(mid,name,email) values (1,&#39;jack&#39;,&#39;scott@163.com&#39;);

Copy after login

1 row created

SQL> insert into member(mid,name,email) values (1,&#39;jack&#39;,&#39;jack@163.com&#39;);
insert into member(mid,name,email) values (1,&#39;jack&#39;,&#39;jack@163.com&#39;)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.PK_NUMBER)

Copy after login

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(&#39;男&#39;,&#39;女&#39;))    
   );

Copy after login

Insert wrong data error:

SQL> insert into member(mid,name,age,sex,email) values (1,&#39;Jack&#39;,&#39;300&#39;,&#39;无&#39;,&#39;jack@163.com&#39;);
insert into member(mid,name,age,sex,email) values (1,&#39;Jack&#39;,&#39;300&#39;,&#39;无&#39;,&#39;jack@163.com&#39;)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.CK_SEX)

Copy after login

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)
       );

Copy after login

Insert data correctly, mid already exists in the attached table

insert into member(mid,name) values (1,&#39;Scott&#39;);
insert into member(mid,name) values (2,&#39;Jack&#39;);
insert into advice(adid,content,mid) values (1,&#39;test&#39;,1);
insert into advice(adid,content,mid) values (2,&#39;test&#39;,2);
commit;

Copy after login

Insert a mid data that is not in the parent table

SQL> insert into advice(adid,content,mid) values (3,&#39;test&#39;,3);
insert into advice(adid,content,mid) values (3,&#39;test&#39;,3)
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SCOTT.FK_MID) - 未找到父项关键字

Copy after login

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: 表中的唯一/主键被外键引用

Copy after login

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,&#39;test&#39;,3)；

Copy after login

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) - 已找到子记录

Copy after login

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
       );

Copy after login

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      
 );

Copy after login

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

Copy after login

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

Copy after login

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);

Copy after login

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;

Copy after login

Foreign Key

alter table member disable constraint pk_mid cascade;

Copy after login

Delete Constraint

alter table member drop constraint pk_mid cascade;
alter table member drop constraint ck_age;

Copy after login

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!