SQL中的约束语句

SQL 中的约束语句

---

数据库：SQL Server

通用语法

create table [table_name](
    [column_1] [type] [constraint],
    ...
)

create table [table_name](
    ... ,
    constraint [constraint_name] [constraint] (column, ...)
)

alter table [table_name] add [constraint] ([column_name])

alter table [table_name] add constraint [contrasint_name] [constraint] (column, ...)

alter table [table_name] drop constraint [constraint_name]

//查询约束名
exec sp_helpconstraint [table_name]

---

1. UNIQUE 约束的创建与删除

唯一性约束 UNIQUE
创建

//建表时单列约束
create table [table_name](
    [column_1] [type] unique,
    ...
)
//建表时多列约束，并对约束命名
create table [table_name](
    ... ,
    constraint [UQ_name] unique (column_1, column_2, ...)
)
//对已建表的单列约束
alter table [table_name] add unique ([column_name])
//对已建表多列约束，并对约束命名
alter table [table_name] add constraint [UQ_name] unique (column_1, column_2)

删除

// 约束已经命名
alter table [table_name] drop constraint [UQ_name]
//如果约束未命名
exec sp_helpconstraint [table_name]
alter table [table_name] drop constraint [UQ_name]

---

2. 主键约束

创建

create table [table_name](
    [column_1] primary key,
    ... 
)

create table [table_name](
    ... ,
    constraint [PK_name] primary key (column_1, column_2, ...)
)

alter table [table_name] add primary key  [column_name]

alter table [table_name] add constraint [PK_name] primary key (column_1, column_2)

删除

alter table [table_name] drop constraint [PK_name]

---

3. 外键约束

创建

create table [table_name](
    ... ,
    [column_fk] [type] foreign key references table_1([pk_name])
)

create table [table_name](
    ... ，
    constraint [FK_name] foreign key ([column_fk]) references table_1([pk_name])
)

alter table [table_name] add constraint [fk_name] foreign key ([column_fk]) references table_1([column_pk])

删除

alter table [table_name] drop constraint [FK_name]

4. check 约束

创建

create table [table_name](
    [column_1] [type] check([column_1] > 0) ,
    ...
)

create table [table_name](
    ... ,
    constraint [CHK_name] check ([column_1] > 0 and [column_2] = 'str')
)

alter table [table_name] add check ([column_1] > 0)

alter table [table_name] add constraint [CHK_name] check ([column_1] > 0 and [column_2] = 'str')

删除

alter table [table_name] drop check [CHK_name]

5. default 约束

创建

create table [table_name](
    ... ,
    [column_1] [type] default 'str',
    [column_2] [type] default getdate()  
)

alter table [table_name] add default ['str'] for [column] with values 

删除

alter table [table_name] alter column [column_name] drop default