Shulou(Shulou.com)06/01 Report--

This article will explain in detail what are the five common constraints in sql. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Sql integrity constraints include: non-null constraints, unique constraints, primary key constraints, foreign key constraints, conditional constraints, etc.

Integrity constraint is a means to ensure that the changes made by users will not destroy the consistency of data, and is a means to protect the correctness and compatibility of data.

Five common constraints:

NOT NULL: non-null constraint C, specified columns are not allowed to be null

UNIQUE: unique constraint U, there are no duplicate values in the specified column, or each value or set of values in the table will be unique

PRIMARY KEY: primary key constraint P, which 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, so that there is a dependency and can point to columns that reference themselves.

CHECK: conditional constraint C, which specifies whether the column satisfies a condition

Non-null constrained NK

Create table member (mid number, name varchar2 not null)

Insert null error:

SQL > insert into member (mid,name) values (1 NAME null); * error in line 1: ORA-01400: unable to insert 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)

Insert error report

SQL > insert into member (mid,name,email) values.

1 line has been created.

SQL > insert into member (mid,name,email) values; insert into member (mid,name,email) values: error in line 1: ORA-00001: violation of unique constraints (SCOTT.SYS_C0010891)

Assign a name to a constraint

This error does not tell the user exactly which field is wrong, as the previous non-empty constraint did. We can give the constraint a name.

Create table member (mid number, name varchar2 (200) not null, email varchar2 (50), constraint uk_email unique (email))

Primary key constraint competition

Primary key constraint = non-null 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))

Insert the wrong data report error:

SQL > insert into member (mid,name,email) values (1)

1 line has been created

SQL > insert into member (mid,name,email) values; insert into member (mid,name,email) values: error in line 1: ORA-00001: violation of unique constraints (SCOTT.PK_NUMBER)

Check constraint CK

Create table member (mid number, name varchar2 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 ('male', 'female')

Insert error data report error:

SQL > insert into member (mid,name,age,sex,email) values; insert into member (mid,name,age,sex,email) values; error in line 1: ORA-02290: violation of check constraints (SCOTT.CK_SEX)

Primary foreign key constraint FK

Create table member (mid number, name varchar2) 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 schedule

Insert into member (mid,name) values (1); insert into member (mid,name) values (2); insert into advice (adid,content,mid) values (1); insert into advice (adid,content,mid) values (2); commit

Insert an mid data that is not in the parent table

SQL > insert into advice (adid,content,mid) values (3Magnetics Magazine 3); insert into advice (adid,content,mid) values (3recovertestestetry3) * error occurred in line 1: ORA-02291: violation of complete constraint (SCOTT.FK_MID)-parent keyword not found

The adoption of primary foreign key constraints will cause new problems in table deletion and data deletion.

To delete the parent table, you have to delete the child table first.

SQL > drop table member;drop table member * error occurred in line 1: ORA-02449: the unique / primary key in the table is referenced by a foreign key

The parent table is forced to be deleted, so that the purge option cannot be used, and the primary foreign key constraint in the word table no longer exists

The SQL > drop table member cascade constraint; table has been deleted. SQL > insert into advice (adid,content,mid) values

1 line has been created

Delete the data in the word table first by deleting the data from the parent table

Error occurred in line 1 of SQL > delete from member where mid=1;delete from member where mid=1*: ORA-02292: violation of complete constraint (SCOTT.FK_MID)-child record found

In addition to this method, cascade operation clauses can also be set.

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 attached data will also delete the data in the word table.

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)

In this way, the data in the parent table is deleted and the corresponding associated fields in the child table will be set to empty.

SQL > delete from member where mid=1; deleted 1 line. 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 EMPPK_EMP P EMPPK_DEPT P DEPT

R foreign key constraint, C check constraint, Q unique constraint, P primary key constraint

Modify constraint

Increase constraint

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 approach

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; on what are the five common constraints in sql to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.