mariadb foriegn key insert error

Question

So, I was using mysql server on Centos 6 and it was alright then, I shifted my code to Centos 7 server.

I had a constraint in an table where I used to insert null values by default in MySQL Server. I guess thats not happenning in MariaDB.

I get the following error when inserting data.

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (dbName.tableName, CONSTRAINT constraintName FOREIGN KEY (columnName) REFERENCES externalTableName (externalTableColumnName))

Any help would be appreciated.

Thanks

UPDATE 1 :

Table with the key to be referenced :

CREATE TABLE `users` (
  `uid` int(25) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`uid`)
);

CREATE TABLE `abc_xyz` (
  `isUser` int(25) DEFAULT NULL,
  `last_modified_user` int(25) DEFAULT NULL,
  KEY `abc_xyz_is_user` (`isUser`),
  KEY `abc_xyz_last_modified_user` (`last_modified_user`),
  CONSTRAINT `abc_xyz_is_user` FOREIGN KEY (`isUser`) REFERENCES `users` (`uid`),
  CONSTRAINT `abc_xyz_last_modified_user`
            FOREIGN KEY (`last_modified_user`) REFERENCES `users` (`uid`)
);

Check that the foreign key exists in `externalTableName` - looks like it might not! — khargoosh, Aug 22 '16 at 02:03
It does, as mentioned. The whole logic was working fine before, it started crashing now in MariaDB. I had also checked it before. — legalimpurity, Aug 22 '16 at 04:52
Could you 'SHOW CREATE TABLE' for both and the query and referenced row? — khargoosh, Aug 22 '16 at 04:58
Do not expect `(25)` to mean that you can have 25-digit integers. `INT` is limited to about 2 billion positive values. — Rick James, Aug 22 '16 at 18:20
Possible duplicate of [Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails](http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa) — miken32, Aug 22 '16 at 18:26

score 0 · Answer 1 · answered Aug 21 '16 at 18:11

0

If the columnName is NULLable then you are not inserting a NULL value. I really doubt that inserting NULL in a NULLable column can create a foreign key failure. Check the query being called (if you use ORM, etc).

answered Aug 21 '16 at 18:11

Boris Schegolev

3,601
5
21
34

Hello Boris, I know it sounds crazy, but it is. It was working fine in Mysql Server before. I am not using an ORM. I tried executing the query manually, then I got the error above. – legalimpurity Aug 22 '16 at 04:52
@RickJames Hello Rick, actually i modified the data of my orignal definition before posting it on Stackoverflow, when I tried running my own sample above. it can still insert, this makes me more confused as why my orignal tables is failing to insert data. Maybe it will be some trigger, giving this error. Let me check and get back to you. thanks. – legalimpurity Aug 23 '16 at 18:21

khargoosh · Answer 2 · 2016-08-22T06:26:40.090

0

The issue is that your referring column can contain a NULL value while the referred column cannot (it's a PRIMARY KEY). You can't enter a default null value in the abc_xyz.last_modified_user column and maintain a valid foreign key to the users.uid that cannot accept null.

A PRIMARY KEY Constraint is either a < Table Constraint> or a and defines a rule that constrains a unique key to non-duplicate, non-null values only. The required syntax for a PRIMARY KEY Constraint is:

https://mariadb.com/kb/en/sql-99/constraint_type-primary-key-constraint/

edited Aug 22 '16 at 06:26

answered Aug 22 '16 at 06:09

khargoosh

1,450
15
40

No, Khargoosh. We can, check this out : http://stackoverflow.com/questions/2366854/can-table-columns-with-a-foreign-key-be-null Also, it was working before in MYSQL server. This problem started coming only after porting the code to MariaDB. – legalimpurity Aug 22 '16 at 06:14
It's the `PRIMARY KEY` that can't be null, not the foreign key necessarily. Previous versions of MySQL may have allowed this behaviour, which may explain the difference you are seeing. – khargoosh Aug 22 '16 at 06:19
So ? My Primary key is not null. I am trying to insert null in foreign key, exactly as the example in the link does. – legalimpurity Aug 22 '16 at 06:24
Think about what you are asking the database to do. How can you insert a foreign key value, that can not exist in the referred column? It is being constrained according to your design. – khargoosh Aug 22 '16 at 06:25
The constraint is designed in such a way, that the only value that can be entered in that column is should be a primary key of users table. Otherwise nothing can be entered, that's how I designed the concept. And I just shared the link with you. see the example its possible. ALSO I WAS DOING IT BEFORE IN MYSQL SERVER !!!! Thanks, for being patient with me BDW. :P – legalimpurity Aug 22 '16 at 06:28
However, if at least one of the foreign key values is NULL, the row has no parents, but it is still allowed. – legalimpurity Aug 22 '16 at 06:31
Disregard the comment above. Just realised the link you shared is for Primary key and not foreign key. https://mariadb.com/kb/en/mariadb/foreign-keys/ However, if at least one of the foreign key values is NULL, the row has no parents, but it is still allowed. ** We are talking about foreign keys, not primary keys. ** – legalimpurity Aug 22 '16 at 06:36

mariadb foriegn key insert error

2 Answers2