Argthtjtr

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.

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 remeber 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 to the same id several times ).

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

error is
you have to use same column name which is defined in reference table

i.e

CONSTRAINT m_group_name_fk FOREIGN KEY(group_name) REFERENCES Artist(group_name)

That group_name should be define as primary key in Artist Table.

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.