Re: check on foreign table?

On 26 Jan 2011, at 19:11, Esmin Gracic wrote:

> I'm migrating mssql database to pgsql, and have a bunch of small simple ref. tables.
>
> I want to put all ref tables into one table. (denormalize kinda...)

What's wrong with the ref tables? They're small, they will result in fast lookups.

> MegaRefTable:
> id | value | refType
>
> variant a)
> id is primary key and foreign keys from main tables references to id. ids are resequnced in the process.
> Is it possible to add check option to foreign key to look at refType type column for particular value.

You can't put a check constraint on a foreign table. You could code some trigger-functions and triggers that check for this, but really...

Another possibility is to use views for each "section" of the megareftable. You won't get real integrity that way.

> variant b)
> id and refType are primary key. id values are kept. but underlaying tables must have additional column with constant value in odred to satisty composite foreign key?

Realise that you will have to add the refType column to your referring table as well as to your referenced tables, or you can't define your foreign keys.

> variant c)
> any other ideas? I'm still in learning process of pg, so I might be overlooking some simpler solution...

If your lookup tables are really constant, then you could take a look at enumerated types: http://www.postgresql.org/docs/9.0/interactive/datatype-enum.html

Something I'd change in that design like, immediately, is to throw out the surrogate keys in those lookup tables. Tables like these are a natural fit for natural keys (pun not intended).

The benefit of natural keys is that you don't need to join with your lookup tables at query-time, as you already have the value you were looking for in your main table. Your queries are simpler to write and to parse by the database. Also, by just looking at a record from the main table, it's immediately obvious what you're looking at - no need to trace back what values those ID's belong to.
The lookup tables are still necessary, but only used to maintain integrity when inserting new values or updating existing ones.

The drawback is a slightly larger footprint of your main table.
In many cases natural keys are a performance gain though.

Using enums would work very similar to this approach as well.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4d40836511732533417067!