SQL 违反 - 未找到父密钥错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13518246/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
violated - parent key not found error
提问by AkshaiShah
I have the following error appearing:
我出现以下错误：
INSERT INTO GroupMembers VALUES ('Goldfrat', 'Simon Palm')
*
ERROR at line 1:
ORA-02291: integrity constraint (SHAHA1.IAM_IS_GROUP_FK) violated - parent key
not found
The constraint in the GroupMemberstable is:
GroupMembers表中的约束为：
CONSTRAINT iam_is_group_fk FOREIGN KEY(is_group) REFERENCES Members(group_name)
The Members Table looks like this:
成员表如下所示：
CREATE TABLE Members (
group_name VARCHAR2(40),
CONSTRAINT g_id_pk PRIMARY KEY(group_name),
CONSTRAINT m_group_name_fk FOREIGN KEY(group_name) REFERENCES Artist(artistic_name));
All of the tables are created fine until it comes to creating the GroupMemberstable. Anyone have any ideas? I've been scratching for quite a while.
在创建表之前，所有表都创建得很好GroupMembers。谁有想法？我已经刮了一段时间了。
回答by ionutab
The problem is that
问题是
CONSTRAINT iam_is_group_fk FOREIGN KEY(is_group) REFERENCES Members(group_name);
references the table Members on the group_name field.
CONSTRAINT iam_is_group_fk FOREIGN KEY(is_group) REFERENCES Members(group_name);
references the table Members on the group_name field.
This means that the field is_group on the table GroupMembersmust have an identical value on the table Membersand group_namefield.
这意味着表上的字段 is_group 在GroupMembers表Members和group_name字段上必须具有相同的值。
In my opinion this is bad practice. First of all you should have a primary key field on the table GroupMembers. You should not store the names of the group members in the table GroupMembers, but their corresponding id from the table Members.
在我看来，这是不好的做法。首先，您应该在 GroupMembers 表上有一个主键字段。您不应将组成员的名称存储在表 GroupMembers 中，而应将其对应的 id 存储在表成员中。
Also the table Members, should look something like this:
还有表成员，应该是这样的：
CREATE TABLE Members (
member_id NUMBER PRIMARY KEY
member_name VARCHAR2(40),
CONSTRAINT g_id_pk PRIMARY KEY(member_id),
CONSTRAINT m_group_name_fk FOREIGN KEY(group_name) REFERENCES Artist(artistic_name));
Then on the table GroupMembers, I suppose you want to associate some members to their set of groups and back, so you should do something like this:
然后在 table 上GroupMembers，我想您想将一些成员关联到他们的一组组并返回，因此您应该执行以下操作：
CREATE TABLE GroupMembers (
member_id NUMBER,
group_id NUMBER
)
CONSTRAINT iam_is_member_fk FOREIGN KEY(member_id) REFERENCES Members(member_id);
CONSTRAINT iam_is_member_fk FOREIGN KEY(group_id) REFERENCES Groups(group_id);
Supposing that you have a table Groupscontaining all the group details, with the primary keystored as number, and name group_id.
假设您有一个Groups包含所有组详细信息的表，其中primary key存储为number和名称group_id。
Always remeber that each table must have a primary key. It is good practice for this key to be a number.
永远记住，每张桌子都必须有一个primary key. 将此键设为数字是一种很好的做法。
So by having member_id in Members, group_idin Groups, you can create a many to many relationship in GroupMembers. Also, put a unique index on this table so you don't have duplicates ( the same member associated to the same id several times ).
因此，通过将 member_id in Members, group_idin Groups，您可以在 中创建多对多关系GroupMembers。另外，在这个表上放置一个唯一索引，这样你就没有重复（同一个成员多次关联到同一个 id ）。
Look at this example linking users to roles. It is the same case:
看看这个将用户链接到角色的例子。这是同样的情况：
回答by Shamis Shukoor
Order in which you are insert is the reason for the error.
您插入的顺序是错误的原因。
Members(group_name)does not contain Goldfrat. If this is not true then it's not there in the table Artists.
Members(group_name)不包含Goldfrat. 如果这不是真的，那么它就不会出现在表中Artists。
回答by srinath
error is you have to use same column name which is defined in reference table
错误是您必须使用在引用表中定义的相同列名
i.e
IE
CONSTRAINT m_group_name_fk FOREIGN KEY(group_name) REFERENCES Artist(group_name)
CONSTRAINT m_group_name_fk FOREIGN KEY(group_name) REFERENCES Artist(group_name)
That group_nameshould be define as primary keyin ArtistTable.
这group_name应该被定义为primary key在Artist表。
