Foreign Key Referencing a 2 Columns Primary Key in SQL Server

Foreign key referencing a 2 columns primary key in SQL Server

Of course it's possible to create a foreign key relationship to a compound (more than one column) primary key. You didn't show us the statement you're using to try and create that relationship - it should be something like:

ALTER TABLE dbo.Content
   ADD CONSTRAINT FK_Content_Libraries
   FOREIGN KEY(LibraryID, Application)
   REFERENCES dbo.Libraries(ID, Application)

Is that what you're using?? If (ID, Application) is indeed the primary key on dbo.Libraries, this statement should definitely work.

Luk: just to check - can you run this statement in your database and report back what the output is??

SELECT
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME, 
    ccu.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
      ON ccu.TABLE_NAME = tc.TABLE_NAME AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE
    tc.TABLE_NAME IN ('Libraries', 'Content')

Referencing a two column primary key with multiple foreign keys

"Why doesn't this statement work? Or more specifically, why shouldn't
it work? "

You have defined the primary key on A as a compound of two columns (A,B). Any foreign key which references PK_AB must match those columns in number. This is because a foreign key must identify a single row in the referenced table which owns any given row in the child table. The compound primary key means column A.A can contain duplicate values and so can column A.B; only the permutations of (A,B) are unique. Consequently the referencing foreign key needs two columns.

Create Table B
( D int, E int, F int,
  Constraint fk_de Foreign Key (D,E) References A(A,B)
);

"Since there are multiple PK's that table B references"

Wrong. B references a single primary key, which happens to comprise more than one column,

" say, in the future, I want to delete B.D, but keep the relation
fk_e. "

That doesn't make sense. Think of it this way: D is not a property of B, it is an attribute B inherits through its dependence on table A.

One way to avoid this situation is to use a surrogate (or synthetic) key. Compound keys are often business keys, hence their columns are meaningful in a business context. One feature of meaningful column values is that they can change, and cascading such changes to foreign keys can be messy.

Implementing a surrogate key would look like this:

Create Table A
( id int not null, A int, B int, C int,
  Constraint pk_a Primary Key(ID),
  constraint uk_ab Unique (A,B)
);

Create Table B
( a_id int, F int,
  Constraint fk_n_a Foreign Key (A_ID) References A(ID)
);

Of course, you could kind of do this using the schema you posted, as you already have a single column constraint on A(C). However, I think it is bad practice to reference unique constraints rather than primary keys, even though it's allowed. I think this partly because unique constraints often enforce a business key, hence meaning, hence the potential for change, but mainly because referencing primary keys just is the industry standard.

SQL Server foreign relationship on two-column primary key

If your primary key is made up from more than one columns, then all foregin keys also must have all those columns - there's no way around this.

But I don't understand why you'd get this error trying to link TeamGroups to Team based on the Team.Id column.... that should work just fine.

Try using this:

ALTER TABLE TeamGroups WITH CHECK 
   ADD CONSTRAINT [FK_TeamGroups_Teams] 
   FOREIGN KEY (TeamId) REFERENCES Teams(Id);

You had Teams (which is not a valid column in TeamGroups at all), and you had REFERENCES Teams.id which is wrong - it needs to be REFERNCES Teams(Id); (column in parenthesis - not the "dot" notation)

Update: from TeamGroups to RoomBookings - yes.... either use both columns from TeamGroups in your RoomBookings table - or what would stop you from making the TeamGroups.Id column an INT IDENTITY and then have the PK on just this one column?? Any good reason for that??

CREATE TABLE TeamGroups 
(
    TeamGroupId int NOT NULL,
    TeamId int NOT NULL,

    PRIMARY KEY (TeamGroupId)
)

ALTER TABLE dbo.RoomBookings
   ADD CONSTRAINT FK_RoomBookings_TeamGroup
   FOREIGN KEY TeamGroupId REFERENCES TeamGroups(TeamGroupId)

How to use two columns in a foreign key constraint

In Article Table I have two fields that are the primary key: id,sl. In the Subscription Table I have a foreign key 'idsl`

This design is broken - it is apparent that the composite primary key in Article(id, sl) has been mangled into a single compound foreign key in table Subscription. This isn't a good idea.

