Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Multiple Foreign Keys as Primary Keys

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.

like image 948
Kairan Avatar asked Apr 12 '13 00:04

Kairan


People also ask

Can a primary key have multiple foreign keys?

It is perfectly fine to have two foreign key columns referencing the same primary key column in a different table since each foreign key value will reference a different record in the related table.

Can you have multiple foreign keys SQL?

A table can have multiple foreign keys based on the requirement.

How many foreign keys can a primary key have?

A table with a foreign key reference to itself is still limited to 253 foreign key references. Greater than 253 foreign key references are not currently available for columnstore indexes, memory-optimized tables, Stretch Database, or partitioned foreign key tables. Stretch Database is deprecated in SQL Server 2022 (16.

Can multiple foreign keys reference the same table?

FOREIGN KEY constraints can reference another column in the same table, and is referred to as a self-reference. A FOREIGN KEY constraint specified at the column level can list only one reference column. This column must have the same data type as the column on which the constraint is defined.


2 Answers

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.

like image 137
gtsouk Avatar answered Sep 18 '22 08:09

gtsouk


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)
)
like image 23
Ben Thul Avatar answered Sep 22 '22 08:09

Ben Thul