Re: Index not used with IS NULL

From: "Andrei Ivanov" <andrei.ivanov@ines.ro>
>
> This is a resend, don't know if the first time it got to the
list... sorry
> if it did.
>
> Hello, sorry for barging in...
> I use a similar structure for keeping some some text pages
categorized.
>
> CREATE TABLE pages (
>   id          SERIAL NOT NULL PRIMARY KEY,
>   categ       INTEGER,
>   CONSTRAINT  categ_fk FOREIGN KEY(categ) REFERENCES
categs(id) ON DELETE CASCADE
> );
>
> All the pages that are not contained in a category are marked
by categ IS
> NULL ( this is like the files in / in a filesystem). If I use
other values
> than NULL for marking this kind of pages, then the constraint
would
> complain, but then I can't use an index to find these pages.
>
> Do you have a better solution for this ?

If some pages aren't associated with a category, shouldn't you
have three relations?

categories (
 categ PRIMARY KEY
 ...
);

pages (
 id PRIMARY KEY
 ...
);

category_pages (
 categ INTEGER NOT NULL,
 id INTEGER NOT NULL
);

Similarly, with previous posts regarding hierarchies, the model
should look like:

employees (
 employeeid PRIMARY KEY
 ...
)

employee_manager (
 employeeid INTEGER NOT NULL,
 manager INTEGER NOT NULL
)

*not*:

employees (
 employeeid PRIMARY KEY,
 manager INTEGER
);

NULLs are evil. ;-)

Mike Mascari
mascarm@mascari.com