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" — user330315
– user330315, Commented Jul 3, 2014 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 — Cjxcz Odjcayrwl
– Cjxcz Odjcayrwl, Commented Jul 3, 2014 at 10:14
What an awful database design. As far as I know you can't do what you're looking for. — Craig Ringer
– Craig Ringer, Commented Jul 3, 2014 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? — Cjxcz Odjcayrwl
– Cjxcz Odjcayrwl, Commented Jul 3, 2014 at 10:35

philipxy · Accepted Answer · 2014-07-03 11:26:02Z

2

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.)

answered Jul 3, 2014 at 11:26

philipxy

15.2k6 gold badges44 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cjxcz Odjcayrwl Over a year ago

+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 but still it's an important term to know :)

joop · Accepted Answer · 2014-07-03 11:43:36Z

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 )
        ;

Collectives™ on Stack Overflow

PostgreSQL using constants in foreign key

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related