1. 添加表的主键(Primary Key)和默认值(Default)约束
在SQL Server中,给表添加主键(Primary Key)及默认值(Default)约束是数据库设计和维护中常见的操作。这些操作可以通过ALTER TABLE语句在表已存在的情况下执行,也可以通过CREATE TABLE语句在创建表时直接指定。下面分别介绍这两种情况下的操作方法。
1.1 使用ALTER TABLE语句
1.1.1 添加主键约束
假设有一个表Employees
,你想将EmployeeID
列设置为主键。首先,确保EmployeeID
列中的每个值都是唯一的,并且没有NULL值。然后,你可以使用以下SQL语句来添加主键约束:
ALTER TABLE Employees
ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID);
这里,PK_Employees
是主键约束的名称,你可以根据需要自定义。EmployeeID
是你要设置为主键的列名。
1.1.2 添加默认值约束
如果你想给Employees
表的HireDate
列设置一个默认值(例如,当前日期),你可以使用以下SQL语句:
ALTER TABLE Employees
ADD CONSTRAINT DF_Employees_HireDate DEFAULT GETDATE() FOR HireDate;
这里,DF_Employees_HireDate
是默认值约束的名称,GETDATE()
是一个SQL Server函数,用于获取当前的日期和时间。HireDate
是你要添加默认值约束的列名。
1.2 使用CREATE TABLE语句
在创建新表时,你也可以直接在列定义中指定主键和默认值约束。
CREATE TABLE Employees (
EmployeeID INT NOT NULL,
Name NVARCHAR(100),
HireDate DATE constraint df_Employees_HireDate DEFAULT GETDATE(),
CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
);
在这个例子中,EmployeeID
列被定义为NOT NULL,并且直接在表定义中指定了主键约束PK_Employees
。同时,HireDate
列被定义为具有默认值GETDATE()
。
sys.key_constraints和sys.default_constraints系统视图分别提供了关于主键约束和默认值约束的更多详细信息。
-- 主键约束
select name,parent_object_id,object_name(parent_object_id) as table_name,type
from sys.key_constraints
where parent_object_id=object_id('Employees');
-- 默认值约束
select name,parent_object_id,object_name(parent_object_id) as table_name,type
from sys.default_constraints
where parent_object_id=object_id('Employees');
1.3 注意事项
- 主键约束自动确保列中的每个值都是唯一的,并且不允许NULL值。
- 默认值约束在插入新行时没有为列提供值时,自动为列提供指定的值。
- 在添加主键约束之前,确保列中没有重复的值和NULL值。
- 约束名称(如
PK_Employees
和DF_Employees_HireDate
)在数据库中必须是唯一的,但它们可以根据你的偏好进行自定义。 - 使用
ALTER TABLE
语句时,如果表已经存在数据,添加主键约束时必须确保该列中没有重复值或NULL值,否则操作将失败。
2. 删除表的主键(Primary Key)和默认值(Default)约束
在SQL Server中,删除表的主键(Primary Key)和默认值(Default)约束需要使用ALTER TABLE
语句配合DROP CONSTRAINT
子句。以下是如何执行这些操作的示例。
2.1 删除主键约束
要删除表的主键约束,你首先需要知道该主键约束的名称。这个名称在创建主键时指定,如果你不记得了,可以通过查询系统表(如INFORMATION_SCHEMA.TABLE_CONSTRAINTS
或sys.key_constraints
)来查找。
假设主键约束的名称为PK_Employees
,你可以使用以下SQL语句来删除它:
ALTER TABLE Employees
DROP CONSTRAINT PK_Employees;
2.2 删除默认值约束
同样地,要删除默认值约束,你也需要知道该约束的名称。假设默认值约束的名称为DF_Employees_HireDate
,你可以使用以下SQL语句来删除它:
ALTER TABLE Employees
DROP CONSTRAINT DF_Employees_HireDate;
2.3 查找约束名称
如果你不知道要删除的约束的确切名称,可以通过查询系统表来查找。以下是一个使用INFORMATION_SCHEMA.TABLE_CONSTRAINTS
视图的例子,该视图列出了数据库中所有表的约束信息:
SELECT
CONSTRAINT_NAME,
CONSTRAINT_TYPE
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
TABLE_NAME = 'Employees' AND TABLE_SCHEMA = 'dbo'; -- 替换为你的表名和模式名
如果你想要查找特定类型的约束(如主键或默认值),可以在WHERE子句中添加额外的条件来筛选CONSTRAINT_TYPE
。
另外,sys.key_constraints
和sys.default_constraints
系统视图分别提供了关于主键约束和默认值约束的更多详细信息。你可以根据需要查询这些视图来获取约束的名称和其他相关信息。
-- 主键约束
select name,parent_object_id,object_name(parent_object_id) as table_name,type
from sys.key_constraints
where parent_object_id=object_id('Employees');
-- 默认值约束
select name,parent_object_id,object_name(parent_object_id) as table_name,type
from sys.default_constraints
where parent_object_id=object_id('Employees');
2.4 注意事项
- 在删除主键约束之前,请确保这不是你数据库逻辑中依赖的关键部分,因为删除它可能会破坏数据完整性和外键关系。
- 删除默认值约束后,如果在插入新行时没有为受影响的列提供值,该列将接受其数据类型的默认值(如果有的话),否则可能会违反NOT NULL约束(如果列被定义为NOT NULL且没有默认值)。
- 始终在执行此类操作之前备份你的数据,以防万一需要恢复。