Creating relationship to non-primary column

Question

I have a table which references a foreign key to its main table. But I want to add an other reference to another table.

For instance, let's suppose I have three tables: Child, Parent, Transaction

Parent table:

• ParentID

Child table:

• ChildID
• ParentID

Transaction table:

• TransactionID
• ParentID (references ParentID on Parent table, nullable)
• ChildID (references ChildID on Child table, nullable)

I want to add a cascade on update reference to ParentID on Child table. So that when a relationship changes in parent-child then my ParentID on the Transaction table will be automatically updated.

Answer 1

Obviously, ParentID is not the primary key on the Child table.

Is this column unique over the whole table?

• If yes: you can define a unique index on ParentID on the Child table and then add the FK reference to that unique index

• If NO: if ParentID on Child is not unique, then you cannot create a FK reference to it. The "target" of a FK reference must be either the primary key of that table, or at least a unique column on that table. Otherwise, which row exactly are you referring to if you have ParentID = 42 and the value is not unique?!?!?

Simply, I want the same value pair on my ParentID&ChildID columns on Transaction table as ParentID&ChildID columns on Child table

In that case, you need a FK relation on both columns - create it like this:

CREATE UNIQUE INDEX UX_ParentChild
ON dbo.Child(ParentID, ChildID)

ALTER TABLE dbo.Transaction
ADD CONSTRAINT FK_Transaction_Child
FOREIGN KEY(ParentID, ChildID) REFERENCES dbo.Child(ParentID, ChildID)
ON UPDATE CASCADE