如果两个表都具有主键和外键约束，如何插入值？ [英] How to insert values if both the tables have primary and foreign key constrains?

问题描述

我有两个表作为A，B.表A有列col1作为主键，表B有列

作为col2作为主键.col1引用col2和col2引用col1（意味着两者互相依赖）。

如何在表A（col1）上插入记录???



我尝试了什么：



我尝试禁用一个约束。

I have two tables as A,B.Table A has column as col1 as primary key and table B has column
as col2 as primary key.col1 references to col2 and col2 references to col1(means both are dependent upon each other).
How i can insert records on table A(col1)???

What I have tried:

I tried with disabling one constraints.

推荐答案

除非至少有一列可以保存空值，你不能在任何一个表中输入数据而不需要做很多事情：你可以通过在两个表中创建一个永久的空条目并将外键指向最初，但这是一个混乱的解决方案。



但是......最重要的问题是为什么？这听起来不像是一个自然的数据结构 - 在现实世界中我们可以在数据库中建模的很少是自然递归的，因此您的数据设计可能需要进行良好的审查以检查是否存在你可以使用更好的结构。我见过这种事情的唯一一次是当人们犯错并假设InvoiceLines表需要一个外键回到发票表（他们这样做）和发票表也需要一个外国人发票行表的键（他们没有 - 您通过SELECT语句中的JOIN获得该链接）。

Unless at least one of the columns can hold null values, you can't enter data in either table without a lot of faffing about: you can do it by creating a permanent "empty" entry in both tables and pointing the foreign key at that initially, but it's a messy solution.

But ... the big question is "Why?" That doesn't sound like a "natural" data structure - very little in the real world that we can model in a database is "naturally recursive" so it's possible that your data design needs a good review to check that there isn't a better structure you can use. The only time I've seen this kind of thing is when people make mistakes and assume that the "InvoiceLines" table need a Foreign Key back to the "Invoices" table (which they do) and the "Invoices" table also need a Foreign Key to the "Invoice Lines" table (which they don't - you get that link via a JOIN in your SELECT statements).

这篇关于如果两个表都具有主键和外键约束，如何插入值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！