Parent key not found workaround

Question

I have a table USER

CREATE TABLE "USER"
  (
    USER_ID    INTEGER NOT NULL ,
    CREATED_BY INTEGER NOT NULL ,
  ) ;
ALTER TABLE "USER" ADD CONSTRAINT USER_PK PRIMARY KEY ( USER_ID ) ;

For this particular database, I need the CREATED_BY table to be a foreign key to USER_ID in the same table.

ALTER TABLE "USER" ADD CONSTRAINT USER_USER_FK FOREIGN KEY ( CREATED_BY ) REFERENCES "USER" ( USER_ID ) ;

Because of this constraint, my insert statement will always fail on the first user.

Is there a best practice for getting around this? Can I disable the constraint for the first user added and then re-enable the constraint?

is USER_ID really an auto increment? you could make it null instead as suggested by others then once inserted update CREATED_BY to the userID that was inserted and then make it not null again. If it is not auto incremented, just leave it as it is and repeat your USER_ID value for you CREATED_BY value in your insert statement — Matt, Aug 22 '16 at 18:31

score 1 · Answer 1 · answered Aug 22 '16 at 18:23

1

You can modify your create table script, so that it accepts null values for created_by column. Then you can insert your first record(that has a user_id and a null for created_by). Logically someone in that User table has to be the root record(in other words admin or first-user whose record isn't created by someone else).

answered Aug 22 '16 at 18:23

hchaznedaroglu

155
11

1

remove not null condition when you define the "created_by" column. – hchaznedaroglu Aug 22 '16 at 18:33
CREATED_BY INTEGER NOT NULL , becomes CREATED_BY INTEGER – Matt Aug 22 '16 at 18:33
I added an answer trying to type code in comments is bad practice. cheers – Matt Aug 22 '16 at 19:03

score 0 · Answer 2 · edited May 23 '17 at 11:51

0

CREATE TABLE "USER"
  (
    USER_ID    INTEGER NOT NULL ,
    CREATED_BY INTEGER ,
  ) ;
ALTER TABLE "USER" ADD CONSTRAINT USER_PK PRIMARY KEY ( USER_ID ) ;

If table exists this line makes the column NOT NULL or non nullable.

ALTER TABLE USER MODIFY CREATED_BY INTEGER NOT NULL;

How to add not null constraint to existing column in MySQL5.1

edited May 23 '17 at 11:51

Community

1
1

answered Aug 22 '16 at 19:02

Matt

13,833
2
16
28

Parent key not found workaround

2 Answers2