使用 SQL Server 我只需要非空值的唯一索引 [英] With SQL Server I want a unique index on only the non-null values

问题描述

可能的重复:
具有重复 NULL 的 SQL Server UNIQUE 约束

在 Sql Server 中，我希望有一个包含空值和实值的列.如果该列有一个值，我想确保它是唯一的.对于该列，该列可以有多行且为 null.

In Sql Server I would like to have a column that has both nulls and real values. If the column has a value, I want to insure it is unique. The column could have multiple rows with null for this column.

例如，假设我有一个包含 3 列的书籍表格，即:

For example suppose I had a table of books with 3 columns, namely:

Book Number - identity
Title - varchar
ISBN - char

现在该表将有一个带有标识列的主键，这很好.问题将在 ISBN 列上.我不希望任何 ISBN 号多次出现在表中，但是会有很多书没有 ISBN 号(因为我不知道).

Now the table would have a primary key with the identity column, which is good. The question would be on the ISBN column. I do not want any ISBN number to appear in the table more than once, but there will be many books with no ISBN number (since I don't know it).

有什么方法(或最好的方法)可以对 ISBN 实施这种限制吗?

Is there any way (or what is the best way) to inforce this constraint on ISBN?

推荐答案

在 SQL Server 2008 中，您可以创建过滤索引，例如:

In SQL Server 2008, you can create a filtered index, like:

CREATE UNIQUE INDEX indexName ON tableName(isbn) WHERE isbn IS NOT NULL

在早期版本中，创建一个计算列，存在时为ISBN，ISBN为空时为主键:

In earlier versions, create a calculated column which is the ISBN when it exists, and the primary key when the ISBN is null:

CREATE TABLE tableName (
    pk int identity(1,1) primary key,
    isbn int NULL,
    nullbuster as (case when isbn is null then pk else 0 end),
    CONSTRAINT dupNulls_uqX UNIQUE (isbn)
)

复制自 SQL Server UNIQUE 约束与重复的 NULLs，所以这是在维基模式:)

Copied from SQL Server UNIQUE constraint with duplicate NULLs, so this is in wiki mode :)

这篇关于使用 SQL Server 我只需要非空值的唯一索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！