constraint

constraint	explain
NOT NULL	Non NULL constraint, specifying that all row data of a column cannot contain null values
UNIQUE	Unique constraint. All row data of the specified column or combination of columns must be unique
PRIMARY KEY	The primary key constraint is the unique identifier of each row of the table. All row data of the specified column or column combination must be unique
FOREIGN KEY	Foreign key constraint, a mandatory dependency on columns and reference columns
CHECK	Checking constraints, specifying a condition on the column that must be met

 

I

Not null (non NULL constraint): cannot be null or ''
1. The column value of the constraint cannot be NULL
2. Only column level constraints can be defined
3. The constraint name may not be specified

1.Create table emp_bak,appoint ename Cannot be NULL
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10) NOT NULL,--Add a non empty constraint
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2)
);
--test
INSERT INTO emp_bak(empno, ename) VALUES(1001, '');--Failed to add data, ename Cannot be empty
INSERT INTO emp_bak(empno, ename) VALUES(1002, 'Lynn');--Data added successfully
SELECT * FROM emp_bak;
--Delete table
DROP TABLE emp_bak;

 

II

Unique (unique constraint, unique key):
1. The column value of the constraint must be unique, but can be NULL
2. Both column level constraints (single column QNIQUE constraints) and table level constraints (multi column combined UNIQUE constraints) can be defined
3. It is recommended to specify the constraint name
4. When creating a unique constraint, INDEX index is added to the columns of all constraints by default to optimize query efficiency
5. A table can define multiple UNIQUE constraints at the same time
6. UNIQUE constraints can be defined for one or more columns

1.Create table emp_bak,appoint empno Must be unique and simultaneous ename Must be unique, no constraint name specified
CREATE TABLE emp_bak(
       empno NUMBER(4) UNIQUE,--Do not specify constraint name
       ename VARCHAR2(10) UNIQUE,--Do not specify constraint name
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2)
);
--test
INSERT INTO emp_bak(empno, ename) VALUES(1001, 'Lynn');
INSERT INTO emp_bak(empno, ename) VALUES(1001, 'Tom');--Failed to add, violating unique constraint(SCOTT.SYS_C0010811)
INSERT INTO emp_bak(empno, ename) VALUES(1002, 'Lynn');--Failed to add, violating unique constraint(SCOTT.SYS_C0010812)
--Delete table
DROP TABLE emp_bak;

2.Create table emp_bak,appoint empno Must be unique and simultaneous ename Must be unique, specify constraint name
CREATE TABLE emp_bak(
       empno NUMBER(4) CONSTRAINT emp_bak_empno_unique UNIQUE,--Specifies the constraint name(Column level constraints)
       ename VARCHAR2(10) CONSTRAINT emp_bak_ename_unique UNIQUE,--Specifies the constraint name(Column level constraints)
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2)
);
--amount to:
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_empno_unique UNIQUE(empno),--Specifies the constraint name(Table level constraints)
       CONSTRAINT emp_bak_ename_unique UNIQUE(ename)--Specifies the constraint name(Table level constraints)
);
--test
INSERT INTO emp_bak(empno, ename) VALUES(1001, 'Lynn');
INSERT INTO emp_bak(empno, ename) VALUES(1001, 'Tom');--Failed to add, violating unique constraint(SOCTT.EMP_BAK_EMPNO_UNIUQE)
INSERT INTO emp_bak(empno, ename) VALUES(1002, 'Lynn');--Failed to add, violating unique constraint(SOCTT.EMP_BAK_ENAME_UNIUQE)
--Delete table
DROP TABLE emp_bak;
SELECT * FROM emp_bak;

--Define combined unique constraints for multiple columns at the same time
1.Create table emp_bak,appoint empno,ename Combination unique
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_empnoandename_unique UNIQUE(empno, ename)--Combined constraints, only table level constraints can be defined
);
--test
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Lynn');
INSERT INTO emp_bak(empno, ename) VALUES(1001, 'Lynn');--Insert data succeeded
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Tom');--Insert data succeeded
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Lynn');--Violation of unique constraints(SOCTT.EMP_BAK_EMPNOANDENAME_UNIUQE)
--Delete table
DROP TABLE emp_bak;

 

III

Primary key: equivalent to UNIQUE + NOT NULL
1. The column value of the constraint must be unique and cannot be NULL
2. Both column level constraints (single column PK constraints) and table level constraints (multi column combined PK constraints) can be defined
3. It is recommended to specify the constraint name
4. When creating a unique constraint, an index is added to all constrained columns by default to optimize query efficiency
5. A table can only have one primary key constraint
6. primary key constraints can be defined for one or more columns

1.Create table emp_bak,appoint empno As primary key
CREATE TABLE emp_bak(
       empno NUMBER(4) CONSTRAINT emp_bak_empno_pk PRIMARY KEY,--empno Set as primary key(Column level constraints)
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2)
);
--amount to:
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_empno_pk PRIMARY KEY(empno)--empno Set as primary key(Table level constraints)
);
--test
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Lynn');
INSERT INTO emp_bak(empno, ename) VALUES('', 'Lynn');--Failed to insert data. The primary key cannot be empty
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Tom');--Failed to insert data. The primary key must be unique
--Delete table
DROP TABLE emp_bak;

