Foreign Key Constraint Post Import Check

[vtest]

Hi,

I've download trial version of SQLyog and trying to work with it on importing some data to my MySQL database.

I need your help on the following issue I'm having and I appreciate your time and desire to help.

I've created 2 tables in MySQL, both are InnoDB:

create table `temp_state` (

`STATE_ID` bigint(20) NOT NULL,

`STATE_NM` varchar(50) NOT NULL,

PRIMARY KEY (`STATE_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

create table `temp_user` (

`CUSTOMER_ID` bigint(20) NOT NULL AUTO_INCREMENT,

`STATE_ID` bigint(20) NOT NULL,

`NAME` varchar(30) NOT NULL,

PRIMARY KEY (`CUSTOMER_ID`),

KEY `FK_temp_user_1` (`STATE_ID`),

CONSTRAINT `temp_ser_ibfk_1` FOREIGN KEY (`STATE_ID`) REFERENCES `temp_state` (`STATE_ID`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

I inserted couple of records in the temp_state table

insert into temp_state (STATE_ID, STATE_NM) values (1, 'ON'), (2,'QC');

temp_user table I intentionally left empty.

I also made 2 new table at the source:

create table `temp_state` (

`STATE_ID` bigint(20) NOT NULL,

`STATE_NM` varchar(50) NULL,

PRIMARY KEY (`STATE_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

create table `temp_user` (

`CUSTOMER_ID` bigint(20) NOT NULL AUTO_INCREMENT,

`STATE_ID` bigint(20) NOT NULL,

`NAME` varchar(30) NOT NULL,

PRIMARY KEY (`CUSTOMER_ID`),

KEY `FK_temp_user_1` (`STATE_ID`),

CONSTRAINT `temp_ser_ibfk_1` FOREIGN KEY (`STATE_ID`) REFERENCES `temp_state` (`STATE_ID`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

As you can see there is one small difference – in source table STATE_NM can be null, at the destination, it can't.

I inserted 3 records in the source temp_state table:

insert into temp_state (STATE_ID, STATE_NM) values (1, 'ON'), (2,'QC'), (3,null);

And I inserted 6 records into the temp_user table at source:

insert into temp_user (CUSTOMER_ID, STATE_ID, NAME) values

(1,1,'John')

,(2,2,'Paul')

,(3,3,'Bill')

,(4,2,'Sam')

,(5,1,'Mike')

,(6,2,'Bob')

;

Everything is ready, I start SQLyog import from table temp_user into table temp_user on different db. I check marked box “Import foreign key” in Advanced part of the mapping. I did not put the check mark on the box Abort on error!

Couple of seconds – 6 records where inserted into temp_user table. Including Bill, that had failed foreign key constraint, as at the destination table temp_state there was no record for STATE_ID = 3. If I am trying to insert record manually, it fails. If I try to update Bill's name to Phill, it fails. But SQLyog did that without any error in the sja.log!

Second thing I did – I am importing table temp_state into temp_state. And I'm getting records with STATE_ID = 3. And STATE_NM = null! Even though STATE_NM is not nullable at the destination. Manually, I can't insert record (4, null) there, it fails. If I try to update record with STATE_ID = 2 to have STATE_NM = null, it fails. But there is no sign of error in the log again.

After such a long, but I hope breathtaking story, my question would be – if there any way to run some process post import to log or to identify all places where the records were inserted even though they violated some constraints that would not allow normal manual insert, but do work with SQLyog?

Thank you for your patience to read it till the end and I hope to get some wisdom on that!

[peterlaursen]

Please clarify: What exact SQLyog feature are you using? From this “I start SQLyog import ..” it is not quite clear to me if it is 'Data Sync' or 'Import external data'.

[vtest]

'peterlaursen' wrote:

Please clarify: What exact SQLyog feature are you using? From this “I start SQLyog import ..” it is not quite clear to me if it is 'Data Sync' or 'Import external data'.

Import External Data one. Thank you!

[peterlaursen]

What I can come up with right from my head is something like

Code:

SELECT * FROM childtable WHERE childcolumn NOT IN (SELECT parentcolumn FROM parenttable);

But the inner query will generate a temporary table in memory. It should be possible to write it as a JOIN using indexes (if proper indexes exist on the table)

[vishal.pr]

Its because SET FOREIGN_KEY_CHECKS = 0;

Read this http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

[Author]

Posts