null도 허용하는 고유 제약 조건을 어떻게 생성합니까?

null도 허용하는 고유 제약 조건을 어떻게 생성합니까?

---

GUID로 채울 열에 대한 고유 한 제약 조건을 갖고 싶습니다. 그러나 내 데이터에는이 열에 대한 null 값이 포함되어 있습니다. 여러 null 값을 허용하는 제약 조건을 어떻게 생성합니까?

다음은 예제 시나리오 입니다. 다음 스키마를 고려하십시오.

CREATE TABLE People (
  Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY,
  Name NVARCHAR(250) NOT NULL,
  LibraryCardId UNIQUEIDENTIFIER NULL,
  CONSTRAINT UQ_People_LibraryCardId UNIQUE (LibraryCardId)
)

그런 다음 내가 달성하려는 작업에 대해 다음 코드를 참조하십시오.

-- This works fine:
INSERT INTO People (Name, LibraryCardId) 
 VALUES ('John Doe', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');

-- This also works fine, obviously:
INSERT INTO People (Name, LibraryCardId) 
VALUES ('Marie Doe', 'BBBBBBBB-BBBB-BBBB-BBBB-BBBBBBBBBBBB');

-- This would *correctly* fail:
--INSERT INTO People (Name, LibraryCardId) 
--VALUES ('John Doe the Second', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');

-- This works fine this one first time:
INSERT INTO People (Name, LibraryCardId) 
VALUES ('Richard Roe', NULL);

-- THE PROBLEM: This fails even though I'd like to be able to do this:
INSERT INTO People (Name, LibraryCardId) 
VALUES ('Marcus Roe', NULL);

마지막 문은 다음 메시지와 함께 실패합니다.

UNIQUE KEY 제약 조건 'UQ_People_LibraryCardId'위반. 'dbo.People'개체에 중복 키를 삽입 할 수 없습니다.

NULL실제 데이터에 대한 고유성을 확인하면서 여러 값을 허용하도록 스키마 및 / 또는 고유성 제약 조건을 변경하려면 어떻게해야 합니까?

---

SQL Server 2008 이상

WHERE절을 사용하여 여러 NULL을 허용하는 고유 인덱스를 만들 수 있습니다 . 아래 답변을 참조하십시오 .

SQL Server 2008 이전

UNIQUE 제약 조건을 생성하고 NULL을 허용 할 수 없습니다. 기본값 NEWID ()를 설정해야합니다.

UNIQUE 제약 조건을 만들기 전에 NULL 인 NEWID ()로 기존 값을 업데이트합니다.

---

여러분이 찾고있는 것은 실제로 ANSI 표준 SQL : 92, SQL : 1999 및 SQL : 2003의 일부입니다. 즉, UNIQUE 제약 조건은 NULL이 아닌 중복 값을 허용하지 않고 여러 NULL 값을 허용해야합니다.

그러나 Microsoft SQL Server 세계에서는 단일 NULL이 허용되지만 여러 NULL은 허용되지 않습니다.

에서 SQL 서버 2008 , 당신은 제외 NULL을 그 조건 자에 따라 고유 필터링 된 인덱스를 정의 할 수 있습니다 :

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

이전 버전에서는 제약 조건을 적용하기 위해 NOT NULL 조건 자와 함께 VIEWS를 사용할 수 있습니다.

---

SQL Server 2008 이상

고유 색인을 필터링하십시오.

CREATE UNIQUE NONCLUSTERED INDEX UQ_Party_SamAccountName
ON dbo.Party(SamAccountName)
WHERE SamAccountName IS NOT NULL;

하위 버전에서는 구체화 된 뷰가 여전히 필요하지 않습니다.

SQL Server 2005 및 이전 버전의 경우보기없이 수행 할 수 있습니다. 방금 내 테이블 중 하나에 요청하는 것과 같은 고유 한 제약 조건을 추가했습니다. column SamAccountName에서 고유성을 원 하지만 여러 NULL을 허용하려는 경우 구체화 된 뷰 대신 구체화 된 열을 사용했습니다.

ALTER TABLE dbo.Party ADD SamAccountNameUnique
   AS (Coalesce(SamAccountName, Convert(varchar(11), PartyID)))
ALTER TABLE dbo.Party ADD CONSTRAINT UQ_Party_SamAccountName
   UNIQUE (SamAccountNameUnique)

You simply have to put something in the computed column that will be guaranteed unique across the whole table when the actual desired unique column is NULL. In this case, PartyID is an identity column and being numeric will never match any SamAccountName, so it worked for me. You can try your own method—be sure you understand the domain of your data so that there is no possibility of intersection with real data. That could be as simple as prepending a differentiator character like this:

Coalesce('n' + SamAccountName, 'p' + Convert(varchar(11), PartyID))

Even if PartyID became non-numeric someday and could coincide with a SamAccountName, now it won't matter.

Note that the presence of an index including the computed column implicitly causes each expression result to be saved to disk with the other data in the table, which DOES take additional disk space.

Note that if you don't want an index, you can still save CPU by making the expression be precalculated to disk by adding the keyword PERSISTED to the end of the column expression definition.

In SQL Server 2008 and up, definitely use the filtered solution instead if you possibly can!

Controversy

Please note that some database professionals will see this as a case of "surrogate NULLs", which definitely have problems (mostly due to issues around trying to determine when something is a real value or a surrogate value for missing data; there can also be issues with the number of non-NULL surrogate values multiplying like crazy).

However, I believe this case is different. The computed column I'm adding will never be used to determine anything. It has no meaning of itself, and encodes no information that isn't already found separately in other, properly defined columns. It should never be selected or used.

So, my story is that this is not a surrogate NULL, and I'm sticking to it! Since we don't actually want the non-NULL value for any purpose other than to trick the UNIQUE index to ignore NULLs, our use case has none of the problems that arise with normal surrogate NULL creation.

All that said, I have no problem with using an indexed view instead—but it brings some issues with it such as the requirement of using SCHEMABINDING. Have fun adding a new column to your base table (you'll at minimum have to drop the index, and then drop the view or alter the view to not be schema bound). See the full (long) list of requirements for creating an indexed view in SQL Server (2005) (also later versions), (2000).

Update

If your column is numeric, there may be the challenge of ensuring that the unique constraint using Coalesce does not result in collisions. In that case, there are some options. One might be to use a negative number, to put the "surrogate NULLs" only in the negative range, and the "real values" only in the positive range. Alternately, the following pattern could be used. In table Issue (where IssueID is the PRIMARY KEY), there may or may not be a TicketID, but if there is one, it must be unique.

ALTER TABLE dbo.Issue ADD TicketUnique
   AS (CASE WHEN TicketID IS NULL THEN IssueID END);
ALTER TABLE dbo.Issue ADD CONSTRAINT UQ_Issue_Ticket_AllowNull
   UNIQUE (TicketID, TicketUnique);

If IssueID 1 has ticket 123, the UNIQUE constraint will be on values (123, NULL). If IssueID 2 has no ticket, it will be on (NULL, 2). Some thought will show that this constraint cannot be duplicated for any row in the table, and still allows multiple NULLs.

---

For people who are using Microsoft SQL Server Manager and want to create a Unique but Nullable index you can create your unique index as you normally would then in your Index Properties for your new index, select "Filter" from the left hand panel, then enter your filter (which is your where clause). It should read something like this:

([YourColumnName] IS NOT NULL)

This works with MSSQL 2012

---

When I applied the unique index below:

CREATE UNIQUE NONCLUSTERED INDEX idx_badgeid_notnull
ON employee(badgeid)
WHERE badgeid IS NOT NULL;

every non null update and insert failed with the error below:

UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'.

I found this on MSDN

SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

So to get this to work correctly I did this

Right click [Database]-->Properties-->Options-->Other Options-->Misscellaneous-->Arithmetic Abort Enabled -->true

I believe it is possible to set this option in code using

ALTER DATABASE "DBNAME" SET ARITHABORT ON

but i have not tested this

---

Create a view that selects only non-NULL columns and create the UNIQUE INDEX on the view:

CREATE VIEW myview
AS
SELECT  *
FROM    mytable
WHERE   mycolumn IS NOT NULL

CREATE UNIQUE INDEX ux_myview_mycolumn ON myview (mycolumn)

Note that you'll need to perform INSERT's and UPDATE's on the view instead of table.

You may do it with an INSTEAD OF trigger:

CREATE TRIGGER trg_mytable_insert ON mytable
INSTEAD OF INSERT
AS
BEGIN
        INSERT
        INTO    myview
        SELECT  *
        FROM    inserted
END

---

It can be done in the designer as well

Right click on the Index > Properties to get this window

---

It is possible to create a unique constraint on a Clustered Indexed View

You can create the View like this:

CREATE VIEW dbo.VIEW_OfYourTable WITH SCHEMABINDING AS
SELECT YourUniqueColumnWithNullValues FROM dbo.YourTable
WHERE YourUniqueColumnWithNullValues IS NOT NULL;

---

and the unique constraint like this:

CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_OFYOURTABLE 
  ON dbo.VIEW_OfYourTable(YourUniqueColumnWithNullValues)

---

Maybe consider an "INSTEAD OF" trigger and do the check yourself? With a non-clustered (non-unique) index on the column to enable the lookup.

---

As stated before, SQL Server doesn't implement the ANSI standard when it comes to UNIQUE CONSTRAINT. There is a ticket on Microsoft Connect for this since 2007. As suggested there and here the best options as of today are to use a filtered index as stated in another answer or a computed column, e.g.:

CREATE TABLE [Orders] (
  [OrderId] INT IDENTITY(1,1) NOT NULL,
  [TrackingId] varchar(11) NULL,
  ...
  [ComputedUniqueTrackingId] AS (
      CASE WHEN [TrackingId] IS NULL
      THEN '#' + cast([OrderId] as varchar(12))
      ELSE [TrackingId_Unique] END
  ),
  CONSTRAINT [UQ_TrackingId] UNIQUE ([ComputedUniqueTrackingId])
)

---

You can create an INSTEAD OF trigger to check for specific conditions and error if they are met. Creating an index can be costly on larger tables.

Here's an example:

CREATE TRIGGER PONY.trg_pony_unique_name ON PONY.tbl_pony
 INSTEAD OF INSERT, UPDATE
 AS
BEGIN
 IF EXISTS(
    SELECT TOP (1) 1 
    FROM inserted i
    GROUP BY i.pony_name
    HAVING COUNT(1) > 1     
    ) 
     OR EXISTS(
    SELECT TOP (1) 1 
    FROM PONY.tbl_pony t
    INNER JOIN inserted i
    ON i.pony_name = t.pony_name
    )
    THROW 911911, 'A pony must have a name as unique as s/he is. --PAS', 16;
 ELSE
    INSERT INTO PONY.tbl_pony (pony_name, stable_id, pet_human_id)
    SELECT pony_name, stable_id, pet_human_id
    FROM inserted
 END

---

You can't do this with a UNIQUE constraint, but you can do this in a trigger.

    CREATE TRIGGER [dbo].[OnInsertMyTableTrigger]
   ON  [dbo].[MyTable]
   INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @Column1 INT;
    DECLARE @Column2 INT; -- allow nulls on this column

    SELECT @Column1=Column1, @Column2=Column2 FROM inserted;

    -- Check if an existing record already exists, if not allow the insert.
    IF NOT EXISTS(SELECT * FROM dbo.MyTable WHERE Column1=@Column1 AND Column2=@Column2 @Column2 IS NOT NULL)
    BEGIN
        INSERT INTO dbo.MyTable (Column1, Column2)
            SELECT @Column2, @Column2;
    END
    ELSE
    BEGIN
        RAISERROR('The unique constraint applies on Column1 %d, AND Column2 %d, unless Column2 is NULL.', 16, 1, @Column1, @Column2);
        ROLLBACK TRANSACTION;   
    END

END

---

CREATE UNIQUE NONCLUSTERED INDEX [UIX_COLUMN_NAME]
ON [dbo].[Employee]([Username] ASC) WHERE ([Username] IS NOT NULL) 
WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, 
MAXDOP = 0) ON [PRIMARY];

---

this code if u make a register form with textBox and use insert and ur textBox is empty and u click on submit button .

CREATE UNIQUE NONCLUSTERED INDEX [IX_tableName_Column]
ON [dbo].[tableName]([columnName] ASC) WHERE [columnName] !=`''`;

참고URL : https://stackoverflow.com/questions/767657/how-do-i-create-a-unique-constraint-that-also-allows-nulls