constraint constraint
First article: Five kinds of constraints
1. Primary KEY ConstraintsCREATE TABLE T (i int primary key)--constraint name is automatically generated by the system ALTER TABLE DBO.T ADD constraint T_PK primary key (i)
The index is created automatically when the primary key is created, which is necessary, even if there is already an index on the field. The cluster index is created unless the current table already contains the cluster index, or the nonclustered keyword is specified when the primary key is created. Note that the field must first be defined as NOT NULL to add the PRIMARY KEY constraint to this field.
2.Foreign key ConstraintCREATE TABLE t (ID int foreign key references T2 (ID)) ALTER TABLE table name ADD constraint constraint name foreign key (associated field) References Main Table (association word Paragraph
-level linkage forA FOREIGN KEY constraint restricts the insertion or updating of the value of a field in a child table, which must already exist in the parent table beforehand. On the other hand, the primary key is either update or delete in the parent table, and child rows are also checked (avoid orphan). SQL Server defaults to restricting the deletion of the parent row when a child row exists, and the parent table of the child table cannot be deleted. ALTER TABLE DBO.T ADD constraint constraint name foreign key (associated field) references Main table (associated field) on Update no action |cascade | Set NULL | Set default on Delete no action | Cascade | Set NULL | Set default is no Action,update the parent row is a cascade update child row. Cascade is a cascade delete. Set NULL and set default are introduced after sqlserver2005, that is, when the parent row is updated, the child rows are null and reset to default.
Example: Create table dbo.t1 (i int not null) ALTER TABLE DBO.T1 add constraint pk_t1 primary KEY (i) CREATE table dbo.t2 (i int no T null) ALTER TABLE DBO.T2 add constraint fk_t2 foreign key (i) references dbo.t1 (i)
If the foreign key field is defined as NOT NULL, the value of INSERT or update must already exist in the parent table. If it is defined as NULL, the foreign key field can have a null value, regardless of whether the parent table has null.
3.Unique ConstraintsCREATE TABLE T (i int unique) ALTER TABLE name ADD CONSTRAINT constraint name unique (field) it is well known that Oracle is uniquely constrained by a field that allows multiple null values to be inserted. These null values are also not inserted into the unique index on which the unique constraint depends. In SQL Server, the only bound field can have only one null value, and the null value is inserted into the unique index.
When you create a unique constraint, an index is built automatically, even if there are already indexes on that field. For this new index, the default is unique noncluster.
4.Default ConstraintsCREATE TABLE T (i default ' man ' Check (i= ' male ' or i= ' female ') ALTER TABLE name add constraintconstraint name default (' default content ') for field
The default value is used only in INSERT statements and is ignored in delete and UPDATE statements. (UnlessThe UPDATE statement explicitly describes the update as the default keyword)any value of the field is provided in the INSERT statement, and the default value is not used.
5.Check ConstraintsCREATE TABLE t (i int check (i=1 or i=2)) ALTER TABLE name add constraintConstraint name Check (field between 1 and)
6.Not null table definitionIn SQL Server, not NULL is considered a table definition, not a constraint. So when we look at a data dictionary, we can't find the constraint of NOT NULL. However, it is generally semantically assumed that this is also a constraint. ALTER TABLE T ALTER COLUMN I NOT NULLALTER TABLE t ALTER COLUMN I NULL
Article two: constraint managementDelete ConstraintALTER TABLE name DROP CONSTRAINT constraint nameDeleting a constraint deletes the dependent index.
temporarily deactivate | Enable constraintWhen we want to enter data that does not satisfy the current constraint but is reasonable, we can temporarily disable the constraint, and then enable the constraint after inserting the data. Only for FOREIGN KEY constraints and check constraints. Temporary disable constraint ALTER TABLE DBO.T nocheck constraint constraint_name all table constraints: SELECT ' ALTER TABLE ' +name+ ' nocheck constraint all ' fro M sysobjects where type= ' U ' re-enable constraint ALTER TABLE DBO.T CHECK constraint constraint_name enable all table constraints select ' ALTER TABLE ' +name+ ' che CK constraint all ' from sysobjects where type= ' U '
Add constraint to table original data without validation ALTER TABLE TABLE_NAME
With Nocheck Add constraint constraint_name Check (age>18) This way, the table's original data may not satisfy the constraint.
Chapter Three: Data dictionary
Query constraint select * from INFORMATION_SCHEMA. Table_constraintswhere table_name in (' T ')
View the constraint name and state Sp_helpconstraint [DBO] on a table. T]--status_enabled Explicit whether the constraint is enabled
