I need to make a primary key, which constitute two different columns
on our MS SQL server. When I wrote the following code I received an
error message:


create table t_dz_borrowing_record
(
CutOffDate datetime not null Primary key,
LoanType varchar(20) not null primary key,
..... More columns
)


Msg 8110, Level 16, State 0, Line 2
Cannot add multiple PRIMARY KEY constraints to table
't_dz_borrowing_record'.

Does anyone know how to make a primary key which is a combination of
two columns?

On Tue, 18 Dec 2007 15:57:51 -0800 (PST), YZXIA <yzx27 (AT) hotmail (DOT) com>
wrote:

From Books online, on the topic of Create Table:
CREATE TABLE [dbo].[PurchaseOrderDetail]
(
[PurchaseOrderID] [int] NOT NULL
REFERENCES Purchasing.PurchaseOrderHeader(PurchaseOrderID),
[LineNumber] [smallint] NOT NULL,
[ProductID] [int] NULL
REFERENCES Production.Product(ProductID),
[UnitPrice] [money] NULL,
[OrderQty] [smallint] NULL,
[ReceivedQty] [float] NULL,
[RejectedQty] [float] NULL,
[DueDate] [datetime] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL
CONSTRAINT [DF_PurchaseOrderDetail_rowguid] DEFAULT (newid()),
[ModifiedDate] [datetime] NOT NULL
CONSTRAINT [DF_PurchaseOrderDetail_ModifiedDate] DEFAULT
(getdate()),
[LineTotal] AS (([UnitPrice]*[OrderQty])),
[StockedQty] AS (([ReceivedQty]-[RejectedQty])),
CONSTRAINT [PK_PurchaseOrderDetail_PurchaseOrderID_LineNumber]
PRIMARY KEY CLUSTERED ([PurchaseOrderID], [LineNumber])
WITH (IGNORE_DUP_KEY = OFF)
)
ON [PRIMARY]

-Tom.

"YZXIA" <yzx27 (AT) hotmail (DOT) com> wrote


Books Online is your friend. Get to know it.

You should name your constraints so I've added the CONSTRAINT clause and a
name as well:

CREATE TABLE t_dz_borrowing_record
(
CutOffDate datetime not null,
LoanType varchar(20) not null,
CONSTRAINT t_dz_borrowing_record_pk PRIMARY KEY (CutOffDate, LoanType),
..... More columns
);

--
David Portas