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]
SQL 中的约束语句
exec sp_helpconstraint [table_name]

1. 唯一性约束 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]

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 ```
删除
```sql
alter table [table_name] alter column [column_name] drop default```