Instead, you will need to change the design of table Subscription to include separate columns for both id and sl, of the same type as the Article Table, and then create a composite foreign key consisting of both columns, referencing Article in the same order as the primary key, e.g:

CREATE TABLE Article
(
    id INT NOT NULL,
    sl VARCHAR(50) NOT NULL,
    -- Other Columns

    CONSTRAINT PK_Article PRIMARY KEY(id, sl) -- composite primary key
);

CREATE TABLE Subscription
(
    -- Other columns
    id INT NOT NULL, -- Same type as Article.id
    sl VARCHAR(50) NOT NULL, -- Same type as Article.sl

    CONSTRAINT FK_Subscription_Article FOREIGN KEY(id, sl) 
         REFERENCES Article(id, sl) -- Same order as Article PK
);

Edit

One thing to consider here is that by convention a column named table.id or table.tableid should be unique, and is the primary key for the table. However, since table Article requires an additional column sl in the primary key, it implies that id isn't unique.

Foreign key reference to a two-column primary key

Rule #1: ALWAYS SAY THE DATABASE YOU'RE USING

Ok, I'm going to suggest you look at the ON DELETE clause, and the MATCH clause. Because, Pg is fairly SQL compliant I'll point you to the current docs on CREATE TABLE.

Excerpt:

These clauses specify a foreign key
constraint, which requires that a
group of one or more columns of the
new table must only contain values
that match values in the referenced
column(s) of some row of the
referenced table. If refcolumn is
omitted, the primary key of the
reftable is used. The referenced
columns must be the columns of a
unique or primary key constraint in
the referenced table. Note that
foreign key constraints cannot be
defined between temporary tables and
permanent tables.

A value inserted into the referencing
column(s) is matched against the
values of the referenced table and
referenced columns using the given
match type. There are three match
types: MATCH FULL, MATCH PARTIAL, and
MATCH SIMPLE, which is also the
default. MATCH FULL will not allow one
column of a multicolumn foreign key to
be null unless all foreign key columns
are null. MATCH SIMPLE allows some
foreign key columns to be null while
other parts of the foreign key are not
null. MATCH PARTIAL is not yet
implemented.

In addition, when the data in the
referenced columns is changed, certain
actions are performed on the data in
this table's columns. The ON DELETE
clause specifies the action to perform
when a referenced row in the
referenced table is being deleted.
Likewise, the ON UPDATE clause
specifies the action to perform when a
referenced column in the referenced
table is being updated to a new value.
If the row is updated, but the
referenced column is not actually
changed, no action is done.
Referential actions other than the NO
ACTION check cannot be deferred, even
if the constraint is declared
deferrable. There are the following
possible actions for each clause:

Also, there is a major exception here with MS SQL -- which doesn't permit partial matches (MATCH SIMPLE and MATCH PARTIAL) behaviors in foreign keys (defaults and enforces MATCH FULL). There are workarounds where you create a MATCH FULL index on the part of the table that IS NOT NULL for any of the composite key's constituents.

SQL Server : creating a table with a multi-column foreign key

Your problem isn't in the Primary Key on SIGNAL_RECEIVINGS, that will work just fine. Your problem is that you have id_chip with a foreign key to SIGNALS, and Signal_no with a foreign key to SIGNALS, both referencing a single column. Given that the PK on SIGNALS is a composite key, your FK on SIGNAL_RECEIVINGS needs to also be a composite to the table.

You need to create a single FK, pointing at both columns:

CREATE TABLE SIGNAL_RECEIVINGS (
  ID_receiver INT REFERENCES RECEIVERS ,
  Id_chip INT, 
  Signal_no INT, 
  Foreign Key (id_chip, signal_no) references Signals (id_chip, signal_no),
  PRIMARY KEY (Id_chip, Signal_no, ID_receiver ) 
  );

That said, I think your use of Composite Primary Keys is going to get out of control they way you are using them all over the place. Pretty soon (carrying on the code that you have in place), you are going to end up with composite keys potentially tens of columns long. I'd direct you to this answer from another question detailing why simply adding an Identity column to your code will help simplify your keys down a fair bit.

Composite keys can be handy in places where the data has to appear multiple places anyway, but if you are adding those columns simply to satisfy the key constraints, then a single Identity column would suit much better.

---

Related Topics