1.先创一张测试表，直接在创建时，设置UserId为主键
2.检查表有无主键约束
也可以使用SQL语句查询
SELECT name FROM sys.key_constraints
WHERE type = ‘PK‘ AND OBJECT_NAME(parent_object_id) = N‘Test‘
3.使用ALTER修改表的主键
每张表只能有一个主键，所以如果想要更改其他列为主键，需要先删除已有的主键
我们可以使用上面的SQL查询语句，先找到主键名进行删除，再重新设置主键
SELECT name FROM sys.key_constraints
WHERE type = ‘PK‘ AND OBJECT_NAME(parent_object_id) = N‘Test‘
--PK__Test__1788CC4C30E67086
ALTER TABLE Test DROP CONSTRAINT PK__Test__1788CC4C30E67086
检查主键是否已被删除
4.然后我们开始将UserName设置为主键
ALTER TABLE Test
ADD CONSTRAINT PK_Test_UserName PRIMARY KEY (UserName)
主键就从UserId更改成UserName.
5.再创建一张Test2表，这张表的Name列是外键是Test的UserName
6.查找外键
exec sp_pkeys ‘Test‘
exec sp_fkeys‘Test‘
select a.name as 约束名,object_name(b.parent_object_id) as 外键表,d.name as 外键列,
object_name(b.referenced_object_id) as 主健表,c.name as 主键列
from sys.foreign_keys A inner join sys.foreign_key_columns B on A.object_id=b.constraint_object_id
inner join sys.columns C on B.parent_object_id=C.object_id and B.parent_column_id=C.column_id
inner join sys.columns D on B.referenced_object_id=d.object_id and B.referenced_column_id=D.column_id
where object_name(B.referenced_object_id)=‘Test‘
7.删除外键约束
SELECT * FROM sys.foreign_keys
WHERE referenced_object_id=OBJECT_ID(‘Test‘)
--FK__Test2__Name__164452B1
ALTER TABLE dbo.Test2
DROP constraint FK__Test2__Name__164452B1
8.设置Student为新的外键
关于删除主外键的时候，如果主外键名是变量存储，可以这样操作
DECLARE @KeyName VARCHAR(255)
SELECT @KeyName = name FROM sys.key_constraints
WHERE type = ‘PK‘ AND OBJECT_NAME(parent_object_id) = N‘Test‘
IF(@KeyName IS NOT NULL)
BEGIN
DECLARE @sql varchar(max)
SET @sql = ‘ALTER TABLE Test DROP CONSTRAINT ‘ + @KeyName
EXEC (@sql) --要加上()
END
GO