1、添加主键约束alter table stuinfo add costraint pk_stuno primary key (stuno)
2、唯一约束alter table stuinfo add constraint uq_stuid unique (stuid)
3、添加默认约束alter table stuinfo add constraint df_address default ('地址不详') for address
4、添加检查约束alter table stuinfo add constraint ck_age check (age between 15 and 40) 这是年龄在15到40之间
5、添加外键约束alter table stumarks add constraint fk_stuno foreign key (stuno) references stuinfo (stuno)这是一个成绩表(stumarks)引用学生信息表中的列 学号
6、not null 非空约束,强制不接受空值,例如,创建表时,name varchar(6) not null
--创建约束
--为性别增加一个检查约束,要求性别只能是:'男' or '女'
alter table AbcTable add constraint CK_AbcTable_EmpGender check(EmpGender='男' or EmpGender='女')
--删除单个约束
alter table AbcTable drop constraint CK_AbcTable_EmpGender
--为EmpName增加一个唯一约束
alter table Employees add constraint UQ_Employees_EmpName unique(EmpName)--为订单增加检查约束,要求Payment=(PaymentToBId+PaymentToTm)
alter table Trade add constraint CK_Trade_PaymentLargeZero check(Payment>0 and PaymentToBid>0)
alter table Trade add constraint CK_Trade_PaymentToBidToTm check(Payment=(PaymentToBid+PaymentToTm))
alter table [Order] add constraint CK_Order_PaymentToBidToTm check(Payment=(PaymentToBid+PaymentToTm))
alter table ChannelInMoneyFlow add constraint UQ_ChannelInMoneyFlow_InTradePayDate unique(InTradePayDate)--查找Check约束
select * from sys.check_constraints chk
select * from sys.tables tabSELECTtab.name AS [表名],chk.name AS [Check约束名],chk.definition
FROMsys.check_constraints chkJOIN sys.tables tabON (chk.parent_object_id = tab.object_id)----查找唯一约束
select * from sys.indexes idx where idx.is_unique_constraint = 1SELECTtab.name AS [表名],idx.name AS [约束名称],col.name AS [约束列名]
FROMsys.indexes idxJOIN sys.index_columns idxColON (idx.object_id = idxCol.object_idAND idx.index_id = idxCol.index_idAND idx.is_unique_constraint = 1)JOIN sys.tables tabON (idx.object_id = tab.object_id)JOIN sys.columns colON (idx.object_id = col.object_idAND idxCol.column_id = col.column_id);----查找外键约束
select * from sys.foreign_keys fk selectoSub.name AS [子表名称],fk.name AS [外键名称],SubCol.name AS [子表列名],oMain.name AS [主表名称],MainCol.name AS [主表列名]
fromsys.foreign_keys fk JOIN sys.all_objects oSub ON (fk.parent_object_id = oSub.object_id)JOIN sys.all_objects oMainON (fk.referenced_object_id = oMain.object_id)JOIN sys.foreign_key_columns fkColsON (fk.object_id = fkCols.constraint_object_id)JOIN sys.columns SubColON (oSub.object_id = SubCol.object_id AND fkCols.parent_column_id = SubCol.column_id)JOIN sys.columns MainColON (oMain.object_id = MainCol.object_id AND fkCols.referenced_column_id = MainCol.column_id)
下一篇:Node.js环境搭建