Re: Moving to postgresql and some ignorant questions - Mailing list pgsql-general

> You're confusing CHECK constraints and FOREIGN KEY constraints. They're
> different things ;)
>
> CHECK constraints verify that data in a certain column matches a certain
> condition. I'm not sure they can reference columns in other tables,
> unless you wrap those checks in stored procedures maybe...
>
> For example:
> CREATE TABLE test (
>         age int NOT NULL CHECK (age > 0)
> );
>
> Next to that, you can define DOMAINs - basically your own customized
> data types that can follow _your_ rules. Admittedly I have never done
> that yet, but it's supposed to be one of the key features of the
> relational model (I've seen claims that you're actually not supposed to
> use the base types, but define domains for all your data types).
>
> *And* you can define compound foreign key constraints,
> for example:
>
> CREATE TABLE employee (
>         employee_id serial NOT NULL,
>         company_id integer NOT NULL,
>         name text NOT NULL,
>         CONSTRAINT employee_pk
>                 PRIMARY KEY (employee_id, company_id)
> );
>
> CREATE TABLE division (
>         employee_id integer NOT NULL,
>         company_id integer NOT NULL,
>         name text NOT NULL,
>         CONSTRAINT division_fk
>                 FOREIGN KEY (employee_id, company_id)
>                         REFERENCES employee
>                         ON DELETE SET NULL
>                         ON UPDATE CASCADE
> );
>
>
> Also a nice trick, when performing DDL statements (CREATE TABLE and
> friends), you can wrap them in a transaction and commit (or rollback) if
> you like the end result (or not). I believe the only exception to that
> rule is CREATE DATABASE.



Thank you for this detailed explanation Alban. But I want to include
FK constraints on a table2 on a column in the referenced table1 where
column values are not unique.

I just want row data to be consistent for the same ID. Yes, this is
repetitive and enough to rile DB purists, but it has its uses (for
performance in certain reporting queries).

Related Questions:

1. Should I explore views for this? I am very skeptical about them
coming from MySQL as the performance of MySQL views is horrendous.
Besides, if they are updated everytime, there's little use for a view
in the first place, I may as well simply query the table -- or is this
wrong? The UPDATE only locks and commits to the table, and then the
view gets auto updated?

2. Or, I could do this with triggers, and now I realize also with
"rules" (CREATE RULE). Which are faster, rules or triggers, are they
similar in speed? Basically I want the rule/trigger to cascade the
update to table1.col1 and table1.col2 to similar columns in table2.

I will surely be exploring views, and reading more of this:
http://www.postgresql.org/docs/8.2/interactive/rules-views.html , but
I just wanted to know what the usual thoughts on this are. Are views
updated as soon as its underlying table(s) are updated? Can I control
the duration or timing of their update? I searched for "materialized
views", after having seen that word on the performance list, but most
of the search results and the discussions on that forum are beyond my
comprehension!!

Would appreciate any thoughts on performance of views. PGSQL seems to
treat views just like tables, so I wonder if there's any performance
gain!