Re: add foreign key constraint after table creation?

>
>
>CREATE TABLE people (
>    n_id SERIAL PRIMARY KEY,
>    n_object_id INTEGER DEFAULT NULL
>                            references objects
>                            ON UPDATE CASCADE
>                            ON DELETE SET NULL,
>    n_objects_counter INTEGER DEFAULT 0,
>    b_hidden BOOLEAN DEFAULT false,
>    dt_created TIMESTAMP DEFAULT current_timestamp,
>    dt_modified TIMESTAMP DEFAULT current_timestamp,
>    t_nickname VARCHAR(20) UNIQUE NOT NULL,
>    t_firstname VARCHAR(20) NOT NULL,
>    t_surname VARCHAR(25) NOT NULL,
>    t_mobile VARCHAR(15) UNIQUE,
>    t_email VARCHAR(30) UNIQUE,
>    b_registered BOOLEAN DEFAULT false,
>    n_email_status INT2 DEFAULT 0,
>    n_txt_status INT2 DEFAULT 0,
>    b_work_hours BOOLEAN DEFAULT false
>);
>
>CREATE TABLE objects (
>    n_id SERIAL PRIMARY KEY,
>    t_text_id VARCHAR(25) UNIQUE NOT NULL,
>    b_hidden BOOLEAN DEFAULT false,
>    dt_created TIMESTAMP DEFAULT current_timestamp,
>    dt_modified TIMESTAMP DEFAULT current_timestamp,
>    n_creator INTEGER NOT NULL
>                       references people
>                       ON UPDATE CASCADE
>                       ON DELETE CASCADE,
>    n_type INT2 NOT NULL DEFAULT 0,
>    t_name VARCHAR(30) NOT NULL,
>    t_description VARCHAR(200),
>    t_location VARCHAR(100) DEFAULT NULL,
>    t_postcode VARCHAR(10) DEFAULT NULL,
>    n_id_photo INTEGER DEFAULT NULL
>                       references photo
>                       ON UPDATE CASCADE
>                       ON DELETE SET NULL
>
>);
>...
>
>
Hi Rory,
maybe you should review your design.
I think you are recording a redundancy:
As soon as a given obect_id is recorded in the person,
you know that this person is the creator (ok,
maybe I am nor fully understanding your schema?).
If you need your design, it might help to treat the
relationship as if it was a many to many,
and putting a third table in between with just
(person_id, object_id, is_creator).

Just my 2 cents.

Cheers, Dani