关于sql server：如何创建一个也允许空值的唯一约束？ |
您所在的位置：网站首页 › 数据库sql不能空且唯一值 › 关于sql server：如何创建一个也允许空值的唯一约束？ |
|
我想在一列上有一个唯一的约束，我将用guid填充该列。但是，我的数据包含此列的空值。如何创建允许多个空值的约束？ 下面是一个示例场景。考虑此架构： 123456CREATE TABLE People ( Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY, Name NVARCHAR(250) NOT NULL, LibraryCardId UNIQUEIDENTIFIER NULL, CONSTRAINT UQ_People_LibraryCardId UNIQUE (LibraryCardId) )然后，请参阅此代码，了解我要实现的目标： 12345678910111213141516171819-- 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);最终语句失败，并显示一条消息： Violation of UNIQUE KEY constraint 'UQ_People_LibraryCardId'. Cannot insert duplicate key in object 'dbo.People'. 如何更改模式和/或唯一性约束，使其允许多个NULL值，同时仍检查实际数据的唯一性？ 相关讨论 要投票的标准兼容性连接问题：connect.microsoft.com/sqlserver/feedback/details/299229 如何在空列上创建唯一索引的可能重复项？ 唯一约束并允许空值。？这是常识。这是不可能的 @轻浮，最好不要指"常识"。这不是有效的论点。尤其是考虑到null不是一个值，而是没有值。根据SQL标准，null不等于null。那么，为什么多个null应该是唯一性冲突呢？您要查找的实际上是ANSI标准SQL:92、SQL:1999和SQL:2003的一部分，即唯一约束必须不允许重复的非空值，但接受多个空值。 然而，在SQL Server的Microsoft世界中，只允许一个空值，但不允许多个空值… 在SQL Server 2008中，可以基于不包含空值的谓词定义唯一的筛选索引： 123CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull ON YourTable(yourcolumn) WHERE yourcolumn IS NOT NULL;在早期版本中，可以使用带有非空谓词的视图来强制约束。 相关讨论 这可能是最好的方法。不确定是否有任何性能影响？有人吗？ 同意Kjensen。非常喜欢这个提示。 我正试图在SQL Server 2008 Express Edition中执行此操作，但得到一个错误，如下所示：在[SLS-CP].dbo.masterfileentry(mailingid)上创建唯一的非聚集索引uc mailingid，其中mailingid不是空值，这会导致：msg 156，level 15，state 1，line 3关键字"where"附近的语法不正确。如果我删除了DDL运行良好的WHERE子句，当然，它不会做我需要它做的事情。有什么想法吗？ 肯尼思我也有同样的问题。在生产版(企业版或专业版)上可以很好地工作，所以我猜这只是一个bug，或者是有意的破坏。 @Simon_Weaver"在创建唯一约束和创建独立于约束的唯一索引之间没有显著差异。"msdn.microsoft.com/en-us/library/ms187019.aspx 除非我弄错了，否则您不能像关闭唯一约束那样从唯一索引创建外键。(至少当我尝试时SSMS向我抱怨)最好能有一个可以为空的列，它总是唯一的(当不是空的时候)作为外键关系的源。 真是个很好的答案。可惜它被公认为答案的人掩盖了。这个解决方案几乎没有引起我的注意，但是现在它在我的实现中像奇迹一样工作。 @瓦卡诺：你为什么要挂一个空列的外键呢？ SQL 2005及以下版本的另一种选择是计算列，也就是"NullBuster"技巧。stackoverflow.com/a/191729/132461它可以避免将数据库与另一个视图混淆，您只需要使用另一个列，通常命名为column a nullbuster(如果column a是您希望成为ansi nullable unique的列)。在columna nullbuster上放置一个唯一的索引(或约束来表达业务意图)，它将强制columna上的唯一性 正是我在寻找的。非常感谢你 当您转到索引视图并只更新索引上的任何内容时，sm sql management studio似乎意外地删除了这个where子句？ 这很管用。感谢你抽出时间来。 是否可以在GUI中配置WHERE子句？ @斯图尔特，你能接受这个答案吗？ @彼得-是的，可以。转到索引属性的"过滤器"页，并将WHERE子句放在其中(不带WHERE关键字)。示例：([yourcolumn] IS NOT NULL)。 要投票的标准兼容性连接问题：connect.microsoft.com/sqlserver/feedback/details/299229 伟大的！！！！6年后，它就是I搜索。在sqlserver 2012/2014上，这个语法是正常的。 在我的例子中，由于这是"过滤的"(有一个WHERE子句)，这要求在创建索引之前"设置ansi_padding on"，然后"设置ansi_padding off"。但是，如果我用这些标志创建一个索引，那么之后的每个插入都需要这些标志。糟糕的数据库设计——为什么索引从根本上改变了我与表的交互方式？ 实体框架用户，请检查：stackoverflow.com/questions/24361518/&hellip； 到了2017年，这是否已经不能成为公认的解决方案？对于2008年之前的SQL，我们真的需要建议吗：) SQL Server 2008+您可以使用WHERE子句创建一个接受多个空值的唯一索引。请参阅下面的答案。 在SQL Server 2008之前不能创建唯一约束并允许空值。您需要设置newID()的默认值。 在创建唯一约束之前，将现有值更新为newid()，其中为空。 相关讨论 这将回顾性地向现有行添加值，如果是这样的话，这就是我需要做的，谢谢？ 您需要运行update语句，将现有值设置为newid()，其中现有字段为空。 好吧，我想我会这样做的，这似乎比创建视图更简单，谢谢。 如果您使用的是SQL Server 2008或更高版本，请参阅下面的100多个升级投票的答案。可以向唯一约束添加WHERE子句。 这只是MSSQL？ 这个问题也会影响到ADO.NET数据表。因此，即使我可以使用此方法在支持字段中允许空值，DataTable也不会让我首先将空值存储在唯一列中。如果有人知道解决办法，请把它贴在这里 伙计们确保你向下滚动并以600票的优势阅读答案。现在已经不到100岁了。 注意不匹配索引警告…当结合使用WHERE子句的唯一索引时，执行原语可以增加CPU使用率。SQL Server 2008及更高版本 只需筛选唯一索引： 123CREATE UNIQUE NONCLUSTERED INDEX UQ_Party_SamAccountName ON dbo.Party(SamAccountName) WHERE SamAccountName IS NOT NULL;在较低版本中，仍然不需要物化视图 对于SQL Server 2005及更早版本，您可以在没有视图的情况下执行此操作。我刚在我的一个表中添加了一个独特的约束，就像你要求的那样。鉴于我希望在列SamAccountName中具有唯一性，但我希望允许多个空值，因此我使用了物化列而不是物化视图： 1234ALTER TABLE dbo.Party ADD SamAccountNameUnique AS (Coalesce(SamAccountName, Convert(varchar(11), PartyID))) ALTER TABLE dbo.Party ADD CONSTRAINT UQ_Party_SamAccountName UNIQUE (SamAccountNameUnique)您只需在计算列中放入一些内容，当实际需要的唯一列为空时，这些内容将保证整个表的唯一性。在这种情况下，PartyID是一个标识列，数字永远不会与任何SamAccountName匹配，因此它对我很有用。您可以尝试自己的方法，确保您了解数据的域，这样就不可能与真实数据交叉。这很简单，只需预先准备一个这样的区分符： 1Coalesce('n' + SamAccountName, 'p' + Convert(varchar(11), PartyID))即使有一天PartyID变为非数字，并且可能与SamAccountName重合，现在也无关紧要了。 请注意，包含计算列的索引的存在隐式地导致每个表达式结果与表中的其他数据一起保存到磁盘，这确实会占用额外的磁盘空间。 请注意，如果不需要索引，您仍然可以通过将关键字PERSISTED添加到列表达式定义的末尾，使表达式预先计算到磁盘，从而节省CPU。 在SQL Server 2008及更高版本中，如果可能的话，一定要使用筛选后的解决方案！ 争议 请注意，一些数据库专业人员会将此视为"代理空值"的情况，这肯定有问题(主要是由于在试图确定某个值是真实值还是丢失数据的代理值时出现的问题；也可能是非空代理值的数量与疯狂值相乘的问题)。 不过，我相信这个案子是不同的。我要添加的计算列永远不会用于确定任何内容。它本身没有意义，也不编码在其他正确定义的列中未单独找到的信息。不得选择或使用。 所以，我的故事是，这不是一个代理无效，我坚持它！由于我们实际上不希望非空值用于除欺骗UNIQUE索引忽略空值以外的任何目的，因此我们的用例没有出现正常代理空创建时出现的任何问题。 尽管如此，我并不反对使用索引视图，但是它带来了一些问题，比如使用SCHEMABINDING的需求。在基表中添加一个新列是很有趣的(您至少需要删除索引，然后删除视图或将视图更改为不受架构约束)。请参阅在SQL Server(2005)(以及更高版本)(2000)中创建索引视图所需的完整(长)列表。 更新 如果您的列是数字列，则可能存在确保使用Coalesce的唯一约束不会导致冲突的挑战。在这种情况下，有一些选择。一种可能是使用一个负数，将"surrogate nulls"只放在负数范围内，而"real values"只放在正数范围内。或者，可以使用以下模式。在表Issue中(其中IssueID为PRIMARY KEY，可能有也可能没有TicketID，但如果有，则必须是唯一的。 1234ALTER TABLE dbo.Issue ADD TicketUnique AS (CASE WHEN TicketID IS NULL THEN IssueID END); ALTER TABLE dbo.Issue ADD CONSTRAINT UQ_Issue_Ticket_AllowNull UNIQUE (TicketID, TicketUnique);如果issueid 1有票据123，那么UNIQUE约束将在值(123，空)上。如果issueid 2没有票据，它将打开(空，2)。有人认为这个约束不能对表中的任何行进行复制，并且仍然允许多个空值。 对于使用Microsoft SQL Server Manager并希望创建唯一但可以为空的索引的用户，可以像通常那样创建唯一索引，然后在新索引的索引属性中，从左侧面板中选择"筛选器"，然后输入筛选器(这是WHERE子句)。它应该是这样的： 1([YourColumnName] IS NOT NULL)这适用于MSSQL 2012 相关讨论 这里介绍了如何在Microsoft SQL Server Management Studio下创建筛选索引，并将其完美地工作：msdn.microsoft.com/en-us/library/cc280372.aspx当我应用以下唯一索引时： 123CREATE UNIQUE NONCLUSTERED INDEX idx_badgeid_notnull ON employee(badgeid) WHERE badgeid IS NOT NULL;每个非空更新和插入都失败，错误如下： UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'. 我在msdn上找到这个 SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. 为了让这个正常工作，我做了这个 Right click [Database]-->Properties-->Options-->Other Options-->Misscellaneous-->Arithmetic Abort Enabled -->true 我相信可以在代码中使用 1ALTER DATABASE"DBNAME" SET ARITHABORT ON但我没有测试过这个 创建只选择非NULL列的视图，并在该视图上创建UNIQUE INDEX： 1234567CREATE VIEW myview AS SELECT * FROM mytable WHERE mycolumn IS NOT NULL CREATE UNIQUE INDEX ux_myview_mycolumn ON myview (mycolumn)请注意，您需要在视图上执行INSERT和UPDATE，而不是在表上执行。 您可以使用INSTEAD OF触发器： 123456789CREATE TRIGGER trg_mytable_insert ON mytable INSTEAD OF INSERT AS BEGIN INSERT INTO myview SELECT * FROM inserted END 相关讨论 那么我需要更改DAL来插入视图吗？ 您可以创建触发器而不是插入。也可以在设计师那里完成 右键单击索引>属性以获取此窗口 相关讨论 如果你能接触到设计师的话，很好的选择 不过，正如我刚刚发现的，一旦您的表中有了数据，就不能再使用设计器了。它似乎忽略了筛选器，并且任何试图更新的表都会显示消息"不允许重复的键"。可以在聚集索引视图上创建唯一约束 您可以这样创建视图： 123CREATE VIEW dbo.VIEW_OfYourTable WITH SCHEMABINDING AS SELECT YourUniqueColumnWithNullValues FROM dbo.YourTable WHERE YourUniqueColumnWithNullValues IS NOT NULL;唯一的约束如下： 12CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_OFYOURTABLE ON dbo.VIEW_OfYourTable(YourUniqueColumnWithNullValues)也许考虑一个"INSTEAD OF"触发器，自己检查一下？在列上使用非聚集(非唯一)索引来启用查找。 您可以创建一个instead-of-trigger来检查是否满足特定条件和错误。在较大的表上创建索引的成本可能很高。 下面是一个例子： 12345678910111213141516171819202122CREATE TRIGGER PONY.trg_pony_unique_name ON PONY.tbl_pony INSTEAD OF INSERT, UPDATE AS BEGIN IF EXISTS( SELECT TOP (1) 1 FROM inserted i GROUP BY i.pony_name HAVING COUNT(1) > 1 ) OR EXISTS( SELECT TOP (1) 1 FROM PONY.tbl_pony t INNER JOIN inserted i ON i.pony_name = t.pony_name ) THROW 911911, 'A pony must have a name as unique as s/he is. --PAS', 16; ELSE INSERT INTO PONY.tbl_pony (pony_name, stable_id, pet_human_id) SELECT pony_name, stable_id, pet_human_id FROM inserted END如前所述，当涉及到UNIQUE CONSTRAINT时，SQL Server不实现ANSI标准。自2007年起，Microsoft Connect上就有此问题的通知单。如前所述，目前最好的选择是使用另一个答案或计算列中所述的过滤索引，例如： 1234567891011CREATE TABLE [Orders] ( [OrderId] INT IDENTITY(1,1) NOT NULL, [TrackingId] varchar(11) NULL, ... [ComputedUniqueTrackingId] AS ( CASE WHEN [TrackingId] IS NULL THEN '#' + cast([OrderId] as varchar(12)) ELSE [TrackingId_Unique] END ), CONSTRAINT [UQ_TrackingId] UNIQUE ([ComputedUniqueTrackingId]) )如果您使用文本框创建一个注册表，并使用insert和ur文本框，则此代码为空，然后单击Submit按钮。 12CREATE UNIQUE NONCLUSTERED INDEX [IX_tableName_Column] ON [dbo].[tableName]([columnName] ASC) WHERE [columnName] !=`''`; 12345CREATE UNIQUE NONCLUSTERED INDEX [UIX_COLUMN_NAME] ON [dbo].[Employee]([Username] ASC) WHERE ([Username] IS NOT NULL) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0) ON [PRIMARY];使用UNIQUE约束不能这样做，但可以在触发器中这样做。 12345678910111213141516171819202122232425 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 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |