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