Data integrity concept: Data integrity is used to ensure the logical consistency, correctness and reliability of the data in the database. Enforcing data integrity ensures data quality in the database. Data integrity classification: generally includes three types: domain integrity, entity integrity and referential integrity

1. Domain integrity

Domain integrity, also known as column integrity, refers to the validity of a given column input, that is, to ensure that the data of a specified column has the correct data type, format, and valid data range. Domain integrity can be achieved by defining the corresponding CHECK constraint, as well as by defining data types and NOT NULL for the columns of the table.

The CHECK constraint

The CHECK constraint is actually a verification rule for field input. It indicates that the input of a field must meet the CHECK constraint. Otherwise, data cannot be entered normally. The CHECK constraint can be created as part of the table definition at table creation time or added to an existing table. Tables and columns can contain multiple CHECK constraints. Allows you to modify or delete existing CHECK constraints.

Create table CHECK constraint syntax

CREATE TABLE table_name (column) [CONSTRAINT check_name] CHECK

CREATE TABLE SC (Sno CHAR(9), Cno CHAR(4), Grade SMALLINT CHECK (Grade>=0 AND Grade<=100),/* The value ranges from 0 to 100*/ PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno),  FOREIGN KEY (Cno) REFERENCES Course(Cno) );Copy the code

Define CHECK constraint syntax format when modifying table:

ALTER TABLE table_name ADD [CONSTRAINT check_name] CHECK

ALTER TABLE T1 ADD CONSTRAINT CHK_Sage CHECK(Sage>16 and Ssex in (' male ',' female '))Copy the code

Define CHECK constraint syntax format when modifying table:

ALTER TABLE table_name [WITH the CHECK | WITH NOCHECK] ADD [CONSTRAINT check_name] CHECK (expression)

Note: 1) The WITH CHECK option indicates that the CHECK constraint applies to both existing and new data. If this option is omitted and the default setting is used, the CHECK constraint applies to both existing and new data. 2) The WITH NOCHECK option indicates that the CHECK constraint applies only to new data and does not force the CHECK constraint on existing data.

Delete the CHECK constraint syntax from the t-SQL statement

ALTER TABLE table_name DROP [CONSTRAINT] check_name

ALTER  TABLE t1 DROP CONSTRAINT CHK_Sage
Copy the code

Note:

Have ADD the CONSTRAINTMust be named!

No ADD the CONSTRAINTYou can’t name it!

Constraint names in the same database cannot be the same, even if they are not in the same table.

Default constraints

Define the DEFAULT constraint syntax format when creating a table:

CREATE TABLE table_name
[CONSTRAINT default_name] default

CREATE  TABLE  CLASS                       
 (CLSNO  CHAR(8),
  NUM   INT  CONSTRAINT  DEF_NUM   DEFAULT   30)
Copy the code

Define the DEFAULT constraint syntax format when modifying a table:

ALTER TABLE table_name ADD [CONSTRAINT default_name] default

ALTERTABLE STU
  ADD CONSTRAINT DEF_SAGE DEFAULT 18 FOR SAGE
Copy the code

Delete the DEFAULT constraint, as above

ALTER TABLE table_name DROP [CONSTRAINT] constraint_name

2. Entity integrity

Entity integrity, also known as row integrity, is used to ensure that records for each particular entity in a data table are unique. Entity integrity of data can be achieved through PRIMARY KEY constraints, UNIQUE constraints, IDENTITY attributes, or indexes.

PRIMARY KEY constraint

The PRIMARY KEY constraint defines a PRIMARY KEY in a table that uniquely identifies rows in the table. A primary key can be a column or a combination of columns. A table can have only one PRIMARY KEY constraint, and each table should have one PRIMARY KEY. If a PRIMARY KEY constraint exists, it can be modified or deleted. To modify the PRIMARY KEY constraint, you must first delete the existing one and then recreate it with the new definition. Note: When adding a PRIMARY KEY constraint to an existing column in a table, SQL SERVER 2008 checks the existing data in the column to ensure that the existing data complies with the PRIMARY KEY rules (no null and duplicate values). If a PRIMARY KEY constraint is added to a column with a null or duplicate value, SQL SERVER does not perform the operation and returns an error message. When a PRIMARY KEY constraint is referenced by a FOREIGN KEY constraint of another table, the referenced PRIMARY KEY constraint cannot be deleted. To delete it, the referenced FOREIGN KEY constraint must be deleted first. Or set cascading deletion.

Define the PRIMARY KEY constraint when creating the table

CREATE TABLE table_name …… PRIMARY KEY… The position/order of the PRIMARY KEY clause

