The integrity of SQL Server data

--sql constraints

/*

(1) A mechanism for providing semantic definition of integrity constraints

Entity integrity: Primary key, non-null

???? Referential integrity: FOREIGN key

???? User-defined integrity: check, Trigger

(2) Provide integrity check method

???? This is usually done after Insert, Update, delete is executed, and after the transaction has been committed, the check begins.

(3) Default handling

???? Reject (no action)

???? Cascade (cascaded Cascade)

?

*/

/* several constraints

Non-null constraint (NOT NULL Constraint)

PRIMARY KEY constraint (Primary key Constraint)

FOREIGN KEY constraint (Foreign key Constraint)

Uniqueness constraint (unique Constraint)

Check constraint (check Constraint)

Default value constraint (Defaults Constraint)

?

*/

?

--PRIMARY KEY constraint

--"Example 1" Entity integrity constraints.

CREATE TABLE Student1 (

???? Sno Char (9) PRIMARY key,--//column-level constraint

???? Sname varchar (10),

???? Ssex char (2),

???? Sage int NULL,

???? Sdept Char (TEN))--,

--or PRIMARY key (SNO))--//table-level constraint

?

--table-level constraints

CREATE TABLE SC1 (

???? Sno Char (9),

???? CNO Char (3),

???? grade int,

Primary KEY (SNO,CNO)--//table-level constraint

)

?

?

--Referential integrity definitions

--foreign key (column name) references table_name (column name)

--Note that the column to be referenced should be the same length as the referenced column, with the same data type

SP_HELP course;

sp_help student;

--"Example 2" Referential integrity instance (column level, table level available).

CREATE TABLE SC2 (

???? Sno Char (9),

???? CNO Char (4),

???? grade int,

Primary KEY (SNO,CNO),

Foreign KEY (CNO) References course (CNO),

Foreign KEY (SNO) references student (SNO)

);

?

?

--Test constraints

CREATE TABLE SC3 (

???? Sno Char (9),

???? CNO Char (4),

???? grade int,

Primary KEY (SNO,CNO),

Foreign KEY (SNO) references student (SNO)

On Delete No action--fails when deleting student records

On UPDATE cascade--but the records in the SC3 table are also updated when you update student records

) ;

--Appropriate use of cascading updates and cascading deletes to facilitate the operation of the database at the time of the update;

INSERT into student (SNO) VALUES (' 2014019 ');

INSERT into SC3 values (' 2014019 ', ' 001 ', 100);

?

?

Delete from student where sno= ' 2014019 ';

Update student set sno= ' 122222 ' where sno= ' 2014019 ';

?

SELECT * from SC3;

--After you delete the constraint, you can successfully delete the

--When you do not give the constraint a name, the DBMS automatically gives the constraint a name

--sp_help SC3;

ALTER TABLE SC3 drop FK__SC3__SNO__398D8EEE;

?

drop table sc3;

The integrity of SQL Server data