Concatenated Primary Keys

I cant remember how to write concatenated primary keys

I have two tables an Equipment table which has many pieces of equipment available to rent its PK is SerialNumber

Then I have a Rental Table It's PK is RentalID, since on any rental you can rent more than one piece of equipment I have a table called RentedEquipment

It's PK's are RentalID and SerialNumber, but I dont remember how write the code

Help

Try this and see how it works out...


CREATE TABLE RentedEquipment(
RentalID Int NOT NULL
SerialNumber Int NOT NULL
<insert more fields here>
) ON [PRIMARY]

ALTER TABLE [dbo].[RentedEquipment] WITH NOCHECK ADD
CONSTRAINT [PK_RentedEquipment] PRIMARY KEY
(
[RentalID],
[SerialNumber]
) ON [PRIMARY]


ALTER TABLE [dbo].[RentedEquipment] ADD
CONSTRAINT [FK_RentedEquipment_RentalID ] FOREIGN KEY
(
[RentalID]
) REFERENCES [dbo].[Rental] (
[RentalID]
),
CONSTRAINT [FK_RentedEquipment_SerialNumber] FOREIGN KEY
(
[SerialNumber]
) REFERENCES [dbo].[Equipment] (
[SerialNumber]
)

However, if you're using SQL Server, why not just go in through Enterprise Manager and select both fields and create the composite primary key?

Thanks that worked, although I'm not entierly sure how or why.

Here is what I was trying to do
CREATE TABLE RentedEquipment
(
RentalID int NOT NULL CONSTRAINT PK_RentedEquipment_RentalID PRIMARY KEY CONSTRAINT FK_RentedEquipment_RentalID FOREIGN KEY REFERENCES Rentals(RentalID),


SerialNumber int NOT NULL CONSTRAINT PK_RentedEquipment_SerialNumber PRIMARY KEY CONSTRAINT FK_RentedEquipment_SerialNumber FOREIGN KEY REFERENCES Equipment(SerialNumber),
)
GO

I don't think you can add your constraints directly in the CREATE TABLE statement, and that's probably why it wasn't working.

Of course you can.
Check the correct syntax in Books On Line.

Ah well, guess that's what I get for thinking. I prefer to add my primary keys through Enterprise Manager.

I finally found some old notes to do it in the create table statement

Here is the code if you wanted to know

CREATE TABLE RentedEquipment
(
RentalID int NOT NULL CONSTRAINT FK_RentedEquipment_RentalID FOREIGN KEY REFERENCES Rentals(RentalID),
SerialNumber int NOT NULL CONSTRAINT FL_RentedEquipment_SerialNumber FOREIGN KEY REFERENCES Equipment(SerialNumber),
CONSTRAINT PK_RentedEquipment PRIMARY KEY CLUSTERED (RentalID, SerialNumber)
)
GO

Thanks again for the help