Foreign key constraint fails

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 
my_travevl.id_country

or

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....

Cheers,

Ian.
 
 

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.


TIA
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. 
-- 
Thanks
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:

CREATE TABLE a (
	id INT NOT NULL AUTO_INCREMENT,
...
	PRIMARY KEY (id)
...

CREATE TABLE b (
	id INT NOT NULL AUTO_INCREMENT,
...
	PRIMARY KEY (id)
...

CREATE TABLE connex (
	a_id INT NOT NULL,
	b_id INT NOT NULL,
	id INT NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (id),
	FOREIGN KEY (a_id) REFERENCES a(id) ON DELETE CASCADE,
	FOREIGN KEY (b_id) REFERENCES b(id) ON DELETE CASCADE
) 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