Re: Moving to postgresql and some ignorant questions

On 8/14/07, Alban Hertroys <alban@magproductions.nl> wrote:
> *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
> );

You can also have multiple foreign keys to different tables, and to
non-primary keys, as long as they are pointing to columns with a
unique constraint on them.

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

One of my all time favorite features of pgsql.

create tablespace is also non-transactable.