foreign key on hierarchical structure?

Hello all - I am writing a table where I am using a hierarchical structure to create a series of nested questions. My structure is this: id | parent_id | question ---------------------------------- 1 | NULL | "Yes or no?" 2 | 1 | "Orange or Yellow?" 3 | 3 | "Sugar or cream?" I'm just getting into foreign keys on InnoDB tables. I'm wondering if I can create the parent_id column as a foreign key in the same table, that would make it easier for me to delete an entire branch of questions. Can I create a foreign key in the same table? I need root questions to have a NULL parent_id. Does a foreign key constraint prevent me from having a NULL parent_id? If I can't create a foreign key in a hierarchical structure, what's the best way to delete an entire branch of questions? Currently I am using a looping code structure where I descend and find the leafs, delete them, and then work my way back up. This would be a lot easier if I could use a foreign key and just delete the root question.

On Nov 7, 3:26 pm, lawp...@gmail.com wrote: > > I'm just getting into foreign keys on InnoDB tables. I'm wondering if > I can create the parent_id column as a foreign key in the same table, > that would make it easier for me to delete an entire branch of > questions. Did you try doing it? If you had you'd find that you can. > > Can I create a foreign key in the same table? I need root questions to > have a NULL parent_id. Does a foreign key constraint prevent me from > having a NULL parent_id? Did you try that, too? Again, if you had, you'd find that it also works.

On Nov 8, 8:38 am, ZeldorBlat <zeldorb...@gmail.com> wrote: > On Nov 7, 3:26 pm, lawp...@gmail.com wrote: > > Did you try doing it? If you had you'd find that you can. > > Did you try that, too? Again, if you had, you'd find that it also > works. Okay, but why does it work? Isn't the idea of the foreign key column to guarantee that the value in the foriegn key column exists in the referenced column? Does NULL mean that there is no value, therefore there isn't a value in the column that does not exist in the referenced column? I.e. for a foriegn key column value, the value must either already exist in the reference column, or else be NULL, even if a NULL value does exist in the reference column?

lawpoop@gmail.com wrote: > On Nov 8, 8:38 am, ZeldorBlat <zeldorb...@gmail.com> wrote: >> On Nov 7, 3:26 pm, lawp...@gmail.com wrote: >> >> Did you try doing it? If you had you'd find that you can. >> >> Did you try that, too? Again, if you had, you'd find that it also >> works. > > Okay, but why does it work? Isn't the idea of the foreign key column > to guarantee that the value in the foriegn key column exists in the > referenced column? > > Does NULL mean that there is no value, therefore there isn't a value > in the column that does not exist in the referenced column? I.e. for a > foriegn key column value, the value must either already exist in the > reference column, or else be NULL, even if a NULL value does exist in > the reference column? Might I respectfully refer you to: http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html http://dev.mysql.com/doc/refman/5.0/en/example-foreign-keys.html http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

On Wed, 07 Nov 2007 20:26:15 -0000, lawpoop@gmail.com wrote: >Hello all - > >I am writing a table where I am using a hierarchical structure to >create a series of nested questions. My structure is this: > >id | parent_id | question >---------------------------------- >1 | NULL | "Yes or no?" >2 | 1 | "Orange or Yellow?" >3 | 3 | "Sugar or cream?" > >I'm just getting into foreign keys on InnoDB tables. I'm wondering if >I can create the parent_id column as a foreign key in the same table, >that would make it easier for me to delete an entire branch of >questions. > >Can I create a foreign key in the same table? I need root questions to >have a NULL parent_id. Does a foreign key constraint prevent me from >having a NULL parent_id? > >If I can't create a foreign key in a hierarchical structure, what's >the best way to delete an entire branch of questions? Currently I am >using a looping code structure where I descend and find the leafs, >delete them, and then work my way back up. This would be a lot easier >if I could use a foreign key and just delete the root question. Theoretically, for purists, this schema is not normalized. Think of it as this: A table is a common name for a relation. The table you have here represents two different and incompatible relations: - the relation between an id and a question, - the relation between an id and its parent. Correcting this would give: t1 id | question ------------------- 1 | "Yes or no?" 2 | "Orange or Yellow?" 3 | "Sugar or cream?" t2 id | parent_id -------------- 2 | 1 3 | 3 Quote: SQL’s “Nulls” Are A Disaster ;) http://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf Yeah, I know, in real life it can be done. -- ( Kees ) c[_] If I'm supposed to live every day like it's my last, I want a gallon of morphine. (Russ Wallace) (#393)

