primary keys and foreign keys conflict

Question

this insert fails on my db -

insert into tig_pairs (pkey, pval, uid) select 'schema-version', '4.0', uid from tig_users where (sha1_user_id = sha1(lower('db-properties')));
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tigasedb`.`tig_pairs`, CONSTRAINT `tig_pairs_constr_2` FOREIGN KEY (`nid`) REFERENCES `tig_nodes` (`nid`))

Where table definitions are:

create table if not exists tig_pairs (
   nid int unsigned,
   uid int unsigned NOT NULL,

   pkey varchar(255) NOT NULL,    
   pval mediumtext,

   PRIMARY KEY (nid, pkey),      --        ***
             key pkey (pkey),
         key uid (uid),
         key nid (nid),
         constraint tig_pairs_constr_1 foreign key (uid) references tig_users (uid),
         constraint tig_pairs_constr_2 foreign key (nid) references tig_nodes (nid)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;

and

create table if not exists tig_nodes (
   nid int unsigned NOT NULL auto_increment,
   parent_nid int unsigned,
   uid int unsigned NOT NULL,

   node varchar(255) NOT NULL,

   primary key (nid), 
   unique key tnode (parent_nid, uid, node),
   key node (node),
         key uid (uid),
         key parent_nid (parent_nid),
         constraint tig_nodes_constr foreign key (uid) references tig_users (uid)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;

The line PRIMARY KEY (nid, pkey), -- ***gets omitted, then my query goes through just fine. Is there a conflict between that primary key and the troubling foreign key constraint ? How can I avoid it ? The primary key has to stay there :)

Thanks!

EDIT: got rid of the error by changing the tig_pairs definition on one line:

nid int unsigned NOT NULL auto_increment,

score 2 · Accepted Answer · edited May 23 '17 at 12:13

2

You have a foreign constraint on your tig_pairs table referencing the tig_nodes table. However, you are not inserting any data into tis nid field. The referenced field, tig_nodes.nid, doesn't allow NULL values. Due to these two constraints, you cannot INSERT null into the nid field of tig_pairs.

See also this question: MySQL foreign key to allow NULL?

Edit: also, primary key values are never allowed to be NULL; so as long as nid is included in that primary key, you cannot make it NULL.

edited May 23 '17 at 12:13

Community

1
1

answered Jun 15 '12 at 14:36

Yhn

2,785
2
13
10

uhm... spot on.Now, can you tip me on how to work around this ? That *** line is extra (i.e. was not there as the original programmer thought it all out) and it has to stay there. if PKs can not be made null, then I guess there are lots of queries and server logic that needs changing [gulp]. Any shortcuts ? – kellogs Jun 15 '12 at 15:08
I don't exactly know what the purpose of the tables is; but without changing the structure of them.. you might be able to put some "default" value in the `tig_nodes` table, and use the ID of that as default value for `nid` in `tig_pairs`? – Yhn Jun 18 '12 at 07:43
I have done an adjustment as shown in the EDIT section and it now works ok (I think :D ) – kellogs Jun 22 '12 at 01:59

primary keys and foreign keys conflict

1 Answers1