CREATE TABLE sc2 (sno CHAR(8), cno CHAR(10), g INT, primary key (sno, cNO)) /*Copy the code

CREATE TABLE sc3
(sno CHAR(8),
 cno char (10),
 primary key (sno , cno),
 g INT
)
Copy the code

CREATE TABLE sc4
(primary key (sno , cno),
 sno CHAR(8),
 cno char (10),
 g INT 
 )
Copy the code

CREATE TABLE sc5
(sno CHAR(8) primary key (sno , cno),
 cno char (10),
 g INT 
 )
Copy the code

Add PRIMARY KEY constraint when modifying table

ALTER TABLE table_name ADD [CONSTRAINT constraint_name] PRIMARY KEY(…) Such as:

Alter table 教 程 add primary keyCopy the code

 alter  table  q  add  primary key(q1 desc,q2 )
Copy the code

Delete PRIMARY KEY constraint when modifying table, as above

ALTER TABLE table_name DROP [CONSTRAINT] constraint_name

The UNIQUE constraint

If you want to ensure that non-primary key columns in a table do not enter duplicate values, you define a UNIQUE constraint on that column. To ensure uniqueness on columns that allow NULL values, use the UNIQUE constraint instead of the PRIMARY KEY constraint, although only one NULL value is allowed in that column. A table can define only one PRIMARY KEY constraint, but a table can define several UNIQUE constraints on different columns as required. (2) The PRIMARY KEY value cannot be NULL, while the UNIQUE value can be NULL. (3) When a PRIMARY KEY constraint is created, the system automatically generates an index. The default type of the index is a cluster index. When a UNIQUE constraint is created, the system automatically generates a UNIQUE index whose default type is non-cluster index. The PRIMARY KEY constraint is similar to the UNIQUE constraint in that it does not allow duplicate values for corresponding fields in the table.

Define the UNIQUE constraint when creating the table

CREATE TABLE table_name
[CONSTRAINT constraint_name]

Sno CHAR(9), Sname CHAR(20) UNIQUE, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR (20));Copy the code

Add UNIQUE constraint when modifying table

ALTER TABLE table_name ADD [CONSTRAINT constraint_name] UNIQUE (column)

alter  table  Student20  add  unique(sname)
Copy the code

Delete the UNIQUE constraint as above

ALTER TABLE table_name DROP [CONSTRAINT] constraint_name

3. Referential integrity

When adding, modifying, or deleting records from a data table, referential integrity can be used to ensure data consistency between associated tables. Referential integrity ensures consistency between data in the master table and data in the slave table. In SQL SERVER 2008, referential integrity is achieved by defining the correspondence between a foreign key and a primary key or a foreign key and a unique key.

FOREIGN KEY FOREIGN keys

When inserting and deleting data from two associated tables (primary and secondary), data consistency between them is guaranteed through referential integrity. The referential integrity between the PRIMARY table and the secondary table can be achieved by defining the FOREIGN KEY of the secondary table with a FOREIGN KEY, and defining the PRIMARY KEY or UNIQUE KEY (not allowed to be empty) of the PRIMARY table with a PRIMARY KEY or UNIQUE constraint. Define inter-table reference relationships: First define primary key constraints (or unique key constraints) for the primary table, and then define foreign key constraints for the secondary table.

Define foreign key constraints when creating a table

CREATE TABLE table_name (
[CONSTRAINT constraint_name] [FOREIGN KEY] REFERENCES ref_table (ref_column) …… ) Such as:

CREATE TABLE Course(Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4) FOREIGN KEY REFERENCES Course(Cno), /*Cpno */ Ccredit SMALLINT);Copy the code

Add a foreign key constraint when modifying a table: only one foreign key constraint can be added at a time!

ALTER TABLE table_name ADD [CONSTRAINT constraint_name] FOREIGN KEY (column) REFERENCES ref_table (ref_column) Alter table XSCJ alter table XSCJ alter table XSCJ alter table XSCJ Add constraint statement.

Alter table XSCJ add CONSTRAINT FK1 Foreign key references XSDACopy the code

Alter table XSCJ add constraint FK2 Foreign key(中 国 名 称)Copy the code

Alter table XSCJ drop constraint… Statement to delete multiple foreign keys:

alter  table  xscj  drop  [constraint]  fk1, fk2
Copy the code

Set cascading changes

Delete the original foreign key first

Alter table XSCJ drop [constraint] FK_kcxx_Copy the code

Add a new CASCADE foreign key

Alter table XSCJ add constraint FK_kcxx_ foreign Key references KCXX on UPDATE CASCADECopy the code

Delete references between tables

Delete the foreign key constraint from the table. The syntax format is the same as for the previous constraint deletions. ALTER TABLE table_name DROP [CONSTRAINT] constraint_name

alter  table  q  DROP constraint FK__q__q1__61F08603
Copy the code

Note: Primary keys, foreign keys, unique values, default values, and check constraints are dropped by constraint