PostgreSQL using constants in foreign key

Question

The syntax for Foreign Key, as described in official documentation, includes set of column from table A referencing set of column from table B.

Is it possible to use Constant in foreign key declaration?

The problem is, I have table DICTIONARIES which contains all dictionary values, discriminated by TYPE. I know it's not good design, but unfortunatelly, I have not much to say, as the DB admin wants to 'minimize the number of tables'...

Is it possible to achieve something like that:

CREATE TABLE PERSON (
  id integer primary key,
  country_id integer,
  ...
  FOREIGN KEY ('COUNTRY', country_id) REFERENCES DICTIONARIES(TYPE, ID)
)

It would effectively solve the issue. I'm aware I can add column to the table person, which will has only one possible value 'COUNTRY', or I could write a trigger, but I'd prefer to avoid that to keep design cleaner.

"*as the DB admin wants to 'minimize the number of tables*" - what a stupid DBA. Maybe you can point him to the fact that this is a well known design **anti** pattern called "one-true-lookup-table" — , Jul 03 '14 at 10:08
while it would give me satisfaction and a great opportunity to look for a new work ;) there are many application that already use that one-true-lookup-table so I need to adapt to the existing architecture — Danubian Sailor, Jul 03 '14 at 10:14
What an awful database design. As far as I know you can't do what you're looking for. — Craig Ringer, Jul 03 '14 at 10:33
Actually, such functionality should be trivial to implement. There's already a check based on the value from the table, it could be easily swapped with constant, or it couldn't because of index issues? Is there a structural (non trigger-based) alternative not requiring the extra column to be added? — Danubian Sailor, Jul 03 '14 at 10:35

score 1 · Accepted Answer · answered Jul 03 '14 at 11:26

A foreign key constraint is from one table's columns to another's columns, so, no.

Of course the database should have a table COUNTRY(country_id). Commenters have pointed out that your admin is imposing an anti-pattern.

Good, you are aware that you can define a column and set it to the value you want and make the foreign key on that. That is an idiom used for avoiding triggers in constraining some subtyping schemes.

You may be able to compute a 'COUNTRY' column depending on your DBMS, at least.

Your question is essentially this one, see the end of the question & the comments & answers.

(Lots of functionality would be trivial to implement. Perhaps the difficulty (besides ignorance of consumers) is that arbitrary constraints become quickly expensive computationally. That might just get vendors aggravation. Also, optimization in SQL is impeded by its differences from the relatonal model.)

+1 because I've learned about COMPUTED columns from your answer. Not having contact with MSSQL I wasn't aware of that concept. Unfortunately, [PostgreSQL doesn't have them](http://stackoverflow.com/questions/8250389/computed-calculated-columns-in-postgresql) but still it's an important term to know :) — Danubian Sailor, Jul 03 '14 at 11:31

score 1 · Answer 2 · answered Jul 03 '14 at 11:43

For Posrgres not having computed (or constant) columns, you can force them to a fixed value column, using DEFAULT plus (maybe) a check. This may be ugly, but it works:

CREATE TABLE dictionaries
  ( id integer primary key
  , typename varchar NOT NULL CHECK ( typename IN ('person' ,'animal' ,'plant' ))
  , content varchar NOT NULL
  -- ...
  , UNIQUE (typename, id)
  , UNIQUE (typename, content)
  );

CREATE TABLE person
  ( id integer primary key
  , typename varchar NOT NULL DEFAULT 'person' CHECK( typename IN ('person' ))
  , species_id integer
  -- ...
  , FOREIGN KEY (typename, species_id) REFERENCES dictionaries(typename, id) -- DEFERRABLE INITIALLY DEFERRED
);

INSERT INTO dictionaries( id, typename, content) VALUES
 ( 1 , 'person' , 'Bob')
,( 2 , 'person' , 'Alice')
,( 11 , 'animal' , 'monkey')
,( 12 , 'animal' , 'cat')
,( 13 , 'animal' , 'dog')
,( 21 , 'plant' , 'cabbage')
        ;
SELECT * FROM dictionaries;

        -- this should succeed
INSERT INTO person( id, species_id) VALUES
 ( 1,1 )
,( 2,2 )
        ;
        -- this should fail
INSERT INTO person( id, species_id) VALUES
 ( 3,11 )
,( 4,12 )
        ;

PostgreSQL using constants in foreign key

2 Answers2