[Reading notes] SQL constraints

Purpose: To ensure that data conforms to some data integrity rule by setting constraints at the column or table level

Implementation: The database proactively checks the integrity of the maintenance data

means: Constraints, data types, triggers

--------------------------------------------------------------------------------------------------------------- -------------------------------------

Three types of constraints:

Entity constraints, domain constraints, referential integrity constraints

Specific implementation methods:

Primary KEY constraints, FOREIGN KEY constraints, unique constraints,CHECK constraints, default Constraints, rules, defaults

Classified

Domain constraints: For columns, ensure that the data for the column meets a specific standard. CHECK constraints, default constraints, rules, and Defaults

Entity constraints: For rows, only specific rows are concerned, and specific columns for each row have unique values. PRIMARY KEY constraint, UNIQUE constraint

Referential integrity constraint: The value of a column must match the value of another column. FOREIGN KEY constraints

--------------------------------------------------------------------------------------------------------------- -------------------------------------

Difference constraints and rules, default values:

Rules and defaults are applied earlier than CHECK and default constraints, which are part of older SQL fallback constraints and are used primarily for backwards compatibility.

Constraint: is a table of characteristics, itself does not exist form;

Rules, default values: are defined separately, and then bound to the table.

--------------------------------------------------------------------------------------------------------------- -------------------------------------

Syntax

Add constraint--use ALTER TABLE <table name>

ALTER TABLE <table name>

ADD CONSTRAINT <constraint name>

<constraint type> <constraint parameters...>

FOREIGN KEY constraints

<column Name><data type><nullability>

FOREIGN KEY REFERENCES <table name> (<column name>)

[on DELETE {cascade|no action| SET null| SET DEFAULT}]

[on UPDATE {cascade|no action| SET null| SET DEFAULT}]

ALTER TABLE <table name>

ADD CONSTRAINT <constraint name>

FOREIGN KEY (<column name>) REFERENCES <table name> (<column name>)

Unique constraint

<column name> <data type> <nullabiliby>

UNIQUE

ALTER TABLE <table name>

ADD CONSTRAINT <constraint name>

UNIQUE (<column name>)

CHECK Constraint

ALTER TABLE <table name>

ADD CONSTRAINT <constraint name>

CHECK

(<rule...>)

DEFAULT Constraint

<column name> <data type> <nullability>

DEFAULT <default value>

ALTER TABLE <table name>

ADD CONSTRAINT <default name>

DEFAULT <value> for <column name>

Ignore invalid data when creating constraints

When you create a constraint with SQL Server, the constraint cannot be created if the existing data does not meet the constraint requirements. There are two workarounds:1) Modify the existing data to conform to the constraint requirements;2) Add the WITH NOCHECK option in the ALTER statement :

ALTER TABLE <table name>

With NOCHECK

ADD CONSTRAINT <constraint name>

<constraint Type>

Temporarily disable a constraint that already exists

ALTER TABLE <table name>

NOCHECK

CONSTRAINT <constraint name>

To enable a forbidden constraint

ALTER TABLE <table name>

CHECK

CONSTRAINT <constraint name>

Delete Constraint

ALTER TABLE <tablename>
Drop constraint <constraintname>

modifying constraints

Delete the constraint before adding

[Reading notes] SQL constraints