Composite alternate key in tables

Sergey picture Sergey · Sep 11, 2011 · Viewed 10.5k times · Source

Assume that a table have 4 columns A, B, C and D. Only A column defines uniqueness that's why it's a primary key. B, C and D allow entries to repeat that's why we can't take them as an composite alternate key. Is it possible to use the same column in primary key and alternate key, say, to make a alternate key as (A and B)?

Answer

mishau picture mishau · Sep 11, 2011

Unless I didn't get your idea, for MS SQL it's quite possible, look at this test:

CREATE TABLE Alternate (
    A int IDENTITY(1,1) NOT NULL,
    B int NULL,
    C int NULL,
    D int NULL,
 CONSTRAINT PK_Alternate PRIMARY KEY (A),
 CONSTRAINT AK_Alternate Unique (A,B)
)
GO

insert into Alternate (B) values(1)
insert into Alternate (B) values(1)
insert into Alternate (B) values(1)
insert into Alternate (B) values(null)
insert into Alternate (B) values(null)
insert into Alternate (B) values(null)

select A, B from Alternate

The result is as following:

1 1

2 1

3 1

4 NULL

5 NULL

6 NULL