Sql (Oracle)-- cannot insert value(could be the constraint factor)

Question

As you can see in the code. three table have its own primary key. "protectmedalno" and "mastermedalno" are the foreign key of the player table. protectmedalno could not be null. masterdealno could be null. I drop table protector first, then drop master , the last drop player.

There is weak relationship between table player and table master.
There is no problem with inserting the value of protector and master. But inserting the value into table player, it will occur: *Cause: A foreign key value has no matching primary key value. *Action: Delete the foreign key or add a matching primary key.

I think that it is a problem with constraint.

insert into player values('01','Joe','101','');
insert into player values('02','Elsa','102','201');

insert into protector values('101','Dragon');
insert into protector values('102','Lion');
insert into master values('201','Fairy')
commits;

It could display the protector table and the master table. But it could not show the player table.

drop table protector;
drop table master;
drop table player;

CREATE TABLE player (
    playno           NUMBER(2) NOT NULL,
    playname         VARCHAR2(30) NOT NULL,
    protectmedalno   CHAR(10) NOT NULL,
    mastermedalno    CHAR(10)
);


ALTER TABLE player ADD CONSTRAINT play_pk PRIMARY KEY ( playno );

CREATE TABLE protector (
    protectmedalno   CHAR(3) NOT NULL,
    protectname      VARCHAR2(30) NOT NULL   
);


ALTER TABLE protector ADD CONSTRAINT protector_pk PRIMARY KEY ( protectmedalno );

CREATE TABLE master (
    mastermedalno   CHAR(3) NOT NULL,
    mastername      VARCHAR2(30) NOT NULL   
);

ALTER TABLE master ADD CONSTRAINT master_pk PRIMARY KEY ( mastermedalno );


ALTER TABLE player
    ADD CONSTRAINT player_protector_fk FOREIGN KEY ( protectmedalno )
        REFERENCES protector ( protectmedalno );

ALTER TABLE player
    ADD CONSTRAINT player_master_fk FOREIGN KEY ( mastermedalno )
        REFERENCES master ( mastermedalno );

try to insert `protector` and `master` values before `player` ones — Paplusc, May 15 '18 at 13:11
I think this is cause you are inserting `''` in the column of `mastermedalno` in the `player` table and it doesn't match with anythiing, try to write `null` there. — Paplusc, May 15 '18 at 13:20

StephaneM · Answer 1 · 2018-05-15T14:17:32.343

You are inserting in the wrong order: you must insert the master and protector first so that when you insert the player it can reference them:

insert into protector values('101','Dragon');
insert into protector values('102','Lion');
insert into master values('201','Fairy');

insert into player values('01','Joe','101',NULL);
insert into player values('02','Elsa','102','201');

Edit: '' is not NULL it's an empty String. To insert null use the explicit NULL jeyword.

score 1 · Answer 2 · answered May 15 '18 at 13:13

1

Since protector and master are the primary tables, you should populate the records there first. Then, insert into player and refer to those records:

insert into protector values('101','Dragon');
insert into protector values('102','Lion');
insert into master values('201','Fairy');

insert into player values('01','Joe','101','201');   -- refer to master
insert into player values('02','Elsa','102','201');  -- refer to master

Note that I edited the inserts into the player table such that both records refer to a record in the master table which actually exists.

answered May 15 '18 at 13:13

Tim Biegeleisen

502,043
27
286
360

Joe do not have a master. I tried to insert the order like what you said , the condition the same. Should I clear the table?or how to clear it ? – YeiBi May 15 '18 at 13:18
If you can, just drop everything, create the tables again, then use the correct insertion order. Not sure if Oracle allows `NULL` foreign keys, but I think the missing foreign key should be `NULL`, not empty string. – Tim Biegeleisen May 15 '18 at 13:19
I also tried null at the beginning without using the order suggested. null seems could not be used. – YeiBi May 15 '18 at 13:25
@YeiBi Foreign keys in Oracle [can be NULL](https://stackoverflow.com/questions/7573590/can-a-foreign-key-be-null-and-or-duplicate), so there must be another problem. – Tim Biegeleisen May 15 '18 at 13:27

score 1 · Answer 3 · answered May 15 '18 at 13:15

You first have to insert protector and master, afterwards insert player since player refers to master and protector and values have to be inside there.

Do otherwise round on delete...

insert into protector values('101','Dragon');
insert into protector values('102','Lion');

insert into master values('201','Fairy');

insert into player values('01','Joe','101','');
insert into player values('02','Elsa','102','201');

If you delete first delete from player, then from protector and master.

score 1 · Answer 4 · answered May 15 '18 at 13:18

To insert data in the player table, you need a record in the protector table. This is because of the foreign key restriction. When inserting data in a table that has a foreign key(Which in this case of protector vs player, cannot be null), you have to create the foreign record first.

 1. insert into protector values('101','Dragon');
    2. insert into player values('01','Joe','101','');
    3. insert into protector values('102','Lion');
    4. insert into master values('201','Fairy');
    5. insert into player values('02','Elsa','102','201');
    commits;

I hope this helps, happy debugging :)

