violated - parent key not found error

database, oracle, sql

Solution

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.`

This means that the field `is_group` on the table `GroupMembers` must have an identical value on the table `Members` and `group_name` field.

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.

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:

    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 `Groups` containing all the group details, with the `primary key` stored as `number` , and name `group_id`.

Always remember that each table must have a `primary key`. It is good practice for this key to be a number.

So by having `member_id` in `Members`, `group_id` in `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 with the same id several times ).

Look at this example linking users to roles. It is the same case:

Problem

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 `GroupMembers` table is: ``` 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 `GroupMembers` table. Anyone have any ideas? I've been scratching for quite a while.

Original source