Thread: check on foreign table?

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

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.

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?

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

thanks.
Esmin Gracić
NGO Flores, Sjenica, Serbia

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
valuein 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
youcan'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.
Tableslike 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
thevalue you were looking for in your main table. Your queries are simpler to write and to parse by the database. Also,
byjust looking at a record from the main table, it's immediately obvious what you're looking at - no need to trace back
whatvalues those ID's belong to. 
The lookup tables are still necessary, but only used to maintain integrity when inserting new values or updating
existingones.  

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!