Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 23 September 2019 12:45:37


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

Столбец 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

На главную страницу

Print  Версия для печати


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.