CODEWITHSUNDEEP
sql-server
RagnaRock
RagnaRock

Reputation: 2630

How to guarantee some rows are unique (a sort of secondary key) SQL

Is there anyway to make a secondary key in SQL?

Lets say I have a table with the columns ID, SECID1, SECID2

The key is ID, but I also want that SECID1, SECID2 work as a key, to guarantee that I don't have 2 entries with the same SECID1 and SECID1.

If possible, how can I do this?

Upvotes: 1

Views: 897

Answers (5)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

1) You could use an UNIQUE NONCLUSTERED constraint or an UNIQUE NONCLUSTERED index:

CREATE TABLE MyTestTable
(
    ID  INT NOT NULL,
    A   INT NOT NULL,
    B   INT NOT NULL,
    C   INT NULL,
    CONSTRAINT PK_MyTestTable PRIMARY KEY(ID)
);
GO
ALTER TABLE MyTestTable
ADD CONSTRAINT UQ_MyTestTable_A_B UNIQUE NONCLUSTERED (A, B);
GO

CREATE UNIQUE NONCLUSTERED INDEX IUN_MyTestTable_A_B
ON MyTestTable(A, B);
GO
SELECT  i.name, i.index_id, i.type, i.type_desc, i.is_unique, i.is_primary_key
FROM    sys.indexes i
WHERE   i.object_id = OBJECT_ID('dbo.MyTestTable')
GO

Results:

name                index_id type type_desc    is_unique is_primary_key
------------------- -------- ---- ------------ --------- --------------
PK_MyTestTable      1        1    CLUSTERED    1         1
UQ_MyTestTable_A_B  2        2    NONCLUSTERED 1         0
IUN_MyTestTable_A_B 3        2    NONCLUSTERED 1         0

Note 1: You can see that CONSTRAINT PK_MyTestTable is created as an CLUSTERED UNIQUE INDEX and CONSTRAINT UQ_MyTestTable_A_B UNIQUE NONCLUSTERED is created as 'NONCLUSTERED INDEX'.

Note 2 (SQL 2008+): For me, in SQL Server 2008, then main difference between an UNIQUE NONCLUSTERED CONSTRAINT and an UNIQUE NONCLUSTERED INDEX is the ability to include non-indexed columns within NC index thus: 

CREATE UNIQUE NONCLUSTERED INDEX IUN_MyTestTable_A_B_#_C
ON MyTestTable(A, B)
INCLUDE (C); --Nonindexed column
GO
SELECT  i.name, i.index_id, i.type, i.type_desc, i.is_unique, i.is_primary_key, i.is_disabled
FROM    sys.indexes i
WHERE   i.object_id = OBJECT_ID('dbo.MyTestTable')
GO

Results:

name                    index_id    type type_desc    is_unique is_primary_key is_disabled
----------------------- ----------- ---- ------------ --------- -------------- -----------
PK_MyTestTable          1           1    CLUSTERED    1         1              0
UQ_MyTestTable_A_B      2           2    NONCLUSTERED 1         0              0
IUN_MyTestTable_A_B     3           2    NONCLUSTERED 1         0              0
IUN_MyTestTable_A_B_#_C 4           2    NONCLUSTERED 1         0              0

You may use CREATE UNIQUE NONCLUSTERED INDEX ... ... INCLUDE(...) to create covering indexes and to eliminate KeyLookup or RID Lookup from execution plans.

Note 3 (SQL 2008+): Also, you may have UNIQUE NONCLUSTERED FILTERED indexes.

