创建与非主列的关系 [英] Creating relationship to non-primary column
问题描述
我有一个引用外键到主表的表。例如,假设我有三个表: 子表格: Child Child,Parent,Transaction
表上的
$ p
$ b $ <$> li> ParentID
$ b
ParentID
表, nullable )
ParentID引用 上的
表。 Transaction
表中的 ParentID
会自动更新。显然, ParentID
并不是<$ c $>上的主键c>子
这个列在整个表中是唯一的吗? 如果是,您可以在
ParentID
上定义唯一索引 Child
表,然后将FK引用添加到该唯一索引中。 / strong>:如果 ParentID
在 Child
不是独一无二的,那么您不能创建FK引用。 FK引用的目标必须是该表的主键,或者该表的至少一个唯一列。否则,如果您有 ParentID = 42
,并且值不是是唯一的,那么您指的是哪一行?!?!?
lockquote
简单地说,我希望在Transaction表上的ParentID& ChildID列上有相同的值对作为Child Table上的ParentID& ChildID列
在这种情况下,您需要在
- 创建它像这样: 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
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.
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 theChild
table and then add the FK reference to that unique indexIf NO: if
ParentID
onChild
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 haveParentID = 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
这篇关于创建与非主列的关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!