我想在一个列上有一个唯一的约束,我将用guid填充这个列。但是,我的数据包含这些列的空值。如何创建允许多个空值的约束?
下面是一个示例场景。考虑这个模式:
CREATE TABLE People (
Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY,
Name NVARCHAR(250) NOT NULL,
LibraryCardId UNIQUEIDENTIFIER NULL,
CONSTRAINT UQ_People_LibraryCardId UNIQUE (LibraryCardId)
)
然后看看我想要实现的代码:
-- This works fine:
INSERT INTO People (Name, LibraryCardId)
VALUES ('John Doe', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');
-- This also works fine, obviously:
INSERT INTO People (Name, LibraryCardId)
VALUES ('Marie Doe', 'BBBBBBBB-BBBB-BBBB-BBBB-BBBBBBBBBBBB');
-- This would *correctly* fail:
--INSERT INTO People (Name, LibraryCardId)
--VALUES ('John Doe the Second', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');
-- This works fine this one first time:
INSERT INTO People (Name, LibraryCardId)
VALUES ('Richard Roe', NULL);
-- THE PROBLEM: This fails even though I'd like to be able to do this:
INSERT INTO People (Name, LibraryCardId)
VALUES ('Marcus Roe', NULL);
最后一个语句失败,并返回一条消息:
违反唯一键约束'UQ_People_LibraryCardId'。不能在对象'dbo.People'中插入重复的键。
我如何改变我的模式和/或唯一性约束,使它允许多个NULL值,同时仍然检查对实际数据的唯一性?
你不能用UNIQUE约束这样做,但你可以在触发器中这样做。
CREATE TRIGGER [dbo].[OnInsertMyTableTrigger]
ON [dbo].[MyTable]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Column1 INT;
DECLARE @Column2 INT; -- allow nulls on this column
SELECT @Column1=Column1, @Column2=Column2 FROM inserted;
-- Check if an existing record already exists, if not allow the insert.
IF NOT EXISTS(SELECT * FROM dbo.MyTable WHERE Column1=@Column1 AND Column2=@Column2 @Column2 IS NOT NULL)
BEGIN
INSERT INTO dbo.MyTable (Column1, Column2)
SELECT @Column2, @Column2;
END
ELSE
BEGIN
RAISERROR('The unique constraint applies on Column1 %d, AND Column2 %d, unless Column2 is NULL.', 16, 1, @Column1, @Column2);
ROLLBACK TRANSACTION;
END
END
根据我的经验——如果你认为一个列需要允许null,但也需要为它们存在的值设置UNIQUE,那么你可能对数据建模不正确。这通常表明您在同一个表中创建了一个单独的子实体作为一个不同的实体。将这个实体放在第二个表中可能更有意义。
在提供的例子中,我将把LibraryCardId放在一个单独的LibraryCards表中,并在People表中添加一个唯一的非空外键:
CREATE TABLE People (
Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY,
Name NVARCHAR(250) NOT NULL,
)
CREATE TABLE LibraryCards (
LibraryCardId UNIQUEIDENTIFIER CONSTRAINT PK_LibraryCards PRIMARY KEY,
PersonId INT NOT NULL
CONSTRAINT UQ_LibraryCardId_PersonId UNIQUE (PersonId),
FOREIGN KEY (PersonId) REFERENCES People(id)
)
这样,您就不需要为列既唯一又可为空而烦恼了。如果一个人没有借书证,他就不会在借书证表上有记录。此外,如果有关于借书证的其他属性(可能是Expiration Date或其他属性),那么现在就有了一个放置这些字段的逻辑位置。