MySQL Forums
Forum List  »  Newbie

Re: self relations... (what??!!!)
Posted by: Dave Smith
Date: March 16, 2005 02:39PM

I have never heard of one column in a record having a foreign key constraint to another column in that same record. I am curious about the requirement for this and suspect there is a better way.

Having said that, I tested this under mysql 4.1.10 and Mircrosoft SQL Server 2000. mysql acts just as you describe while M-SQL allows this same self-referential configuration but has no problem deleting a row that refers to itself.

So my guess is that it is a bug in mysql even though there is the following statement in the mysql documentation:

"Foreign key support addresses many referential integrity issues, but it is still neccessary to design key relationships carefully to avoid circular rules..." Section 1.5.5.5 Foreign Keys

I could argue that this is a circular rule and thereby documented as not supported. I believe that the foreign key should, by definition, refer to a value in another (i.e., foreigh) table. Whether or not this is a bug, I would be happy to work with you to find a better way to do whatever you need to do.

Dave

Options: ReplyQuote


Subject
Written By
Posted
March 16, 2005 10:48AM
Re: self relations... (what??!!!)
March 16, 2005 02:39PM
March 17, 2005 09:33AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.