Search code examples
sqlalternate

Composite alternate key in tables

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)?

Solution

  • 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