|
|
Версия для печати
На главную страницу
Столбец UNIQUE с многочисленными NULL-значениями
Muthusamy Anantha Kumar (оригинал:
UNIQUE Column with multiple NULL values)
Перевод Моисеенко С.И.
Как вы знаете, при создании ограничения UNIQUE на столбце, допускающем NULL-значения,
SQL Server позволяет иметь только одно NULL-значение, тем самым поддерживается уникальность.
Однако возникают ситуации, когда нам нужно иметь более одного NULL-значения в столбце,
но по-прежнему поддерживать уникальность для не NULL значений.
В этой статье я собираюсь показать, как поддержать уникальность на столбце и иметь
при этом многочисленные NULL-значения.
Предположим, что у нас имеется база данных Employee, и мы хотим создать следующую
таблицу для хранения номеров карт социального страхования.
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Employee')
DROP DATABASE [Employee]
GO
Create Database [Employee]
GO
use [Employee]
GO
Create Table Emp
([Employee id] int not NULL constraint Emp_pk primary key clustered,
[First Name] varchar(100) NULL,
[Last Name] varchar(100) NULL,
[Nick Name] varchar(100) NULL,
[Social Security Number] int NULL)
GO
Мы знаем, что номер социального страхования уникален, поэтому добавим ограничение
UNIQUE на столбец [Social Security Number].
Alter table Emp Add constraint ssn_unique UNIQUE
([Social Security Number] )
Теперь попробуем вставить некоторые данные, как показано ниже.
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(1,'Robert','Bates','Bob',111213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(2,'Robert','Bates','Bob',121213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(3,'Robert','William','Rob',131213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(4,'Sonia','Keira','Sony',131413426)
Как мы знаем, номер социального страхования уникален. Однако в ситуациях людей,
приезжающих в страну с рабочей визой, требуется некоторое время для получения номера
социального страхования. В течение этого времени соответствующими данными должен
быть NULL.
Теперь давайте вставим одну строку без номера социального страхования:
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(5,'Mellisa','Brown','Mel',NULL)
Результат
(1 row(s) affected)
Теперь вставим еще одну строку без номера социального страхования:
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(6,'Sibey','Chikhs','Ciby',NULL)
Результат
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'ssn_unique'. Cannot insert duplicate key in object 'dbo.Emp'.
The statement has been terminated.
(Нарушение ограничения UNIQUE KEY 'ssn_unique'. Невозможно вставить дубликат ключа в объект 'dbo.Emp'.
Выполнение оператора прервано.)
Это нормальное поведение ограничения UNIQUE на столбце с NULL. Оно допускает наличие
только одной строки с NULL-значением. Однако это не то поведение, которое мы хотим
получить для этого столбца. Нам требуется, чтобы столбец принимал уникальные значения
и многочисленные NULL-значения.
Этого можно достичь с помощью вычисляемого столбца и добавления ограничения на вычисляемый
столбец, а не на сам столбец номера социального страхования.
Теперь давайте снова создадим базу данных с нуля, только теперь мы добавим вычисляемый
столбец, как показано ниже.
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Employee')
DROP DATABASE [Employee]
GO
Create Database [Employee]
GO
use [Employee]
GO
Create Table Emp
([Employee id] int not NULL constraint Emp_pk primary key clustered,
[First Name] varchar(100) NULL,
[Last Name] varchar(100) NULL,
[Nick Name] varchar(100) NULL,
[Social Security Number] int NULL)
GO
Alter table Emp Add MySSN as case when [Social Security Number]
is NULL then [Employee id] else [Social Security Number] end
GO
Давайте теперь наложим ограничения UNIQUE на вычисляемый столбец:
Alter table Emp Add constraint ssn_unique UNIQUE ([MySSN] )
go
Теперь вставим некоторые данные:
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(1,'Robert','Bates','Bob',111213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(2,'Robert','Bates','Bob',121213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(3,'Robert','William','Rob',131213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(4,'Sonia','Keira','Sony',131413426)
Попробуем вставить одну строку без номера социального страхования:
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(5,'Mellisa','Brown','Mel',NULL)
Результат
(1 row(s) affected)
Вставим еще одну строку без номера социального страхования:
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(6,'Sibey','Chikhs','Ciby',NULL)
Результат
(1 row(s) affected)
Если столбец номера социального страхования имеет тип varchar, то мы можем создать
вычисляемый столбец и ограничение показанным ниже способом, чтобы добиться той же
самой цели - получения уникальности с множественными NULL-значениями.
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Employee')
DROP DATABASE [Employee]
go
Create Database [Employee]
go
use [Employee]
go
Create Table Emp
([Employee id] int not NULL constraint Emp_pk primary key clustered,
[First Name] varchar(100) NULL,
[Last Name] varchar(100) NULL,
[Nick Name] varchar(100) NULL,
[Social Security Number] varchar(12) NULL)
go
Давайте создадим вычисляемый столбец для номера социального страхования:
Alter table Emp Add MySSN as case when [Social Security Number]
is NULL then convert(varchar(12),[Employee id]) else [Social Security Number] end
go
Теперь добавим ограничение UNIQUE на вычисляемый столбец:
Alter table Emp Add constraint ssn_unique UNIQUE ([MySSN] )
go
Теперь вставим некоторые данные:
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(1,'Robert','Bates','Bob','111-21-3422')
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(2,'Robert','Bates','Bob','121-21-3422')
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(3,'Robert','William','Rob','131-21-3422')
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(4,'Sonia','Keira','Sony','131-41-3426')
Теперь вставим строку без номера социального страхования:
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(5,'Mellisa','Brown','Mel',NULL)
Результат
(1 row(s) affected)
А теперь вставим еще одну строку без номера социального страхования:
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(6,'Sibey','Chikhs','Ciby',NULL)
Результат
(1 row(s) affected)
Заключение
Мы создали столбец в таблице, который содержит уникальные (UNIQUE) значения, а также
многочисленные NULL-значения с помощью значений первичного ключа, вычисляемого столбца
и ограничения UNIQUE на вычисляемом столбце.
21-11-2007
На главную страницу
Версия для печати
|
|