On Nov 8, 5:49 pm, Kees Nuyt <k.n...@nospam.demon.nl> wrote: > On Wed, 07 Nov 2007 20:26:15 -0000, lawp...@gmail.com > wrote: > > > Theoretically, for purists, this schema is not normalized. > > Think of it as this: > A table is a common name for a relation. > The table you have here represents two different and > incompatible relations: > - the relation between an id and a question, > - the relation between an id and its parent. > > Correcting this would give: > t1 > id | question > ------------------- > 1 | "Yes or no?" > 2 | "Orange or Yellow?" > 3 | "Sugar or cream?" > > t2 > id | parent_id > -------------- > 2 | 1 > 3 | 3 > > Quote: > SQL's "Nulls" Are A Disaster ;) Thanks, Kees, this is exactly what I was looking for. Is there a name for this type of relationship, or a common naming convention for these two tables? E.g. "many-to-many" is a common name for a intermediary table that joins two tables. Is there also a common name for table 2 is your example?

On Nov 8, 5:49 pm, Kees Nuyt <k.n...@nospam.demon.nl> wrote: > > Theoretically, for purists, this schema is not normalized. > > Think of it as this: > A table is a common name for a relation. > The table you have here represents two different and > incompatible relations: > - the relation between an id and a question, > - the relation between an id and its parent. > > Correcting this would give: > t1 > id | question > ------------------- > 1 | "Yes or no?" > 2 | "Orange or Yellow?" > 3 | "Sugar or cream?" > > t2 > id | parent_id > -------------- > 2 | 1 > 3 | 3 > Another question: With the table structure described above, how would you delete an entire tree of questions? If you delete a root question in the questions table, a delete can cascade to the relationships table, deleting one row. But then, the delete from the relationships table cannot cascade back to the question table -- leaving the first node with no record of a parent question, and it thereby becomes a root question. Am I right? With this structure, it seems that I have to write code to loop through and delete all of these questions with individual statements. With the non-normalized structure shown earlier, I can delete the entire tree with a single delete statement.

On Fri, 09 Nov 2007 15:55:29 -0000, lawpoop@gmail.com wrote: >On Nov 8, 5:49 pm, Kees Nuyt <k.n...@nospam.demon.nl> wrote: >> On Wed, 07 Nov 2007 20:26:15 -0000, lawp...@gmail.com >> wrote: >> >> >> Theoretically, for purists, this schema is not normalized. >> >> Think of it as this: >> A table is a common name for a relation. >> The table you have here represents two different and >> incompatible relations: >> - the relation between an id and a question, >> - the relation between an id and its parent. >> >> Correcting this would give: >> t1 >> id | question >> ------------------- >> 1 | "Yes or no?" >> 2 | "Orange or Yellow?" >> 3 | "Sugar or cream?" >> >> t2 >> id | parent_id >> -------------- >> 2 | 1 >> 3 | 3 >> >> Quote: >> SQL's "Nulls" Are A Disaster ;) > >Thanks, Kees, this is exactly what I was looking for. Is there a name >for this type of relationship, or a common naming convention for these >two tables? E.g. "many-to-many" is a common name for a intermediary >table that joins two tables. Is there also a common name for table 2 >is your example? Hm, I'm not aware of common naming conventions for these tables. You could call t1 Questions, and t2 Questions_Adjacency, or Questions_Hierarchy. -- ( Kees ) c[_] A closed mouth gathers no feet. (#153)

On Fri, 09 Nov 2007 18:05:27 -0000, lawpoop@gmail.com wrote: >On Nov 8, 5:49 pm, Kees Nuyt <k.n...@nospam.demon.nl> wrote: >> >> Theoretically, for purists, this schema is not normalized. >> >> Think of it as this: >> A table is a common name for a relation. >> The table you have here represents two different and >> incompatible relations: >> - the relation between an id and a question, >> - the relation between an id and its parent. >> >> Correcting this would give: >> t1 >> id | question >> ------------------- >> 1 | "Yes or no?" >> 2 | "Orange or Yellow?" >> 3 | "Sugar or cream?" >> >> t2 >> id | parent_id >> -------------- >> 2 | 1 >> 3 | 3 >> > >Another question: > >With the table structure described above, how would you delete an >entire tree of questions? > >If you delete a root question in the questions table, a delete can >cascade to the relationships table, deleting one row. But then, the >delete from the relationships table cannot cascade back to the >question table -- leaving the first node with no record of a parent >question, and it thereby becomes a root question. Am I right? I would have to try to confirm that, but I'm lazy, so I guess you are right. Perhaps a trigger is useful? >With this structure, it seems that I have to write code to loop >through and delete all of these questions with individual statements. That is more or less a disadvantage of the adjacency model for hierarchies. The nested set model is simpler sometimes. http://www.developersdex.com/gurus/articles/112.asp http://dev.mysql.com/tech-resources/articles/hierarchical-data.html >With the non-normalized structure shown earlier, I can delete the >entire tree with a single delete statement. That's nice. I would say: don't look any further ;) By the way, with my original remark I didn't mean to say "completely normalized is always better". It was meant to show that mixing two different relations in one table can have its disadvantages. -- ( Kees ) c[_] A closed mouth gathers no feet. (#153)