Geek asks and answers

Multiple SQL foreign keys as primary keys

If I declare the table below, does this mean that both foreign keys form a unique primary key, or do I need to do something more so that both attributes are primary?

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 form a unique key.

+5
source share
3 answers

No no. There is no primary key in the above table. If you want to use 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.

+9
source

What are our limitations, 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)
)
+5
source

, , , ( ). .

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 be unique to the table, and no value can be NULL.

Here is a very useful link for SQL queries

+3
source

More articles:


All Articles