CODEWITHSUNDEEP
sqldatabase-design
davor
davor

Reputation: 969

One to zero or many association and FK usage in third table

I have two tables - 1st is place and 2nd is location. Place can have zero or many locations. Place PK is id, and location PK is two component key place_id + id.

enter image description here

In third table (ex. invoice) I could have place which doesn't have any location.

The question is: how to set FK in invoice table?

  1. I can't use only place.id as FK because I want to know location.
  2. If I use locations PK which consists of place_id + id columns - then the relationship to table location is not consistent - some place can have 0 location.

So, the main problem is that some place can have 0 locations.

Yes, I might to set surrogate PK in location table, and set place_id FK, location_id FK NULL. But, the requirement is that place and location keys are natural (based on company organizational structure). Second idea is to have one to one/many association and put in location id value 0 for place which have zero locations and hide this location in user interface. Actually, then I need to put for every place location 0 (no location).

Thanks.

Upvotes: 1

Views: 199

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

You should be able to simply "merge" these two FKs together:

enter image description here

CREATE TABLE invoice (
    id INT PRIMARY KEY,
    place_id INT NOT NULL,
    location_id INT NULL,
    FOREIGN KEY (place_id) REFERENCES place (id),
    FOREIGN KEY (place_id, location_id) REFERENCES location (place_id, id)
);

The DBMS will ignore a FK with at least one NULL:

  • The place_id is NOT NULL so the first FK will always be in force.
  • If location_id is NULL, the second FK will simply be ignored.

You can play with it in this SQL Fiddle.

(In fact, the very existence of natural key is what enables you to do this - if location had a surrogate key, you could not guarantee that the two FKs point to the same place.)

In an unlikely case your DBMS treats NULLs in a funny way so a FK with a NULL is not ignored, you can completely separate these two FKs, and use CHECK to ensure they belong to the same location:

CREATE TABLE invoice (
    id INT PRIMARY KEY,
    place_id INT NOT NULL,
    location_place_id INT NULL,
    location_id INT NULL,
    FOREIGN KEY (place_id) REFERENCES place (id),
    FOREIGN KEY (location_place_id, location_id) REFERENCES location (place_id, id),
    CHECK (place_id = location_place_id)
);

Upvotes: 2

Related Questions