Ограничение UNIQUE SQL Server с дубликатом АННУЛИРУЕТ [копируют]
Мои фавориты:
- опция + команда + [ для чистки добавления отступа
- "lorem", ВКЛАДКА для вставки текста заполнителя
6 ответов
If you're using SQL Server 2008 (won't work for earlier version) there is the concept of a filtered index. You can create the index on a filtered subset of the table.
CREATE UNIQUE INDEX indexName ON tableName(columns) INCLUDE includeColumns
WHERE columnName IS NOT NULL
You can create a view in which you select only not null values and create an index on it.
Here is the source - Creating Indexed Views
If you're using SQL Server 2008, have a look into Filtered Indexes to achieve what you want.
For older version of SQL Server, a possible alternative to a trigger involves a computed column:
- Create a computed column which uses the value of your "unique" column if it's not
NULL, otherwise it uses the value of the row's Primary Key column (or any column which will be unique). - Apply a
UNIQUEconstraint to the computed column.
http://www.sqlmag.com/article/articleid/98678/sql_server_blog_98678.html
will work only in Microsoft SQL Server 2008
You should use UNIQUEIDENTIFIER in that column, can be NULL and also is unique by definition. Hope that helps.
Дубликат этого вопроса ?
уловка с вычисляемым столбцом широко известна как "уничтожение нуля"; мои примечания: Стив Касс:
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)
Работает на SQL Server 2000. Вам может понадобиться ARITHABORT , например,
ALTER DATABASE MyDatabase SET ARITHABORT ON