5.4 integrity constraint naming clause

1. Integrity constraint naming clause
   Constraint < integrity constraint name > < integrity constraint >
        ◾ < integrity constraints > include not null, unique, primary key phrase, foreign key phrase, check phrase, etc
   [example 5.1] establish a Student registration form, which requires that the Student number is between 90000 and 99999, the name cannot be blank, the age is less than 30, and the gender can only be "male" or "female"

create table Student
(	Sno numeric(6) constraint c1 check(Sno between 90000 and 99999),
	Sname char(20) constraint c2 not null.
	Sage numeric(3) constraint c3 check(Sage < 30),
	Ssex char(2) constraint c4 check(Ssex in('male','female')),
	constraint StudentKey primary key(Sno)
);

[example 5.2] establish a TEACHER form, which requires that each TEACHER's salary should not be less than 3000 yuan. Salary payable is the sum of salary list Sal and deduction.

create table TEACHER
(	Eno numeric(4) primary key		-- Define master code at column level
	Ename char(10),
	Job	char(8),
	Sal numeric(7,2),
	Deptno numeric(2),
	constraint TEACHERKey foreign key(Deptno) references DEPT(Deptno),
	constraint c1 check (Sal + Deduct >= 3000)
)

1. Modify integrity limits in tables
        ◾ Use the alter table statement to modify the integrity limit in the table
   [example 5.3] remove the restrictions on gender in * * [example 5.1]** Student table (removing the restrictions on gender can only be men and women, and I don't know what else it can be)

alter table Student
drop constraint c4;

[example 5.4] modify the constraints in the Student table, requiring the Student number to be between 900000 and 999999, and the age to be less than 40 from less than 30
You cannot modify it directly. You can only delete it first and then modify it
     ◾ You can delete the original constraints first and then add new constraints

alter table Student
drop constraint c1;

alter table Student
add constraint c1 check (Sno between 900000 and 999999);

alter table Student
drop constraint c3;

alter table Student
add constraint c3 check (Sage < 40);

5.6 assertions

♥ In SQL, you can use the create assertion statement to specify more general constraints through declarative assertions
♥ You can design more complex integrity constraints for multiple tables or aggregation operations
♥ After the assertion is created, the operation of the relationship involved in the assertion will start from the check of the assertion by the relational database management system, and any operation that makes the assertion not true will be rejected

1. Statement format for creating assertions
        ◾ Create assertion < assertion name > < check clause >
        ◾ Each assertion is given a name
        ◾ The constraints in the < check clause > are similar to the expressions in the where clause

[example 5.5] the database course is limited to 60 students at most

create assertion ASSE_SC_DB_NUM	
check(60 >= (select count(*) 
			from Course, SC 
			where SC.Cno = Course.Cno 
				  and Course.Cname = 'database')
	);

Trigger timing: insert tuple into SC table

Default response: refusal to execute

[example 5.6] limit each course to a maximum of 60 students

create assertion ASSE_SC_CNUM	
check(60 >= all(select count(*) 
			from  SC 
			group by Cno)
	);

[example 5.7] limit each course to a maximum of 60 students per semester
First, you need to modify the mode of SC table and add a TERM attribute

alter table SC add TERM DATE;

Then, define the assertion:

create assertion ASSE_SC_CNUM2	
check(60 >= all(select count(*) 
			from  SC 
			group by Cno，TERM)		-- It needs to be grouped according to the two conditions of course number and semester
	);

If assertions are complex, the overhead of detecting and maintaining assertions is high, which should be paid attention to when using assertions

1. The statement format for deleting assertions is:
        ◾ Drop assertion < assertion name >;

Summary:
♥ Integrity constraint naming clause
     ◾ Naming integrity constraints
     ◾ Use the alter table statement to modify the integrity constraint
● add / delete / modify integrity constraints
♥ Assert
     ◾ Define assertion
● define complex integrity constraints with assertions
     ◾ Delete assertion