use null or 0 in foreign key column, to mean "no value"?

Поиск
Список
Период
Сортировка
От Robert Nikander
Тема use null or 0 in foreign key column, to mean "no value"?
Дата
Msg-id 3721E9B9-AE49-4C19-BFE2-8578671870BD@gmail.com
обсуждение исходный текст
Ответы Re: use null or 0 in foreign key column, to mean "no value"?  (Jan de Visser <jan@de-visser.net>)
Re: use null or 0 in foreign key column, to mean "no value"?  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: use null or 0 in foreign key column, to mean "no value"?  (John McKown <john.archie.mckown@gmail.com>)
Re: use null or 0 in foreign key column, to mean "no value"?  (Alban Hertroys <haramrae@gmail.com>)
Список pgsql-general
Hi,

(Maybe my subject line should be: `is not distinct from` and indexes.)

In Postgres 9.4, I’ve got a table of ‘items’ that references a table ‘colors’.  Not all items have colors, so I created
anullable column in items like: 

      color_id bigint references colors

There is also an index on color_id:

      create index on items (color_id);

I thought this was the right way to do it, but now I’m not so sure... In application code, prepared statements want to
say:`select * from items where color_id = ?` and that `?` might be a int or null, so that doesn’t work.  I used `is not
distinctfrom` instead of =, which has the right meaning, but now I notice it doesn’t use the index for queries that
replace`=` with `is not distinct from`, and queries run much slower.  Using `explain` confirms: it’s doing sequential
scanswhere `=` was using index. 

So… is this bad DB design to use null to mean that an item has no color? Should I instead put a special row in
`colors`,maybe with id = 0, to represent the “no color” value?  Or is there some way to make an index work with nulls
and`is not distinct from`?   

thank you,
Rob





В списке pgsql-general по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: Question about CONSTRAINT TRIGGER
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: How to convert a text variable into a timestamp in postgreSQL?