如何使列允许null或唯一的序列

I've seen a few web searches and responses to this but it seems they all involve views.

我已经看过一些网络搜索和对此的回复,但似乎它们都涉及到视图。

How can I adjust a column so that it only allows NULL or a unique value?

如何调整列以使其仅允许NULL或唯一值?

In my case there's a table with stock items in which only the server items have a serial number. The rest are null. I would like to enforce some kind of control against entering the same serials.

在我的例子中,有一个包含库存商品的表格,其中只有服务器商品有序列号。其余的都是空的。我想对进入相同的连续剧实施某种控制。

Also, I cannot redesign the architecture as I'm writing a new front end to a still live site.

此外,我无法重新设计架构,因为我正在为静态网站编写新的前端。

8 个解决方案

#1

Can you validate from the frontend? Before you insert or commit your data make sure it is unique or NULL.

你能从前端验证吗?在插入或提交数据之前,请确保它是唯一的或NULL。

#2

In MySQL, a UNIQUE column that's also nullable allows any number of nulls (in all engines). So, if you're using MySQL, just add a UNIQUE constraint to the column of interest. This behavior is the SQL standard and is also supported by PostgreSQL and SQLite (and apparently Oracle for single-column UNIQUE constraint only, though I can't confirm this).

在MySQL中,一个也可以为空的UNIQUE列允许任意数量的空值(在所有引擎中)。因此,如果您使用的是MySQL,只需在感兴趣的列中添加UNIQUE约束即可。这种行为是SQL标准,PostgreSQL和SQLite也支持这种行为(显然Oracle只支持单列UNIQUE约束,但我无法证实这一点)。

However, this SQL standard behavior won't necessarily work for all other RDBMS engines, such as SQL Server or DB2; if you specify what engines you need to support, we may be able to offer more specific suggestions.

但是,此SQL标准行为不一定适用于所有其他RDBMS引擎,例如SQL Server或DB2;如果您指定需要支持的引擎,我们可能会提供更具体的建议。

#3

SQL Server allows creating UNIQUE indexes that accept NULL values, though it takes a little trick.

SQL Server允许创建接受NULL值的UNIQUE索引,尽管需要一点技巧。

Create a view that selects only non-NULL columns and create the UNIQUE INDEX on the view:

创建一个仅选择非NULL列的视图,并在视图上创建UNIQUE INDEX:

CREATE VIEW myview
AS
SELECT  *
FROM    mytable
WHERE   mycolumn IS NOT NULL

CREATE UNIQUE INDEX ux_myview_mycolumn ON myview (mycolumn)

Note that you'll need to perform INSERT's and UPDATE's on the view instead of table.

请注意,您需要在视图而不是表上执行INSERT和UPDATE。

You may do it with an INSTEAD OF trigger:

您可以使用INSTEAD OF触发器执行此操作:

CREATE TRIGGER trg_mytable_insert ON mytable
INSTEAD OF INSERT
AS
BEGIN
        INSERT
        INTO    myview
        SELECT  *
        FROM    inserted
END

#4

If you're using MS SQL Server 2008, then you can use a filtered unique index to achieve this. Have a look at this forum thread for details.

如果您正在使用MS SQL Server 2008,则可以使用筛选的唯一索引来实现此目的。有关详细信息,请查看此论坛帖子。

#5

You could do it with a Trigger instead...

你可以用触发器代替......

Index the column but without a unique constraint Allow nulls Stick a trigger on the table that on INSERT and UPDATE you check if the column value your attempting to insert is either NULL or doesn't already exist in the table

索引列但没有唯一约束允许空值在INSERT和UPDATE上的表上粘贴触发器,检查您尝试插入的列值是否为NULL或表中是否已存在

#6

I don't know what database you're using, but in Postgres, you can define a "before insert or update" trigger that can return a special value to reject the insert. You could use it to enforce a constraint like this on your table if there isn't a native way to do it.

我不知道你正在使用什么数据库,但是在Postgres中,你可以定义一个“before insert or update”触发器,它可以返回一个特殊值来拒绝插入。如果没有本地方法,您可以使用它在您的表上强制执行此类约束。

#7

What platform is this for?

这个平台是什么?

For SQL Server see this blog post which shows a technique using indexed views.

对于SQL Server,请参阅此博客文章,其中显示了使用索引视图的技术。

#8

For Sql Server 2008, here's a copy of the filtered index solution linked by MattB. I write it here to make sure people dont miss it:

对于Sql Server 2008,这里是由MattB链接的过滤索引解决方案的副本。我在这里写它是为了确保人们不要错过它:

CREATE UNIQUE NONCLUSTERED INDEX [UniqueExceptNulls]
ON [Customers] (SSN)
WHERE [SSN] IS NOT NULL

#1