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?
 
LowfatspreadConnect With a Mentor Commented:
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
 
chapmandewConnect With a Mentor Commented:

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
 
chapmandewCommented:
good catch
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
chapmandewConnect With a Mentor Commented:
>>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
All Courses

From novice to tech pro — start learning today.