--Define combined primary key constraints for multiple columns at the same time
1.Create table emp_bak,appoint empno,ename Combine as primary key
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_empno_pk PRIMARY KEY(empno, ename)--Combined constraints, only table level constraints can be defined
);
--test
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Lynn');
INSERT INTO emp_bak(empno, ename) VALUES(1001, 'Lynn');--Insert data succeeded
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Tom');--Insert data succeeded
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Lynn');--Failed to insert data. The primary key must be unique
--Delete table
DROP TABLE emp_bak;

 

IV

Foreign key constraint:
1. The value of the foreign key column must be within the range of the reference column value or NULL
2. The column referenced by the foreign key must be a primary key or unique key
3. When the primary key value of a primary key table is referenced by a foreign key table, the primary key table record cannot be deleted

4. It is recommended to specify the constraint name
5. You can define both column level constraints and table level constraints
6. A table can have multiple foreign key constraints

Cascade delete ON DELETE CASCADE
Cascade clear ON DELETE SET NULL

1.Create table emp_bak,appoint deptno Foreign key, reference dept Tabular deptno Primary key column
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2) CONSTRAINT emp_bak_deptno_fk REFERENCES dept(deptno)
);
--amount to
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno)
);
--test
INSERT INTO emp_bak(deptno, ename) VALUES(10, 'Lynn');
INSERT INTO emp_bak(deptno, ename) VALUES(NULL, 'Lynn');--Insert data succeeded
INSERT INTO emp_bak(deptno, ename) VALUES(50, 'Lynn');--Violation of integrity constraints(SOCTT.EMP_BAK_DEPTNO_FK)Parent key not found because dept In the table dept No 50
DELETE FROM dept WHERE deptno = 10;--Failed to delete data, violating the integrity constraint(SCOTT,FK_DEPTNO)-Child record found
--Delete table
DROP TABLE emp_bak;

--cascading deletion
1.Create table emp_bak,appoint deptno Foreign key, reference dept Tabular deptno Primary key column; When dept Delete a table deptno When, emp_bak The records corresponding to the table are also deleted
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2) CONSTRAINT emp_bak_deptno_fk REFERENCES dept(deptno) ON DELETE CASCADE--Cascading deletion of column level constraints
);
--amount to
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno) ON DELETE CASCADE--Cascade deletion of tag type constraints
);
--test
SELECT * FROM dept;
SELECT * FROM emp_bak;
INSERT INTO dept VALUES(88, 'STUDENT', 'ZHANJIANG');
INSERT INTO emp_bak(deptno, ename) VALUES(88, 'Lynn');
INSERT INTO emp_bak(deptno, ename) VALUES(88, 'Tom');
INSERT INTO emp_bak(deptno, ename) VALUES(40, 'Amy'); 
DELETE FROM dept WHERE deptno = 88;--here emp_bak Only deptno A piece of data of 40
--Delete table
DROP TABLE emp_bak;

--Cascade emptying
1.Create table emp_bak,appoint deptno Foreign key, reference dept Tabular deptno Primary key column; When dept Delete a table deptno When, emp_bak Table corresponding records deptno Set to empty
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2) CONSTRAINT emp_bak_deptno_fk REFERENCES dept(deptno) ON DELETE SET NULL--Cascading emptying of column level constraints
);
--amount to
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno) ON DELETE SET NULL--Cascade emptying of tag type constraints
);
--test
SELECT * FROM dept;
SELECT * FROM emp_bak;
INSERT INTO dept VALUES(88, 'STUDENT', 'ZHANJIANG');
INSERT INTO emp_bak(deptno, ename) VALUES(88, 'Lynn');
INSERT INTO emp_bak(deptno, ename) VALUES(88, 'Tom');
INSERT INTO emp_bak(deptno, ename) VALUES(40, 'Amy'); 
DELETE FROM dept WHERE deptno = 88;--here emp_bak In, originally deptno For 88, change to deptno Empty
--Delete table
DROP TABLE emp_bak;

 

V

Check:
1. The column value range of the constraint, but it can be NULL
2. It is recommended to specify the constraint name
3. You can define both column level constraints and table level constraints
4. A table can have multiple CHECK key constraints

1.Create table emp_bak,constraint sal Must be greater than or equal to 3000
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2) CONSTRAINT emp_bak_sal_check CHECK(sal >= 3000),
       comm NUMBER(7, 2),
       deptno NUMBER(2)
);
--amount to
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_sal_check CHECK(sal >= 3000)
);
--test
INSERT INTO emp_bak(empno, sal) VALUES(1001, NULL);--Insert data succeeded
INSERT INTO emp_bak(empno, sal) VALUES(1001, 2900);--Failed to insert data, violating the check constraint(SCOTT.EMP_BAK_SAL_CHECK)
INSERT INTO emp_bak(empno, sal) VALUES(1001, 3000);--Insert data succeeded
--Delete table
DROP TABLE emp_bak;


