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:
Child table:
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.
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
