조건부 고유 제약

조건부 고유 제약

열 집합에 고유한 제약 조건을 적용해야 하지만 열의 값이 하나만 적용됩니다.

예를 들어 Table(ID, Name, Record Status)과 같은 테이블이 있습니다.

RecordStatus에는 1 또는 2(액티브 또는 삭제) 값만 있을 수 있으며, 동일한 ID를 가진 삭제된 레코드가 여러 개 있어도 상관 없으므로 RecordStatus = 1일 때만 (ID, RecordStatus)에 대한 고유한 제약 조건을 만들고 싶습니다.

방아쇠를 당기는 것 말고도 할 수 있나요?

SQL Server 2005를 사용하고 있습니다.

보세요, 필터링된 색인입니다.문서(강조 지뢰):

필터링된 인덱스는 잘 정의된 데이터 하위 집합에서 선택하는 쿼리를 처리하는 데 특히 적합한 최적화된 비클러스터형 인덱스입니다.필터 술어를 사용하여 테이블의 행 일부를 인덱싱합니다.잘 설계된 필터링된 인덱스는 전체 테이블 인덱스에 비해 쿼리 성능을 향상시키고 인덱스 유지 보수 및 저장 비용을 절감할 수 있습니다.

다음은 고유 인덱스와 필터 술어를 조합한 예입니다.

create unique index MyIndex
on MyTable(ID)
where RecordStatus = 1;

이는 기본적으로 의 고유성을 강제한다.ID언제RecordStatus이1.

이 인덱스를 작성한 후 고유성 위반이 발생하면 다음 오류가 발생합니다.

메시지 2601, 레벨 14, 상태 1, 13 회선
개체 'dbo'에 중복된 키 행을 삽입할 수 없습니다.고유한 색인 'MyIndex'가 있는 MyTable.중복되는 키 값은 (999)입니다.

_{주의: 필터링된 인덱스는 SQL Server 2008에서 도입되었습니다.이전 버전의 SQL Server에 대해서는 다음 답변을 참조하십시오.}

이와 같이 체크 제약 조건을 추가합니다.차이점은 Status = 1 및 Count > 0이면 false를 반환한다는 것입니다.

http://msdn.microsoft.com/en-us/library/ms188258.aspx

CREATE TABLE CheckConstraint
(
  Id TINYINT,
  Name VARCHAR(50),
  RecordStatus TINYINT
)
GO

CREATE FUNCTION CheckActiveCount(
  @Id INT
) RETURNS INT AS BEGIN

  DECLARE @ret INT;
  SELECT @ret = COUNT(*) FROM CheckConstraint WHERE Id = @Id AND RecordStatus = 1;
  RETURN @ret;

END;
GO

ALTER TABLE CheckConstraint
  ADD CONSTRAINT CheckActiveCountConstraint CHECK (NOT (dbo.CheckActiveCount(Id) > 1 AND RecordStatus = 1));

INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 1);

INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 2);
-- Msg 547, Level 16, State 0, Line 14
-- The INSERT statement conflicted with the CHECK constraint "CheckActiveCountConstraint". The conflict occurred in database "TestSchema", table "dbo.CheckConstraint".
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);

SELECT * FROM CheckConstraint;
-- Id   Name         RecordStatus
-- ---- ------------ ------------
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  1
-- 2    Oh no!       1
-- 2    Oh no!       2

ALTER TABLE CheckConstraint
  DROP CONSTRAINT CheckActiveCountConstraint;

DROP FUNCTION CheckActiveCount;
DROP TABLE CheckConstraint;

삭제된 레코드를 제약이 없는 테이블로 이동하거나 두 테이블의 UNION 뷰를 사용하여 단일 테이블의 외관을 유지할 수 있습니다.

진짜 해킹할 수 있는...

테이블에 스키마바운드 뷰를 만듭니다.

CREATE VIEW Why SELECT * 테이블에서 RecordStatus = 1

이제 원하는 필드를 사용하여 뷰에 고유한 구속조건을 작성합니다.

그러나 스키마바운드 뷰에 대한 주의사항 중 하나는 기본 테이블을 변경할 경우 뷰를 다시 작성해야 합니다.그것 때문에 고차도 많이 생겼지.

아직도 해결책을 찾고 있는 분들을 위해, 저는 비슷한 질문에 좋은 답을 찾았습니다. 그리고 저는 이것이 여전히 많은 사람들에게 유용할 것이라고 생각합니다.삭제된 레코드를 다른 테이블로 이동하는 것이 더 나을 수 있지만, 레코드 이동을 원하지 않는 사용자는 다음과 같은 링크된 답변에서 아이디어를 사용할 수 있습니다.

• 레코드가 사용 가능/활성화된 경우 deleted=0으로 설정합니다.
• 행을 deleted로 표시할 때는 deleted=<row_id or other unique value >를 설정합니다.

빌의 제안대로 NULL을 RecordStatus로 사용할 수 없다면 빌의 아이디어를 함수 기반 인덱스와 결합할 수 있습니다.RecordStatus가 제약조건에서 고려하는 값 중 하나가 아닌 경우(및 RecordStatus가 아닌 경우) NULL을 반환하는 함수를 만들고 그 위에 인덱스를 만듭니다.

이렇게 하면 제약조건에 따라 테이블의 다른 행을 명시적으로 조사할 필요가 없어 성능 문제가 발생할 수 있습니다.

SQL Server를 전혀 모른다고 해야 하는데 Oracle에서 이 방법을 성공적으로 사용했습니다.

중복을 허용하기 때문에 고유한 제약 조건은 작동하지 않습니다.RecordStatus 컬럼에 대한 체크 제약조건과 중복 ID를 삽입하기 전에 기존 활성 레코드를 체크하는 INSERT 스토어드 프로시저를 작성할 수 있습니다.

언급URL : https://stackoverflow.com/questions/866061/conditional-unique-constraint