你的位置:首页 > 数据库

[数据库]Part 3 talking about constraint in sql

What is Foreign key and how to create a Foreign key constraint?

Note:Foreign Keys are used to enforce(强制) database integrity(完整) . In layman's terms(一般来说), A foreign key in one table points to a primary key in another table. The foreign key constraint prevents invalid data form being inserted into the foreign key column. The values that you enter into the foreign key column, has to be one of the values contained in the table it points to.

for example:add a foreign key relation.

Table - Student: ID, GenderID;

           Gender: ID, StudentID;

alter table Student add constraint FK_Student_GenderIDforeign key (GenderID) references Gender (ID)Syntax:
ALTER TABLE 外键表名 ADD CONSTRAINT 外键约束名
FOREIGN KEY (外键名) REFERENCES 主表名 (主键名)

Adding a default constraint and dropping a constraint

Altering an existing column to add a default constraint:

alter table Gender
add constraint DF_Gender_ID
default 1 for IDSyntax:
ALTER TABLE 表名ADD CONSTRAINT 约束名DEFAULT 默认值 FOR 列名

Adding a new column with default value, to an existing table:

alter table Student
add Name nvarchar(20) not null
constraint DF_Student_Name default 'gester'Syntax:
ALTER TABLE 表名ADD 列名 数据类型 是否允许nullCONSTRAINT 约束名 DEFAULT 默认值

Dropping a constraint:

alter table Student
drop constraint DF_Student_NameSyntax:
ALTER TABLE 表名DROP CONSTRAINT 约束名

Cascading referential integrity constraint

Adding a check constraint

Unique key constraint