ALTER TABLE约束问题

发布于 2024-11-28 19:22:38 字数 423 浏览 1 评论 0原文

我正在尝试运行以下 SQL 语句：

IF OBJECT_ID('MyTable') IS NOT NULL
    DROP TABLE MyTable

SELECT
    a.UserId
INTO
    MyTable
FROM
    UsersTable a
WHERE
    a.UserId='12359670-1DC9-4A0A-8AE5-29B664C1A57E'


ALTER TABLE MyTable ALTER COLUMN UserId UNIQUEIDENTIFIER NOT NULL
ALTER TABLE MyTable ADD PRIMARY KEY(UserId)

但是，我收到以下错误： 无法在表“MyTable”中的可空列上定义 PRIMARY KEY 约束。

有什么想法吗？

原文

I'm trying to run the following SQL statement:

IF OBJECT_ID('MyTable') IS NOT NULL
    DROP TABLE MyTable

SELECT
    a.UserId
INTO
    MyTable
FROM
    UsersTable a
WHERE
    a.UserId='12359670-1DC9-4A0A-8AE5-29B664C1A57E'


ALTER TABLE MyTable ALTER COLUMN UserId UNIQUEIDENTIFIER NOT NULL
ALTER TABLE MyTable ADD PRIMARY KEY(UserId)

However, I get the following error:
Cannot define PRIMARY KEY constraint on nullable column in table 'MyTable'.

Any ideas?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

猫瑾少女 2024-12-05 19:22:38

这假设 SQL Server 基于 UNIQUEIDENTIFIER

在批处理

....
ALTER TABLE MyTable ALTER COLUMN UserId UNIQUEIDENTIFIER NOT NULL
GO
ALTER TABLE MyTable ADD PRIMARY KEY(UserId)

编译时，该列可以为空。所以把批次分开。

SQL 不是逐行的过程语言，

您必须在存储过程中执行此操作

ALTER TABLE MyTable ALTER COLUMN UserId UNIQUEIDENTIFIER NOT NULL
EXEC('ALTER TABLE MyTable ADD PRIMARY KEY(UserId)')

This assumes SQL Server based on UNIQUEIDENTIFIER

Put a GO between (or relevant batch separator if not SQL Server)

....
ALTER TABLE MyTable ALTER COLUMN UserId UNIQUEIDENTIFIER NOT NULL
GO
ALTER TABLE MyTable ADD PRIMARY KEY(UserId)

At batch compile time, the column is nullable. So break up the batches.

SQL isn't a line by line procedural language

You'll have to do this in a stored procedure

ALTER TABLE MyTable ALTER COLUMN UserId UNIQUEIDENTIFIER NOT NULL
EXEC('ALTER TABLE MyTable ADD PRIMARY KEY(UserId)')

回复收藏 0 原文

雾里花 2024-12-05 19:22:38

找到了解决方案。
我正在使用以下语句：

EXEC sp_ExecuteSQL N'ALTER TABLE MyTable ALTER COLUMN UserId UNIQUEIDENTIFIER NOT NULL'
EXEC sp_ExecuteSQL N'ALTER TABLE MyTable ADD PRIMARY KEY(UserId)'

Found a solution.
I'm using the following statement:

EXEC sp_ExecuteSQL N'ALTER TABLE MyTable ALTER COLUMN UserId UNIQUEIDENTIFIER NOT NULL'
EXEC sp_ExecuteSQL N'ALTER TABLE MyTable ADD PRIMARY KEY(UserId)'

回复收藏 0 原文

谜泪 2024-12-05 19:22:38

您不需要动态 SQL。只需先创建表和键即可。

CREATE TABLE MyTable (UserId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY);

INSERT INTO MyTable (UserId)
SELECT UserId
FROM UsersTable
WHERE UserId='12359670-1DC9-4A0A-8AE5-29B664C1A57E';

You don't need dynamic SQL. Just create the table and the key first.

CREATE TABLE MyTable (UserId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY);

INSERT INTO MyTable (UserId)
SELECT UserId
FROM UsersTable
WHERE UserId='12359670-1DC9-4A0A-8AE5-29B664C1A57E';

回复收藏 0 原文

~没有更多了~