CREATE TABLE Invoice
(
    InvoiceID INT PRIMARY KEY, 
    InvoiceDate DATE NOT NULL, 
    Total NUMERIC(8,2) NOT NULL,
    --Business rule: one invoice may have only one child invoice
    ParentInvoiceID INT NULL REFERENCES Invoice(InvoiceID)
);
GO
INSERT Invoice(InvoiceID, InvoiceDate, Total, ParentInvoiceID)
VALUES  (1, '2011-01-01', 1000, NULL), (2, '2011-02-02', 2000, NULL);
GO
SET ANSI_WARNINGS ON;
GO
--Business rule: one invoice may have only one child invoice
CREATE UNIQUE NONCLUSTERED INDEX IUF_Invoice_ParentInvoiceID
ON Invoice(ParentInvoiceID)
WHERE ParentInvoiceID IS NOT NULL;
GO
--OK
INSERT Invoice(InvoiceID, InvoiceDate, Total, ParentInvoiceID)
VALUES  (3, '2012-03-03', 1005, 1);
GO
--Error
INSERT Invoice(InvoiceID, InvoiceDate, Total, ParentInvoiceID)
VALUES  (4, '2012-04-04', 1005, 1);
GO

Results:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.Invoice' with unique index 'IUF_Invoice_ParentInvoiceID'.
The statement has been terminated.

2) You could use an UNIQUE CLUSTERED constraint or an UNIQUE CLUSTERED index:

IF OBJECT_ID('dbo.MyTestTable') IS NOT NULL
    DROP TABLE MyTestTable;
GO
CREATE TABLE MyTestTable
(
    ID  INT NOT NULL,
    A   INT NOT NULL,
    B   INT NOT NULL,
    C   INT NULL,
    CONSTRAINT PK_MyTestTable PRIMARY KEY NONCLUSTERED(ID)
);
GO
ALTER TABLE MyTestTable
ADD CONSTRAINT UQ_MyTestTable_A_B UNIQUE CLUSTERED (A, B);
GO
SELECT  i.name, i.index_id, i.type, i.type_desc, i.is_unique, i.is_primary_key, i.is_disabled
FROM    sys.indexes i
WHERE   i.object_id = OBJECT_ID('dbo.MyTestTable')
GO

Results:

name               index_id    type type_desc     is_unique is_primary_key is_disabled
------------------ ----------- ---- ------------- --------- -------------- -----------
UQ_MyTestTable_A_B 1           1    CLUSTERED     1         0              0
PK_MyTestTable     2           2    NONCLUSTERED  1         1              0

Or

ALTER TABLE MyTesttable
DROP CONSTRAINT UQ_MyTestTable_A_B;
GO
CREATE UNIQUE CLUSTERED INDEX IUN_MyTestTable_A_B2
ON MyTestTable(A, B);
GO
SELECT  i.name, i.index_id, i.type, i.type_desc, i.is_unique, i.is_primary_key, i.is_disabled
FROM    sys.indexes i
WHERE   i.object_id = OBJECT_ID('dbo.MyTestTable')
GO

Results:

name                 index_id    type type_desc    is_unique is_primary_key is_disabled
-------------------- ----------- ---- ------------ --------- -------------- -----------
IUN_MyTestTable_A_B2 1           1    CLUSTERED    1         0              0
PK_MyTestTable       2           2    NONCLUSTERED 1         1              0

Upvotes: 0

Tarek Fadel
Tarek Fadel

Reputation: 1959

What you want is a Unique Key

Upvotes: 2

user1320635
user1320635

Reputation:

It is possible, here you have documentation and explanation: http://www.w3schools.com/sql/sql_unique.asp

Upvotes: 4

Lucero
Lucero

Reputation: 60190

You can create as many UNIQUE KEY constraints as you like to achieve such uniqueness checks/constraints.

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 452988

Yes, you can add a composite column unique constraint or unique index (below I use a unique constraint)

CREATE TABLE YourTable
(
ID INT PRIMARY KEY, 
SECID1 INT, 
SECID2 INT,
UNIQUE(SECID1,SECID2)
)

Is the surrogate ID column definitely needed? If this is a many to many relationship table I usually just have a 2 column PK (and sometimes a unique index/constraint in reversed key order depending on queries that access that table)

Upvotes: 3

Related Questions