The concept of constraints: Constraints are some of the rules that are defined in a table to maintain database integrity. You can prevent incorrect data from being inserted into a table by defining constraints for the fields in the table.
Note: 1. If a constraint is used only for a separate field, you can define constraints at the field level, or you can define constraints at the table level, but if a constraint will be used for multiple fields, the constraint must be defined at the table level. The constraints in 2.oracle are identified by name. Constraints can be named by the CONSTRAINT keyword when defining constraints. If the user does not specify a name for the constraint, Oracle will automatically establish a default name for the constraint.
PRIMARY KEY constraint (primary key): A PRIMARY KEY constraint: A field that is defined as a PRIMARY key constraint cannot contain any duplicate values and cannot contain null values. Only 1 PRIMARY KEY constraints can be defined in the same 1 tables. You can define a PRIMARY key constraint for a field, or you can define a PRIMARY key constraint for a combination of multiple fields. Oracle automatically establishes 1 unique indexes and 1 non-null constraints for fields that have primary key constraints. For example:
CREATE TABLE person (p_id int primary KEY,--Define the field as the primary KEY constraint "field-level constraint" P_name varchar2 (), p_age int); Insert into person (P_ID) values (1); Insert into person (P_ID) values (2); Insert into person (P_ID) values (null); CREATE TABLE Person2 (p_id int constraint Pk_person2 PRIMARY KEY,--Define the field as the primary KEY constraint and specify the constraint name "field-level constraint" P_name varcha R2 (+), p_age int);
CREATE TABLE Person3 (p_id int, p_name varchar2 (), p_age int, constraint Pk3_person3 primary key (p_id,p_name)--Define the composite primary key and specify the name "table-level constraint");
Insert into Person3 (p_id,p_name) VALUES (1, ' 1 '); Insert into Person3 (p_id,p_name) VALUES (1, ' 2 '); Insert into Person3 (P_id,p_name) VALUES (2, ' 1 ');
Non-null constraint (NOT NULL): The characteristic of a non-null constraint: A field that defines a non-null constraint cannot contain a null value. A non-null constraint can only be defined at the field level. Multiple non-null constraints can be defined in the same table. For example:
CREATE TABLE Person4 (p_id int, p_name varchar2 () not NULL,--defines the column's non-null constraint p_age int);
INSERT into Person4 values (1,null,1);
CREATE TABLE person5 (p_id int, p_name varchar2 () not NULL,--defines the column's non-null constraint p_age int not null);
INSERT into Person4 values (1,null,null);
Third, UNIQUE constraint (unique): Unique constraint characteristics: A field that defines a unique constraint cannot contain duplicate values. You can define a unique constraint for 1 fields, or you can define a unique constraint for a combination of multiple fields. Therefore, the unique constraint can be defined either at the field level or at the table level. Oracle automatically establishes 1 unique indexes for fields that have unique constraints. You can define both non-null and UNIQUE constraints on the same field. If you define only a unique constraint on 1 fields, and you do not define a non-null constraint, the field can contain multiple null values. For example: Create TABLE Person1 (p_id int, p_name varchar2 () Unique,--defines the column's unique constraint p_age int);
CREATE TABLE Person22 (p_id int, p_name varchar2 () constraint gulaijing unique NOT NULL,--Define both unique and non-null constraints P_age int);
CREATE TABLE person33 (p_id int, p_name varchar2 (), p_age int, constraint p_un3 unique (p_name)- -Specify constraint name);
Four, FOREIGN KEY constraint (foreign key) foreign KEY constraint characteristics: The field defined as a FOREIGN key constraint can contain only the corresponding value of the Reference field in other tables or null value. You can define a FOREIGN key constraint for 1 fields, or you can define a foreign key constraint for a combination of multiple fields. The field that defines the foreign key constraint and the corresponding reference field can exist in the same 1 tables, called self-referencing. You can define both a foreign key and a non-null constraint on the same 1 fields. The referenced column in the primary table must have a PRIMARY KEY constraint or a unique constraint. For example:
CREATE table works (w_id int, w_name VARCHAR2 (10));
CREATE table works (w_id int primary key,w_name VARCHAR2 (10));
CREATE TABLE person (p_id int, p_name varchar2 (), p_age int, w_id int, constraint P_FK fo Reign Key (w_id) references works (W_ID)-FOREIGN KEY constraint); When you define a FOREIGN key constraint, you can also specify the type of reference behavior by using the ON keyword. When a record in the primary table is deleted, you need to determine how to handle the value of the foreign key column in the child table by referencing the behavior. Delete Cascade: Deletes all related records in the child table when the parent table record is deleted delete set null: Refers to the external Code field value of all related records when the parent table record is deleted set to Nulldelete no action: Do not do anything when deleting the parent table record "default Conditions
CREATE TABLE DEPT2 (DEPTNO number (2) not NULL, Dname VARCHAR2 (14));
ALTER TABLE DEPT2 ADD constraint Pk_dept2 primary key (DEPTNO); Insert into DEPT2 (DEPTNO, Dname) VALUES (10, ' Sales Department '), insert into DEPT2 (DEPTNO, Dname) VALUES (20, ' operations Department '); INSERT INTO DEPT 2 (DEPTNO, dname) VALUES (30, ' development Department '); commit; Delete Dept2 where deptno=10;
CREATE TABLE EMP2 (EMPNO number (4) NOT NULL, ename VARCHAR2 (Ten), DEPTNO number (2));
(1) ALTER TABLE EMP2 add constraint Fk_deptno2 foreign key (DEPTNO) references DEPT2 (DEPTNO);(2) ALTER TABLE EMP2 Add const Raint Fk_deptno2 foreign KEY (DEPTNO) references DEPT2 (DEPTNO) on DELETE cascade; (3) ALTER TABLE EMP2 add constraint Fk_de PTNO2 foreign KEY (DEPTNO) references DEPT2 (DEPTNO) on delete set null;
Insert into EMP2 (EMPNO, Ename,deptno) VALUES (7369, ' SMITH '), insert into EMP2 (EMPNO, Ename,deptno) VALUES (7499, ' all EN ', +); insert into EMP2 (EMPNO, Ename,deptno) VALUES (7521, ' WARD ', +); insert into EMP2 (EMPNO, Ename,deptno) VALUES (756 6, ' JONES ', +); insert into EMP2 (EMPNO, Ename,deptno) VALUES (7654, ' MARTIN ', null); commit;
ALTER TABLE Sys. EMP2 drop constraint Fk_deptno2;
CHECK constraint check constraint characteristics: One or more fields in the table must be referenced in the expression that checks the constraint, and the expression must evaluate to a Boolean value. You cannot include subqueries in an expression. You cannot include SQL functions such as sysdate,uid,user,userenv in an expression, nor can you include pseudo-columns such as Rowid,rownum. Check constraints can be defined at the field level and at the table level. Multiple check constraints can be defined on the same field, and both check and non-null constraints can be defined for the same field. Example: Create TABLE person66 (p_id int, p_name varchar2 (), p_age int check (P_age > 20));
INSERT into person66 values (1, ' 1 ', one), insert into person66 values (1, ' 1 ', 111); commit;
CREATE TABLE person222 (p_id int, p_name varchar2 (), p_age int constraint check_person_age check (p_age > 20));
INSERT into person222 values (1, ' 1 ', 11);
CREATE TABLE person333 (p_id int, p_name varchar2 (), p_age int, constraint p_check3 check (p_age ; 20));
INSERT into person333 values (1, ' 1 ', 11);
-----------------------------Additional Supplements--------------------------------
Add PRIMARY KEY constraint ALTER TABLE TABLE_NAME add PRIMARY key (COLUMN_NAME); ALTER TABLE TABLE_NAME ADD constraint T_PK primary key (Column_n AME);
Add non-null constraint ALTER TABLE person modify p_name not null;alter table person modify p_name constraint t_notnull not null;
Rename constraint ALTER TABLE name rename constraint original constraint name to new constraint name
ALTER TABLE Person2 rename Hongbanzhang;
Delete constraint ALTER TABLE name DROP CONSTRAINT constraint name; ALTER TABLE name DROP CONSTRAINT constraint name cascade; The FOREIGN KEY constraint for a cascade delete child table when the primary key is deleted the PRIMARY KEY constraint can also be deleted as follows: ALTER TABLE person drop primary key;alter table person drop PRIMARY key cascade; NOTE: When you delete a constraint, the default When you delete the index for the constraint, if you want to keep the index, use the Keep index keyword sql> ALTER TABLE employees drop PRIMARY KEY Keep index constraint invalidation ALTER TABLE table name Disable CO Nstraint constraint name ALTER TABLE name DISABLE constraint constraint name cascade;--Invalid primary key when the foreign KEY constraint of a cascade delete child table is in effect ALTER TABLE name ENABLE CONSTRAINT constraint name Novalidate:--not verifying old data validate:--verifying old data
Controls whether existing data applies constraints, such as ALTER TABLE T2 enable novalidate constraint sys_c007211; --the constraint is in effect and does not verify that the existing data meets the constraints
ALTER TABLE T2 enable validate constraint sys_c007211; --constraints are in effect, while verifying that existing data meets constraints
ALTER TABLE t2 disable validate constraint sys_c007211; --the constraint fails, while verifying that the existing data is in compliance with the constraint, you cannot perform a DML operation on the table, otherwise it will be reported ORA-25128 error ALTER TABLE t2 disable novalidate constraint sys_c007211;-- Constraint invalidation, does not verify that the existing data conforms to the constraint (default) Note: The action after the constraint is active (enable) is constrained by constraints.
From for notes (Wiz)
Day05_oracle examples of various constraints