--Comprehensive definition of five types of constraints
--Column level constraints
CREATE TABLE emp_bak(
       empno NUMBER(4) CONSTRAINT emp_bak_empno_pk PRIMARY KEY,--Primary key constraint
       ename VARCHAR2(10) CONSTRAINT emp_bak_ename_unique UNIQUE,--Unique constraint
       job VARCHAR2(9) CONSTRAINT emp_bak_job_nn NOT NULL,--Non NULL constraint
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2) CONSTRAINT emp_bak_sal_check CHECK(sal >= 3000),--Checking constraints
       comm NUMBER(7, 2),
       deptno NUMBER(2) CONSTRAINT emp_bak_deptno_fk REFERENCES dept(deptno)--Foreign key constraint
);
--Table level constraints
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9) CONSTRAINT emp_bak_job_nn NOT NULL,--Non NULL constraint
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_empno_pk PRIMARY KEY(empno),--Primary key constraint
       CONSTRAINT emp_bak_ename_unique UNIQUE(ename),--Unique constraint
       CONSTRAINT emp_bak_sal_check CHECK(sal >= 3000),--Checking constraints
       CONSTRAINT emp_bak_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno)--Foreign key constraint
);
--Delete table
DROP TABLE emp_bak;

 

Vi

Additional constraints
Syntax:
ALTER TABLE table name ADD [CONSTRAINT constraint constraint name] constraint type (column name);

 

Delete constraint

Syntax:
ALTER TABLE table name DROP primary key | unique (column name) | CONSTRAINT constraint name [CASCADE];

 

Disable constraints
Syntax:
ALTER TABLE table name DISABLE CONSTRAINT constraint constraint name [CASCADE];

 

Enable constraints
Syntax:
ALTER TABLE table name ENABLE CONSTRAINT constraint constraint name;

1.by emp_bak After appending constraints, disable and start constraints, and finally delete them( empno Is a primary key constraint, job Is a non empty constraint,ename Is a unique constraint, sal Is a checking constraint, and sal To be greater than or equal to 3000), deptno Associate tables for unique constraints dept Medium deptno
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2)
);
--Additional constraints
ALTER TABLE emp_bak ADD CONSTRAINT emp_bak_empno_pk PRIMARY KEY(empno);--Primary key constraint
ALTER TABLE emp_bak ADD CONSTRAINT emp_bak_ename_unique UNIQUE(ename);--Unique constraint
ALTER TABLE emp_bak ADD CONSTRAINT emp_bak_sal_check CHECK(sal >= 3000);--Checking constraints
ALTER TABLE emp_bak ADD CONSTRAINT emp_bak_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno);--Foreign key constraint

ALTER TABLE emp_bak ADD CONSTRAINT emp_bak_job_notnull NOT NULL(job);--Append failed
ALTER TABLE emp_bak MODIFY job NOT NULL;--Non NULL constraint
ALTER TABLE emp_bak MODIFY (job CONSTRAINT emp_bak_job_not_null NOT NULL);--As above, add a non empty constraint

--Disable constraints
ALTER TABLE emp_bak DISABLE CONSTRAINT emp_bak_empno_pk;--Primary key constraint
ALTER TABLE emp_bak DISABLE CONSTRAINT emp_bak_ename_unique;--Unique constraint
ALTER TABLE emp_bak DISABLE CONSTRAINT emp_bak_sal_check;--Checking constraints
ALTER TABLE emp_bak DISABLE CONSTRAINT emp_bak_deptno_fk;--Foreign key constraint
ALTER TABLE emp_bak DISABLE CONSTRAINT emp_bak_job_not_null;--Non NULL constraint

--Enable constraints
ALTER TABLE emp_bak ENABLE CONSTRAINT emp_bak_empno_pk;--Primary key constraint
ALTER TABLE emp_bak ENABLE CONSTRAINT emp_bak_ename_unique;--Unique constraint
ALTER TABLE emp_bak ENABLE CONSTRAINT emp_bak_sal_check;--Checking constraints
ALTER TABLE emp_bak ENABLE CONSTRAINT emp_bak_deptno_fk;--Foreign key constraint
ALTER TABLE emp_bak ENABLE CONSTRAINT emp_bak_job_not_null;--Non NULL constraint

--Delete constraint
ALTER TABLE emp_bak DROP CONSTRAINT emp_bak_empno_pk;--Primary key constraint
ALTER TABLE emp_bak DROP CONSTRAINT emp_bak_ename_unique;--Unique constraint
ALTER TABLE emp_bak DROP CONSTRAINT emp_bak_sal_check;--Checking constraints
ALTER TABLE emp_bak DROP CONSTRAINT emp_bak_deptno_fk;--Foreign key constraint 
ALTER TABLE emp_bak MODIFY (job NULL);--Non NULL constraint

 

VII

Data dictionary: P(PRIMARY KEY) R(FOREIGN KEY) C(CHECK, NOT NULL) U (unique)

SELECT * FROM USER_CONSTRAINTS;--View all constraints under the current user
SELECT * FROM USER_CONS_COLUMNS;--View constraint associated column information