Composite primary key

[GerryGoldberg]

Could someone show me the correct syntax for creating a CLUSTERED PRIMARY KEY made up of multiple fields. My table is structured like:

CREATE TABLE MASTER (
client_ID int NOT NULL,
visit_ID int NOT NULL,
visit_ID datetime NULL,
clinic_ID varchar(10) NULL,
staff_ID varchar(10) NULL)

I would like the primary key to be a composite of client_ID and visit_ID.

Thanks,


Gerry Goldberg

 

[rajeevnandanmishra]

Hi Gerry,
Try doing like this
----------------------
ALTER TABLE MASTER
ADD
CONSTRAINT myConstraintName PRIMARY KEY CLUSTERED
(client_ID,visit_ID)
----------------------

 

[foxdev]

Gerry, in your table definition you have visit_ID identified twice, with different attributes:
[tt]visit_ID int NOT NULL,
visit_ID datetime NULL,[/tt]

Also, I'm not sure that a composite index on client_id and visit_id would be a good thing, though it is certainly technically possible. Normally, though, you wouldn't want to combine two integer foreign keys together into a single index. Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[GerryGoldberg]

Oops, the second "visit_ID" in my example should be "visit_Date".

Gerry