Primary Key with two columns

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?

Re: Primary Key with two columns

On Tue, 18 Dec 2007 15:57:51 -0800 (PST), YZXIA
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.



>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?

Re: Primary Key with two columns

"YZXIA" wrote in message
news:acf22b78-2cf5-4aea-823d-9f718577de64@w40g2000hsb.google groups.com...
>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?


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