How to add composite primary key to table

Question

create table d id numeric 1   code varchar 2     After I create the above table how can I add a composite primary key on both fields and also a foreign key

User · Answer

If using Sql Server Management Studio Designer just select both rows  Shift Click  and Set Primary Key

User · Answer

You don t need to create the table first and then add the keys in subsequent steps  You can add both primary key and foreign key while creating the table   This example assumes the existence of a table  Codes  that we would want to reference with our foreign key   CREATE TABLE d   id  numeric  1   code  varchar  2   PRIMARY KEY  id  code   CONSTRAINT fk d codes FOREIGN KEY  code  REFERENCES Codes  code      If you don t have a table that we can reference  add one like this so that the example will work   CREATE TABLE Codes       Code  varchar  2  PRIMARY KEY            NOTE  you must have a table to reference before creating the foreign key

User · Answer

In Oracle  you could do this   create table D     ID numeric 1     CODE varchar 2     constraint PK D primary key  ID  CODE

User · Answer

The ALTER TABLE statement presented by Chris should work  but first you need to declare the columns NOT NULL  All parts of a primary key need to be NOT NULL

User · Answer

alter table d add constraint pkc Name primary key  id  code    should do it   There s lots of options to a basic primary key index depending on what DB your working with

[sql] How to add composite primary key to table

Examples related to sql

Examples related to sql-server-2005

Examples related to foreign-keys

Examples related to primary-key