Ошибка violated parent key not found

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:

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:

ORA-02291: integrity constraint violated – parent key not found error occurs when a foreign key value in the child table does not have a matching primary key value in the parent table, as stated by a foreign key constraint. You try to insert a row into a child table that does not have a corresponding parent row. The column value you supplied for the child table did not match the primary key in the parent table.

You try to insert or update a row in the child table. The value in the child table’s reference column should be available in the parent table’s primary key column. If the primary key column does not have a value, the row cannot be inserted or updated in the child table. The parent key’s integrity constraint was violated.

The value of the child table’s foreign key column should be the same as the value of the parent table’s primary key column. If the value does not exist in the parent table, an error ORA-02291: integrity constraint violated – parent key not found will be thrown.

Cause

A foreign key value has no matching primary key value.

Action

Delete the foreign key or add a matching primary key.

The Problem

When two tables in a parent-child relationship are created, a referential foreign key constraint is generated and enforces the relationship between the two tables. The value of the foreign key column in the child table is decided by the value of the primary key column in the parent table.

A value that is not available in the parent table cannot be inserted or updated in the child table. If you try to insert or update a value in the foreign key column of a child table, Oracle will throw the parent key integrity constraint violation error.

create table dept
(
 id numeric(5) primary key,
 name varchar2(100)
);

create table employee
(
  id numeric(5) primary key,
  name varchar2(100),
  deptid numeric(5) references dept(id)
);

insert into employee values(1,'Yawin',1);

Error

Error starting at line : 17 in command -
insert into employee values(1,'Yawin',1)
Error report -
ORA-02291: integrity constraint (HR.SYS_C0012551) violated - parent key not found

Solution 1

If the integrity constraint is violated, knowing the parent and child tables involved in the foreign key relationship is important. The parent and child table names, as well as the column names, may be retrieved using the integrity constraint name. The parent table, child table, parent column name, child column name, and integrity constraint name will be shown in the following sql query.

select r.constraint_name Foreign_key_constraint,
    p.owner parent_owner, p.table_name parent_table, pc.column_name parent_column_name, 
    r.owner child_owner, r.table_name child_table, rc.column_name child_colum_name
from user_constraints p
join user_cons_columns pc on p.owner=pc.owner 
        and p.table_name=pc.table_name and p.constraint_name = pc.constraint_name
        and p.constraint_type='P'
join user_constraints r on p.constraint_name=r.r_constraint_name and r.constraint_type='R'
join user_cons_columns rc on r.owner=rc.owner 
        and r.table_name=rc.table_name and r.constraint_name = rc.constraint_name
        and r.constraint_type='R'
where r.constraint_name='SYS_C0012551' 
order by p.owner, p.table_name, pc.column_name, rc.position;

Output

Foreign_key_constraint | parent_owner |parent_table | parent_column_name |child_owner | child_table | child_colum_name
SYS_C0012548	HR	DEPT	ID	HR	EMPLOYEE	DEPTID

Solution 2

The value you are trying to put into the child table reference column does not exist in the parent table. You must first enter the value that you intended to insert into the child table into the parent table. After inserting the value as a parent row, you may go back and enter it into the child table.

insert into dept values (1, 'sales');

insert into employee values(1,'Yawin',1)

Output

1 row inserted.

1 row inserted.

Solution 3

You are attempting to insert a row into a child table for which the primary key does not exist in the parent table. Before you enter a child, make sure you have a parent key for that child in the parent table.

insert into employee values(1,'Yawin',1)

insert into employee values(1,'Yawin',1)
Error report -
ORA-02291: integrity constraint (HR.SYS_C0012551) violated - parent key not found


insert into employee values(1,'Yawin',100) -- the value 100 exist in the dept table.