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@