RE: Foreign key constraint problem
In some hierarchies I have seen people put the the current id in the
parent_ID Field {basicaly pointing to them self} to represent the top of
the hierarchy.
I don't know how much this would affect the rest of your application but
it would get rid of the null's
-Original Message-
From: Marcus Bointon [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 14, 2005 11:37 AM
To: mysql@lists.mysql.com
Subject: Foreign key constraint problem
I have a table that uses a self join to represent simple hierarchies.
I have a parent_id field that contains a reference to the same
table's id field. Not all items have a parent, so parent_id is nullable.
The problem I run into is in defining the foreign key constraint - if
a parent item is deleted, I want all the children to cascade delete.
But it seems I can't combine cascade deletion with nullable - I can
never have more than one record with a parent_id of null because the
insert causes the foreign key constraint to fail. This means I can
never have more than one tree stored in the table. How should I set
up this relation so it works how I want? I'd really prefer not to
maintain it manually...
Marcus
--
Marcus Bointon
Synchromedia Limited: Putting you in the picture
[EMAIL PROTECTED] | http://www.synchromedia.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign key constraint problem
Marcus Bointon [EMAIL PROTECTED] wrote on 06/14/2005 12:37:18 PM: I have a table that uses a self join to represent simple hierarchies. I have a parent_id field that contains a reference to the same table's id field. Not all items have a parent, so parent_id is nullable. The problem I run into is in defining the foreign key constraint - if a parent item is deleted, I want all the children to cascade delete. But it seems I can't combine cascade deletion with nullable - I can never have more than one record with a parent_id of null because the insert causes the foreign key constraint to fail. This means I can never have more than one tree stored in the table. How should I set up this relation so it works how I want? I'd really prefer not to maintain it manually... Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk My suggestion, lame as it is, would be to use a 0 (zero) in place of the NULL value. That way, you always have a valid entry for PARENT_ID, you can still identify the tops of the trees (parent_id=0) and you have gotten around the only-one-parent-entry-can-be-null trap. Since 0 (or any other non-positive number) won't be auto-generated by an auto_increment field, you should be safe. In fact, you could even pick descending negative numbers (-1, -2, -3) as the parent_id of your multiple trees as a means of unique identification. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Foreign key constraint problem
On 14 Jun 2005, at 17:52, [EMAIL PROTECTED] wrote: My suggestion, lame as it is, would be to use a 0 (zero) in place of the NULL value. That way, you always have a valid entry for PARENT_ID, you can still identify the tops of the trees (parent_id=0) and you have gotten around the only-one-parent-entry-can-be-null trap. Because of the cascade deletion setting, it will not allow inserts of any record where a record with the parent ID does not exist because the new child record will be deleted immediately in order to satisfy the constraint. As far as I'm aware this is exactly the kind of thing that null vales are for - for example a constraint where the action is set_null instead of cascade would quite correctly set the parent_id to null, leaving an unowned child. What I need is a cascade on delete that allows null values (on the basis that null != null because neither is a value). Am I just fantasizing? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: foreign key constraint problem
On Saturday 15 March 2003 04:08, vishnu mahendra wrote: create table stud(rno integer not null, name char(10), primary key(rno)); create table mark(rno integer not null references stud, mark integer); [skip] how is it possible. there is no rollno 3 in the stud table, then how does it accept the rollno 3 in the mark table. does foreign key really working, or i am doing something wrong. REFERENCES do nothing in MySQL for all table types except InnoDB tables. How to use FOREIGN KEY CONSTRAINTS on InnoDB table look at: http://www.mysql.com/doc/en/SEC457.html how can i convert the default date format to the format 'dd-mm-'. Use DATE_FORMAT() function: http://www.mysql.com/doc/en/Date_and_time_functions.html or how can i insert the date in the format 'dd-mm-'. You can't if you want to store as DATE/DATETIME column type. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
