[sql] How to add composite primary key to table

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?

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

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.

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.

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.