Re: self referencing table structure and constraints

On Sep 23, 2004, at 6:36 PM, Stephan Szabo wrote:

>
> On Thu, 23 Sep 2004, Matthew Hixson wrote:
>
>> I have a categories table that contains a FK to another category in
>> the
>> same table, creating a hierarchy. At the very top is this row:
>>
>> category_id | name | description | parent_id
>> -------------+------+-------------------------+-----------
>> 1 | ROOT | The top level category. | 0
>
>>
>> There is no record with category_id 0 because ROOT is at the top of
>> the
>> tree. I'd like to set up a constraint on this table so that every
>> category has to have a parent_id and it would be impossible to delete
>> a
>> category if it had subcategories. The problem is that this root
>> category violates that constraint. Is there a way to setup the
>> constraint so that it constrains every record except for forcing the
>> root category to point at a real parent category?
>
> Well, to simply have the root category not error, you could use NULL
> for
> the parent_id if you're using a foreign key.

Okay, now I just feel silly. For some reason I was thinking that the
parent id couldn't be NULL either.
Thanks, this is exactly what I needed.
-M@