Обсуждение: add foreign key constraint after table creation?

I have two tables both of which have foreign key constraints on each
other. I presume I won't get into a deadlock situation... people can be
made independantly of objects, but if a person object is made this is
recorded in n_object_id.

My problem is that I cannot load my full table schema using \i as to
define peopel I need objects defined and vice versa. To get around this
I though I could define the foreign key constraint on people.n_object_id
after I had loaded everything. How do I do it? The alter table \h
command isn't very helpful!

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

);
...

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

Am Mon, 2003-05-26 um 20.21 schrieb Rory Campbell-Lange:

> My problem is that I cannot load my full table schema using \i as to
> define peopel I need objects defined and vice versa. To get around this
> I though I could define the foreign key constraint on people.n_object_id
> after I had loaded everything. How do I do it? The alter table \h
> command isn't very helpful!

I'm not sure but you could try opening a transaction (BEGIN) before the
inserts and COMMIT it at the end.
AFAIR that way it's possible to have the constraints checked at the end.

HTH
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

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