Re: Moving to postgresql and some ignorant questions

On 8/14/07, Alban Hertroys <alban(at)magproductions(dot)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.