Hi, everybody, does anyone know whether composite key is allowed in MS SQL Server 2000 not?

Normally the database table requires one and only one key, i.e. primary key. For my case, by using only one primary key does not make my tuple unique.

Pls look at the small example below:

Table : ApartmentBlock
Field01 : BlockName
Field02 : ApartmentName

BlockName ApartmentName
M1 Court 7
M2 Court 7
M1 Court 8
M2 Court 8

So, it is very clear that by using only BlockName as primary key is not sufficient to make the tuple unique. Thus, I have to enforce both BlockName and ApartmentName so to make the tuple unique, but I don't know how it is done in SQL Server 2000.

Can anyone pls help me on this, thank you.

you must create a unique index constraint ;since you already have your table created use the following:

alter apartmentblock
add constraint
ck_aptblock
check(blockname,apartmentname)

From Books online
UNIQUE Constraints
You can use UNIQUE constraints to ensure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.

Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table.

Also, unlike PRIMARY KEY constraints, UNIQUE constraints allow the value NULL. However, as with any value participating in a UNIQUE constraint, only one NULL value is allowed per column.

A UNIQUE constraint can also be referenced by a FOREIGN KEY constraint


Jaime E. Maccou
Applications Analyst

Thank you very much, jemacc.

HI,

Yes SQL Server supports composite keys. U can create a single Primary Key composite upto 16 colums max.

Offcourse u should create a Primary key combining both the colums u want.

As the easiesr way In Enterprise Manage, Rclik on the Table u want and cick Design. Hold the control Key pressed and click one by one the colums u want to invlove in composite key. Now Click on the Set Primary key button on tool bar ( u can see an icon of Key).

U can also use alter table T-SQL Statement.

Hope this helps.

B. Anant