Foreign key constraint fails



  • 1. Query-based update failed
    I've been scratching my head with this. I'm using a select query to locate a record from a table based upon its primary key. I then set some field values to those returned from a web page form. I then do a recordset.update. If the web form details differ from the saved values in the database then the update works just fine. If they are the same, an error is returned, being "Query-based update failed because the row to update could not be found." Now, I know this is not the generally accepted way to do things, but I still want to know why it fails... I've tried all combinations of cursors and locks. It's totally retarded in my opinion. any thoughts? regards, paolo
  • 2. Why does 'SELECT * FROM countries' truncate characters and SELECT country FROM countries does not ?
    Hi, Why does 'SELECT * FROM countries' truncate characters and SELECT country FROM countries does not ? mysql> SELECT * FROM countries; +-------------+-----------+ | country | capital | +-------------+-----------+ |ghanistan | Kabul |lbania | Tirane |Algeria | Algiers mysql> SELECT country FROM countries; +-------------+ | country | +-------------+ | Afghanistan | | Albania | | Algeria | +-------------+ 3 rows in set (0.00 sec) c:\countries.csv Afghanistan, Kabul Albania, Tirane Algeria, Algiers mysql> LOAD DATA LOCAL INFILE 'c:\countries.csv' INTO TABLE countries -> FIELDS TERMINATED BY ','; Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 countries TABLE country VARCHAR(30) NOT NULL capital VARCHAR(50) NOT NULL
  • 3. Why is my MySQL database so slow
    Hi, two things come to my mind spontanously that should improve performence, which are a) use a transaction for the INSERT statements - that prevents MySQL from flushing the index for every row (instead, MySQL only does it once when you commit the transaction) and b) prepare the statement, because it only requires to transfer the actual data for every row and not the whole SQL command. One further thing that should improve the performence slightly would be to put the $table = "mytable"; line outside the for loop (as it doesn't change within the loop, it works as well to declare it outside, so this line of code runs only once instead of 2,000 times). Finally, if you use MySQL 5, the best solution would be to insert the rows in a Stored Procedure (this would require only one single roundtrip to the database server, which should give performance a boost). However, 600 rows in 30 seconds still looks very slow - maybe there are other factors involved which are outside the scope of programming and database design. Markus
  • 4. Problem using OLE DB 3 with MySQL 5
    Hi all, i would like to use OLE DB provider ver 3.0 with My SQL 5.0 under Windows XP But when i try to test connection during the UDL file creation i get an "Error during provider initialization". COudl someone post some advice or connection's strings?? Thanks in advance Stefano
  • 5. distinct, count
    hello, is there something wrong w/this query? SELECT DISTINCT(tbuser.fk_userid), COUNT(tbuser.fk_userid) AS cuserid........ I'm getting this error: Unknown table 'tbuser' in field list (table is there, use in other queries..) I'm converting from a DB2 VIEW here... orig was: select count(distinct(tbuser.fk_userid)) thank you very much.. Frances

Foreign key constraint fails

Postby Nuno » Wed, 14 Feb 2007 21:54:26 GMT


I have a sql script that create some tables and some of them have
foreign key restrictions like this:

alter table T_SCHEDULE_TO_DATAFLOW add constraint FK_T_SC_DF foreign
      references T_SCHEDULE (SCHEDULE_ID) on delete restrict on update
alter table T_SCHEDULE_TO_DATAFLOW add constraint FK_T_SC_DF2 foreign
      references T_DATAFLOW (DATAFLOW_ID) on delete restrict on update

I'm executing the same insert statement ('insert into
T_SCHEDULE_TO_DATAFLOW values (10,10,2);' and the 10 id is not valid)
in two diferent mysql databases (with the same sql script), one of
them is in the windows operative system and the other is in the linux.

Windows (XP) version of mysql is: mysql Ver 14.12 Distrib 5.0.27, for
Linux (Suse server) version of mysql is: mysql Ver 14.12 Distrib
5.0.24a, for pc-linux-gnu (i686) using readline 5.0

And in windows i get what i'm was expecting:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint f
ails (`fxsim/t_schedule_to_dataflow`, CONSTRAINT `FK_T_SC_DF` FOREIGN

But in the linux the insertion return ok, and does not return the
foreign key constraint error.

Any idea why this happen, or if there is any configuration to activate
this constraints and if exists what is...


Re: Foreign key constraint fails

Postby Captain Paralytic » Wed, 14 Feb 2007 22:17:22 GMT

Apart from what the insert returns, on the linux system, do you see
the record in the table after the insert?

Re: Foreign key constraint fails

Postby Nuno » Thu, 15 Feb 2007 00:00:10 GMT


Captain Paralytic escreveu:

Re: Foreign key constraint fails

Postby Captain Paralytic » Thu, 15 Feb 2007 00:06:15 GMT

Are the tables in both systems using the same engine? Sounds like one
may be using InnoDB and the other MyISAM. MyISAM will accept the
foreign key ALTER commands but cannot implement them.

Similar Threads:

1.foreign key constraints or / and unique key ?

I have the following database structure :

my_user ( id, name );
my_country (id, name );
my_travel ( id_user , id_country , length_of_stay );

id_user AND id_country are both external keys. They references my_user 
and my_country.

In mysql, innoDB engine, is it better to :

1/ add 2 foreign key constraints to my_travel.id_user and 


2/ add only one unique key on (id_user, id_country)

The basic fact is : when I add foreign key constraints AND unique key, 
mysql tells me that my_travel fields should'nt be part of an unique and 
foregn key in the same time.

Any ideas ?

2.Snapshot Repl. Failing with Foreign Key Constraints

 Hi Guys,

I wondered if you would be able to assist on this one....?

I have a server with several publications, all going to databases on one 
subscriber at a remote site. Some of the DBs work fine with snapshot 
replication and need no further attention. However, several of the DBs fail 
out with the following error:

3726: Could not drop object "TBL NAME" because it is referenced by a foreign 
key constraint.

In the publication properties, articles tab, I have set the article defaults 
for the tables NOT to Include declared referential integrity and have changed 
the name conflicts to delete all data in the existing table. (I changed from 
DROP the existing table and recreate it). When I do this I get the following:

4712: Cannot truncate table "TBL NAME" because it is being referenced by a 
foreign key constraint.

I have been chasing my tail on this for a while now and am gradually getting 
dizzy in the process!!!! 

Any help you could offer would be very much appreciated....



3.Merged items fail due to foreign key constraint

We have merge replication set up between 2 of our local servers.  Both are
MSSQL 2k, SP3a.  As part of our database, we have tables for Software and
SoftwareMedia where the Software may have many SoftwareMedia (e.g. disks 1 -
4).  Foreign keys are defined on the 2 tables such that the SoftwareMedia
references the Software ID in the Software table.  We had a problem occur
the other day where 5 sets of Software/Software media were installed by a
user.  3 of the sets were processed correctly and 2 had problems.  The 2
that had problems were the first 2 to be entered, and when replication
happened it appears that the SoftwareMedia replicated before the Software,
thus causing a foreign key violation at the subscriber.  On the next
replication cycle this translated into deleting the data back at the
publisher.  The other 3 sets had no such problems even though they were
entered by the same person in the same way.  Our merge articles are defined
in the proper order i.e. the Software article comes before the SoftwareMedia
article.  (Note: the 3 that worked happened to be added just after the first
replication cycle mentioned above but before the second.)

Can anyone tell me why we would have had the child table's data being
replicated before the parent in some instances but not in others?  Any other
thoughts/suggestions on this issue would be appreciated.

Ron Lounsbury

4.MergeReplication fails for foreign key constraints across publicat

We have created Merge replication publications to synchronize data from a 
central SQL server to client systems running SQL Express. 

When we are trying to propagate schema changes along with data changes(for 
instance when an enhancement/release is made),  the Replication of schema 
changes fails when there are foreign key constraints across multiple 
publications. This problem does not come up if there is only one publication, 
however for performance consideration we had to create multiple 
publications(We have already partitioned the publications wherever possible 
using SHOST_NAME parameter. 

Any pointers or help to resolve this issue will be very much welcome. 
Venkat B

5.Foreign Key Constraints

Hi  - I'm trying to create foreign key constraints for a few tables in
InnoDB tables. Here are the pertinent parts of the create statements:



) ENGINE = InnoDB;

This gives a:

#1005 - Can't create table './admin_connex/connex.frm' (errno: 150)

Which is a malformed foreign key constraint ... Can anyone help me spot
it? Thanks very much,

-- whit

6. Inconsistent error with create table statement containing foreign key constraint

7. Foreign Key Constraints

8. SQL-DMO Key object (foreign key constraint) with cascade

Return to mysql


Who is online

Users browsing this forum: No registered users and 5 guest