[sql] NULL 열에 고유 인덱스를 만드는 방법은 무엇입니까?

Program / 글쓴이

SQL Server 2005를 사용하고 있습니다. NULLS를 허용하면서 열의 값을 고유하게 제한하고 싶습니다.

내 현재 솔루션에는 다음과 같은 뷰의 고유 인덱스가 포함됩니다.

CREATE VIEW vw_unq WITH SCHEMABINDING AS
    SELECT Column1
      FROM MyTable
     WHERE Column1 IS NOT NULL

CREATE UNIQUE CLUSTERED INDEX unq_idx ON vw_unq (Column1)

더 좋은 아이디어가 있습니까?

답변

고유의 목적을 위반하므로 그렇게 할 수 없습니다.

그러나이 사람은 적절한 해결 방법을 가지고있는 것 같습니다.
http://sqlservercodebook.blogspot.com/2008/04/multiple-null-values-in-unique-index-in.html

답변

SQL Server 2008을 사용하여 필터링 된 인덱스를 만들 수 있습니다 : http://msdn.microsoft.com/en-us/library/cc280372.aspx . (Simon이 이것을 주석으로 추가 한 것을 보았지만 주석을 쉽게 놓칠 수 있으므로 자체 답변이 필요하다고 생각했습니다.)

또 다른 옵션은 고유성을 확인하는 트리거이지만 성능에 영향을 미칠 수 있습니다.

답변

계산 된 열 트릭은 “널 버스터”로 널리 알려져 있습니다. 내 메모 신용 Steve Kass :

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)
)

답변

엄밀히 말하면, 고유 한 nullable 열 (또는 열 집합)은 한 번만 NULL (또는 NULL 레코드)이 될 수 있습니다. 동일한 값 (NULL 포함)이 두 번 이상 있으면 고유 제약 조건을 위반하는 것이 분명하기 때문입니다.

그러나 이것이 “고유 한 nullable 열”개념이 유효 함을 의미하지는 않습니다. 관계형 데이터베이스에서 실제로 구현하려면 이러한 종류의 데이터베이스가 제대로 작동하도록 정규화되어야하며, 정규화에는 일반적으로 엔터티 간의 관계를 설정하기 위해 여러 (엔티티가 아닌) 추가 테이블을 추가해야한다는 점을 명심해야합니다. .

하나의 “고유 한 nullable 열”만 고려하여 기본 예제를 살펴 보겠습니다. 더 많은 열로 쉽게 확장 할 수 있습니다.

다음과 같은 테이블로 표시된 정보를 가정합니다.

create table the_entity_incorrect
(
  id integer,
  uniqnull integer null, /* we want this to be "unique and nullable" */
  primary key (id)
);

uniqnull을 분리하고 두 번째 테이블을 추가하여 uniqnull 값과 the_entity 사이의 관계를 설정하여이를 수행 할 수 있습니다 (uniqnull이 the_entity “내부”에있는 대신).

create table the_entity
(
  id integer,
  primary key(id)
);

create table the_relation
(
  the_entity_id integer not null,
  uniqnull integer not null,

  unique(the_entity_id),
  unique(uniqnull),
  /* primary key can be both or either of the_entity_id or uniqnull */
  primary key (the_entity_id, uniqnull),
  foreign key (the_entity_id) references the_entity(id)
);

uniqnull 값을 the_entity의 행에 연결하려면 the_relation에 행을 추가해야합니다.

the_entity의 행에 대해 uniqnull 값이 연결되지 않은 경우 (즉, the_entity_incorrect에 NULL을 넣은 값) the_relation에 행을 추가하지 않습니다.

uniqnull의 값은 모든 the_relation에 대해 고유하며, the_entity의 각 값에 대해 기본 및 외래 키가이를 적용하기 때문에 the_relation에 최대 하나의 값이있을 수 있습니다.

그런 다음 uniqnull의 값 5가 the_entity id 3과 연결되는 경우 다음을 수행해야합니다.

start transaction;
insert into the_entity (id) values (3);
insert into the_relation (the_entity_id, uniqnull) values (3, 5);
commit;

그리고 the_entity에 대한 id 값 10에 uniqnull 대응 항목이 없으면 다음 작업 만 수행합니다.

start transaction;
insert into the_entity (id) values (10);
commit;

이 정보를 비정규 화하고 the_entity_incorrect와 같은 테이블이 보유 할 데이터를 얻으려면 다음을 수행해야합니다.

select
  id, uniqnull
from
  the_entity left outer join the_relation
on
  the_entity.id = the_relation.the_entity_id
;

“왼쪽 외부 조인”연산자는 the_entity의 모든 행이 결과에 나타나도록하여 the_relation에 일치하는 열이없는 경우 uniqnull 열에 NULL을 넣습니다.

잘 정규화 된 데이터베이스 (및 이에 상응하는 비정규 화 뷰 및 절차)를 설계하는 데 며칠 (또는 몇 주 또는 몇 달) 동안 소요 된 모든 노력은 수년 (또는 수십 년)의 고통과 낭비되는 리소스를 절약 할 수 있다는 점을 기억하십시오.

답변