SQLServer数据库，使用Alter修改表的主外键结构

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