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>