score 0 · Answer 5 · answered May 15 '18 at 13:14

Always include the columns when doing an insert. You should also use single quotes for only string and date constants.

insert into player(playno, playname, protectmedalno, mastermedalno) 
     values(1, 'Joe', '101', '');

I don't think the problem is specifically on player, but you should do this for all your inserts and you'll find the problem.

stefan · Answer 6 · 2018-05-15T18:18:55.803

Populating the "parent" tables first (as our colleagues have suggested) is a step towards solving the problem. In order to get the foreign key constraint to work properly, I suggest modifying the DDL code, too.

With your tables in place (using Oracle 12c), we can do the following:

begin  
  insert into master ( mastermedalno, mastername ) values ('201','Fairy') ;

  insert into protector ( protectmedalno, protectname ) 
    values( '101', 'Dragon');
  insert into protector ( protectmedalno, protectname ) 
    values( '102', 'Lion');
end ;
/

SQL> select * from master;

MAS MASTERNAME                    
--- ------------------------------
201 Fairy                         

SQL> select * from protector ;

PRO PROTECTNAME                   
--- ------------------------------
101 Dragon                        
102 Lion

So far so good. When INSERTing into PLAYER, we get:

insert into player ( playno, playname, protectmedalno, mastermedalno )
  values('02', 'Elsa', '102', '201');

-- ORA-02291: integrity constraint (...PLAYER_MASTER_FK) violated - parent key not found

Suggestion: use the same datatype for the foreign key and the referenced key. Just drop the PLAYER table (including its constraints), and create it afresh:

drop table player cascade constraints ;

CREATE TABLE player (
    playno           NUMBER(2) NOT NULL,
    playname         VARCHAR2(30) NOT NULL,
    protectmedalno   CHAR(3) NOT NULL,     -- changed (was: CHAR(10))
    mastermedalno    CHAR(3)               -- changed (was: CHAR(10))
);

ALTER TABLE player
    ADD CONSTRAINT player_protector_fk FOREIGN KEY ( protectmedalno )
        REFERENCES protector ( protectmedalno );

ALTER TABLE player
    ADD CONSTRAINT player_master_fk FOREIGN KEY ( mastermedalno )
        REFERENCES master ( mastermedalno );

Now execute the INSERTs.

begin
  insert into player values('01','Joe','101','');     -- original INSERT
  insert into player values('02','Elsa','102','201'); -- original INSERT
  commit;
end;
/
-- PL/SQL procedure successfully completed.

The tables contain the following data now:

SQL> select * from player ;
PLAYNO  PLAYNAME  PROTECTMEDALNO  MASTERMEDALNO  
1       Joe       101             NULL           
2       Elsa      102             201            

SQL> select * from master ;
MASTERMEDALNO  MASTERNAME  
201            Fairy       

SQL> select * from protector;
PROTECTMEDALNO  PROTECTNAME  
101             Dragon       
102             Lion

Are both foreign key constraints working now? Yes.

SQL> insert into player values('03','Fifi','101','202');
Error starting at line : 1 in command -
insert into player values('03','Fifi','101','202')
Error report -
ORA-02291: integrity constraint (...PLAYER_MASTER_FK) violated - parent key not found

SQL> insert into player values('03','Fifi','103','201');
Error starting at line : 1 in command -
insert into player values('03','Fifi','103','201')
Error report -
ORA-02291: integrity constraint (...PLAYER_PROTECTOR_FK) violated - parent key not found

Sql (Oracle)-- cannot insert value(could be the constraint factor)

6 Answers6