Postgresql – Foreign Key NULL vs Foreign Key to an empty string

database-designforeign keypostgresqlunique-constraint

This is a table for a catalog of medicines. Some have a pharmaceutical brand, others are generic (i.e. they will never have brand information) 

CREATE TABLE medicine (
   id serial PRIMARY KEY,
   name text NOT NULL,
   brand_id integer
   CONSTRAINT brand_fk FOREIGN KEY (brand_id) REFERENCES brand (id)       
);

CREATE TABLE brand (
  id serial PRIMARY KEY,
  name text NOT NULL
);

For storing generic medicines, e.g. abc & xyz, there are 2 options:

  1. Use NULL for the Foreign Key brand_id

    INSERT INTO medicine (name, brand_id) VALUES ('abc', NULL)
    INSERT INTO medicine (name, brand_id) VALUES ('xyz', NULL)

  2. Insert just 1 empty string in brand name and use that for all generic medicine's brand_id

    INSERT INTO brand (id, name) VALUES (1, '')
    INSERT INTO medicine (name, brand_id) VALUES ('abc', 1)
    INSERT INTO medicine (name, brand_id) VALUES ('xyz', 1)

From what I've read on StackExchange, it seems like 1 is the general way of doing this. However if I want a unique index on medicine name & brand_id, I'll have to use partial indexes (i.e. 2 indexes respectively where brand_id IS NULL & brand_id is NOT NULL).

If I go with the second approach, I can achieve it with a single index.

Is there any other advantage / disadvantage of either of these approaches. I feel the second method is unconventional and there might be some trouble that I'll discover later along the way.

P.S. I've used the example of medicines to illustrate my query but I want to understand the technical merits of the solution purely from a database standpoint i.e. without going into a discussion of which approach would be better for medicine example.

Best Answer

The answer lies in the ambiguity of your question:

However if I want a unique index on medicine name & brand_id ...

You do not actually want "a unique index". You want to enforce certain rules. A unique index is a means to an end; a tool. The question is: What exactly do you want?

Do you want that there can only be a single instance of a name with an unknown brand? Or can there be multiple instances? That depends on what "unknown" or "missing" or "empty" or NULL is supposed to mean for you.

The canonical meaning of NULL is "unknown". We simply do not know, what goes here, it could be anything, including nothing (empty ('') in string types, 0 in numeric types).

If you want to allow one generic brand, include an entry in brand, call it "generic" or "ACME" or whatever. I like to use the id value 0 for such a catch-all entry.

Then your unique index or constraint is doing what it's supposed to do: Allow only one instance of (name, brand_id) in the table medicine. But multiple entries with (name, NULL) are still possible. Set brand_id NOT NULL, too, if you don't want that.

The best solution also depends on typical queries and details of your setup. Be sure to document exactly what NULL / empty / 0 is supposed to mean and why you chose to design it like that. It may seem crystal clear at the moment you implement it, but may be confusing later.

I added another link to the answer you referenced:

Related Question