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

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

Formally:

• A "superkey" is a set of attributes that, taken together, are capable of uniquely identifying rows.
• A "key" is a minimal superkey - i.e. if if you take away any attribute from it, it will no longer be unique.

So in your example: {A, B} is not a key since it is not minimal (you can take away B and still have uniqueness).

While a typical DBMS will let you crate a UNIQUE constraint that "contains" PRIMARY KEY, this is not a good idea - you'd be violating a database design principle that "everything should depend on a key, whole key and nothing but the key".

BTW, why would you want to do such a thing anyway? Are you just trying to avoid having 2 indexes (on {A} and on {A, B}) for performance reasons? If so, keep in mind that "index" and "key" are two separate concepts - the former is physical/performance related while the latter is logical. The fact that a key is often supported by an index (for performance reasons) should not blur this distinction.

Depending on your DBMS, you'll probably be able to crate an INDEX on {A, B} and PRIMARY KEY on A without having to crate INDEX on {A}.