SQL Multiple Foreign Keys as Primary Keys

Question

If I declare the table below does it implicitly imply that both the foreign keys make a unique primary key or do I need to do something more to make both attributes as a primary key?

CREATE TABLE Report_has_Items
(
    ReportID int REFERENCES Report(ReportID) NOT NULL,
    ItemID int REFERENCES Item(ItemID) NOT NULL
)

Essentially both attributes which are foreign keys from other tables, together would form a unique key.

score 18 · Accepted Answer · answered Apr 12 '13 at 00:46

No it doesn't. The above table has no primary key. If you want to use the fields as a primary key use:

CREATE TABLE Report_has_Items(
    ReportID int REFERENCES Report(ReportID) NOT NULL,
    ItemID int REFERENCES Item(ItemID) NOT NULL,
    PRIMARY KEY (ReportID, ItemID)
)

or something similar depending on your sql dilect.

score 9 · Answer 2 · answered Apr 12 '13 at 03:03

9

Let's name our constraints, eh?

CREATE TABLE dbo.Report_has_Items(
    ReportID int NOT NULL,
       CONSTRAINT [FK_RHI_Report] (ReportId) REFERENCES dbo.Report(ReportID),
    ItemID int NOT NULL,
       Constraint [FK_RHI_Item] (ItemId) REFERENCES dbo.Item(ItemID),
    CONSTRAINT [PK_RHI] PRIMARY KEY (ReportID, ItemID)
)

answered Apr 12 '13 at 03:03

Ben Thul

31,080
4
45
68

What is the benefit of naming a constraint? – Kairan Apr 13 '13 at 05:00
1

@Kairan - One main benefit is, if a query (insert, update, delete) violates a constraint, an error message will be generated with the constraint name. If the constraint name is clear and descriptive, the error message will be easier to understand; if the constraint name is a random it would be less clear. – Gayu Apr 25 '13 at 03:40
1

The main reason for me is consistency across environments. I'm a support DBA. Part of that job is migrating changes from DEV to QA to PROD. I use a schema comparison tool for this. It's nice if the constraints are named the same so that differences in naming aren't flagged as differences. – Ben Thul Apr 25 '13 at 12:52
@Gayu Thanks for the information. Going forward I think I will do this. – Kairan Apr 25 '13 at 18:40

Gayu · Answer 3 · 2013-04-12T01:29:08.447

I am not sure i understand your question completely but i assume you are trying to create a composite primary key (primary key with more than one attribute). You could do the following.

CREATE TABLE Report_has_Items(
  ReportID int references Report(ReportID),
  ItemID int references Item(ItemID),
  PRIMARY KEY (ReportID , ItemID )
);

Note:The pair (ReportID , ItemID ) must then be unique for the table and neither value can be NULL.

Here is a very useful link for SQL Queries

SQL Multiple Foreign Keys as Primary Keys

3 Answers3