How Can I Create Composite Primary Keys? - SQL Server/T-SQL

CREATE TABLE SupplierQuote
(
      supplierQuoteID   int identity (3504,2) CONSTRAINT supquoteid_pk PRIMARY KEY,
      supQuoteProductID int identity (980,20) CONSTRAINT supqtprodid_pk PRIMARY KEY,
      PONumber               int identity (9553,20) NOT NULL,
       .
       .
       .
);

I get the following error:

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

Msg 2744, Level 16, State 2, Line 324
Multiple identity columns specified for table 'SupplierQuote'. Only one identity column per table is allowed.

Please note that i need to keep the constraint names of the primary key.
LVL 1
F-J-KAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:

CREATE TABLE SupplierQuote
(
      supplierQuoteID   int identity (3504,2) ,
      supQuoteProductID int identity (980,20) ,
      PONumber               int identity (9553,20) NOT NULL,
      CONSTRAINT supquoteid_pk PRIMARY KEY (supplierQuoteID, supQuoteProductID)
);
0
LowfatspreadCommented:
more like

CREATE TABLE SupplierQuote
(
      supplierQuoteID   int  not null,
      supQuoteProductID int  not null,
      PONumber               int  NOT NULL,
.... ,
      CONSTRAINT supquoteid_pk
        clustered PRIMARY KEY  (supplierQuoteID, supQuoteProductID)
);

use clustered or non clustered to confirm what type of index you want creating...

you can only have 1 identity column per table!  SHOULD pon number be an identity are are the values determined elsewhere?
i assume that the quoteid and productid are identites on other tables...

the columns in the primary key must be not null
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chapmandewCommented:
good catch
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

F-J-KAuthor Commented:
>>supplierQuoteID, supQuoteProductID

Is there anyway i can set constraint name on each primary column? I got child table points at supplierQuoteID and the other child table points at supQuoteProductID. If i only can have one constraint
name for composite primary keys, can i let a foreign key points to unique column?
0
chapmandewCommented:
>>Is there anyway i can set constraint name on each primary column?
No
0
F-J-KAuthor Commented:
Well Answered
0
dportasCommented:
>> I got child table points at supplierQuoteID and the other child table points at supQuoteProductID.

In that case each of those columns will need its own separate key constraint. You can create multiple keys per table. So the syntax would be:

CREATE TABLE SupplierQuote
(
      supplierQuoteID   int NOT NULL CONSTRAINT uq_supplierQuoteID UNIQUE ,
      supQuoteProductID int NOT NULL CONSTRAINT uq_supQuoteProductID UNIQUE,
      PONumber               int NOT NULL
);

You can make one of those UNIQUE constraints into PRIMARY KEY if you like, but it doesn't make any real difference. You should however specify which of those constraints, if any, you want to use a clustered and index and which nonclustered. Add the NONCLUSTERED / CLUSTERED keyword to the constraint as required.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.