조건부 고유 제약

조건부 고유 제약

---

열 집합에 대해 고유 한 제약 조건을 적용해야하지만 열의 한 값에만 적용해야하는 상황이 있습니다.

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

RecordStatus는 값 1 또는 2 (활성 또는 삭제됨) 만 가질 수 있으며 동일한 레코드가 여러 개 삭제 되어도 상관 없으므로 RecordStatus = 1 인 경우에만 (ID, RecordStatus)에 대한 고유 제약 조건을 만들고 싶습니다. 신분증.

트리거 작성 외에도 그렇게 할 수 있습니까?

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

---

이와 같은 검사 제약을 추가하십시오. 차이점은 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;

---

보라, 필터링 된 인덱스 . 문서에서 (강조 내) :

A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

And here's an example combining a unique index with a filter predicate:

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

This essentially enforces uniqueness of ID when RecordStatus is 1.

_{Note: the filtered index was introduced in SQL Server 2008. For earlier versions of SQL Server, please see this answer.}

---

You could move the deleted records to a table that lacks the constraint, and perhaps use a view with UNION of the two tables to preserve the appearance of a single table.

---

You can do this in a really hacky way...

Create an schemabound view on your table.

CREATE VIEW Whatever SELECT * FROM Table WHERE RecordStatus = 1

Now create a unique constraint on the view with the fields you want.

One note about schemabound views though, if you change the underlying tables you will have to recreate the view. Plenty of gotchas because of that.

---

Because, you are going to allow duplicates, a unique constraint will not work. You can create a check constraint for RecordStatus column and a stored procedure for INSERT that checks the existing active records before inserting duplicate IDs.

---

If you can't use NULL as a RecordStatus as Bill's suggested, you could combine his idea with a function-based index. Create a function that returns NULL if the RecordStatus is not one of the values you want to consider in your constraint (and the RecordStatus otherwise) and create an index over that.

That'll have the advantage that you don't have to explicitly examine other rows in the table in your constraint, which could cause you performance issues.

I should say I don't know SQL server at all, but I have successfully used this approach in Oracle.

참고URL : https://stackoverflow.com/questions/866061/conditional-unique-constraint