更改SQL Server中的主键列 [英] Change primary key column in SQL Server

问题描述

更新

以下是查询的约束条件

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'history'

CONSTRAINT_NAME   COLUMN_NAME  ORDINAL_POSITION
PK_history        userKey       1
PK_history        name          2

这是查询的结果

SELECT * 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_NAME = 'history'

CONSTRAINT_NAME   CONSTRAINT_TYPE  IS_DEFERRABLE  INITIALLY_DEFERRED
PK_history        PRIMARY KEY      NO             NO

END UPDATE

我的主机通过ASP.NET Enterprise Manager提供了到我的SQL Server数据库的接口。

My host provides an interface to my SQL Server DB via ASP.NET Enterprise Manager.

我的历史记录表中有3列：

• userId （不允许键，整数，NULL）


• name （不允许键，字符串，NULL）


• id （不允许键，int，NULL）

• userId (key, int, NULL not allowed)
• name (key, string, NULL not allowed)
• id (not key, int, NULL allowed)

我想使id列成为唯一键。

I want to make the id column the only key.

要这样做，我相信我需要：

To do that, I believe I need to:

1. 确保该列中的任何行都没有空值


2. 将列设置为不允许为空


3. 将列添加为主键


4. 将其他两列删除为键
>

1. Make sure there are no NULLs in that column for any row
2. Set the column to not allow NULLs
3. Add the column as a primary key
4. Remove the other 2 columns as keys

但是，当我使用提供的UI时，它将永远无法工作。有时候，它看起来像是在尝试做某事，但是当我刷新列的视图时，它永远不会改变。它偶尔会创建一个临时表，看起来像它试图执行某些操作，但是永远不会被复制/覆盖我要更改的原始表。

However, when I use the UI provided, it never works. Sometimes it'll just look like it tries to do something but it never changes when I refresh the view of the columns. It occasionally creates a temp table that looks like it tried to do some of the operation, but that never gets copied/overwrites the original table that I'm trying to change.

当我尝试使用查询时，更改也不会显示。以下是我认为我需要的查询：

When I try using a query, the changes don't show up either. Here are the queries I think I need:

    SELECT * from history WHERE id is NULL     <---- This shows 0 results

    ALTER TABLE history
    ALTER COLUMN id int NOT NULL

    ALTER TABLE history ADD PRIMARY KEY (id)

    ALTER TABLE history
    DROP CONSTRAINT userId
    DROP CONSTRAINT name
    GO

I'我只是尝试禁止NULL并为id列添加主键。它似乎不起作用。有人可以指出我正确的方向吗？谢谢！

I've only gotten to the attempt to disallow NULLs and to add the primary key for the id column. It doesn't seem to work. Can someone point me in the right direction? Thanks!

推荐答案

假设您当前的主键约束称为pk_history，则可以替换以下行：

Assuming that your current primary key constraint is called pk_history, you can replace the following lines:

ALTER TABLE history ADD PRIMARY KEY (id)

ALTER TABLE history
DROP CONSTRAINT userId
DROP CONSTRAINT name

这些内容：

ALTER TABLE history DROP CONSTRAINT pk_history

ALTER TABLE history ADD CONSTRAINT pk_history PRIMARY KEY (id)

如果您不知道PK的名称，可以通过以下查询找到它：

If you don't know what the name of the PK is, you can find it with the following query:

SELECT * 
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
 WHERE TABLE_NAME = 'history'

这篇关于更改SQL Server中的主键列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！