Oracle's integrity constraints

Oracle's integrity constraints

The integrity constraints in the database are:

1. PRIMARY KEY constraint (Primary)
2. Unique constraint (unique)
3. Check constraints
4. Non-null constraint (NOT NULL)- -belongs to the CHECK constraint
5. FOREIGN KEY constraint (foreign key)

First build a student table:

 create  table   T_student_con (f_id  int  , f_name  varchar2  (  ), f_sex  char  (2  ), f_age  int  , F_birth date  default   sysdate);  

-- Add a field Alter Table T_student_con Add varchar2 (a);

1. PRIMARY KEY constraint: A field with a PRIMARY key constraint added cannot be null, and a unique constraint is automatically added.

-- Add a PRIMARY KEY constraint Alter Table T_student_con Add constraint Primary Key (f_id);

2. Unique constraint: The value of a field with a unique constraint added is unique and cannot be duplicated.

-- Add a UNIQUE constraint Alter Table  addconstraintunique(f_name);

3, check the constraint: the integrity of the data can be customized to ensure that the field data does not exist outside the constraints of data.

-- Add a Check constraint Alter Table  addconstraintcheck(f_sex='M'  or f_sex='f')

4, non-null constraint: A field with a non-null constraint, the value cannot be empty, or a null value is inserted.

-- a non-null constraint is one of the check constraints, which is not a very common method of adding Alter Table T_student_con Add constraint Check  is  not null);

-- we typically add a non-null constraint by modifying the table field's method Alter Table varchar2 (notnull);

5, FOREIGN KEY constraints: if there is a link between the table, then the establishment of FOREIGN key constraints can guarantee the integrity and standardization of data.

First set up an exam information table:

Create Table T_student_exam (       int,- - primary key       varchar2(),--  account number       (5,2),-- fractional       int  -- foreign key to student information );

The following table adds a primary key and a foreign key:

--Add primary KeyAlter TableT_student_examAdd constraintpk_exam_idPrimary Key(e_id);--Add foreign KeyAlter TableT_student_examAdd constraintfk_exam_student_idForeign Key(f_id)ReferencesT_student_con (f_id) on Delete Cascade;--when deleting student information, Cascade deletes the score information from the table

Note: The table with the FOREIGN KEY constraint added, if the primary table has corresponding from the table records, you need to add the foreign key constraint when adding the ON DELETE cascade keyword, can be directly deleted, this keyword represents a cascade delete. Otherwise, it cannot be deleted directly.

6, the deletion of the constraint
Syntax: ALTER TABLE T_STUDENT_EXAM DROP constraint constraint name;

-- Delete Constraint Alter Table Drop constraint fk_exam_student_id;

Attention! We can use the above method to establish the integrity constraints of the table, and we can also build constraints when the table is built. As follows

Create or Replace TableT_student_con (f_idint Primary Key, F_namevarchar2( -), F_sexChar(2)Check(F_sex='M' ORF_sex='F'),--1, inspection of the constraintsF_ageint, F_birth datedefaultSysdate,constraintCk_std_ageCheck(F_age>=1  andF_age<= -)--2, inspection of the constraints);

Create TableT_student_exam (e_idint,--PRIMARY KeyE_subjectvarchar2( -),--SubjectsE_score Number(5,2),--scoref_idint REFERENCEST_student_con (f_id) on DELETE CASCADE--foreign KEY constraints for student information);

Oracle's integrity constraints