From: | John Tregea <john(at)debraneys(dot)com> |
---|---|
To: | Aaron Bono <postgresql(at)aranya(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Joining a result set from four (4) tables |
Date: | 2006-08-01 00:12:19 |
Message-ID: | 44CE9C63.7010409@debraneys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi aaron,
Here are the 'create table' statements. I have indicated what are the
primary and foreign keys with trailing comments.
Thanks
John
Aaron Bono wrote:
> Can you include the table create statements with primary and foreign
> keys? That would help a lot.
CREATE TABLE resources
(
serial_id numeric NOT NULL, -- << Primary Key
related_id numeric, -- << Foreign Key
host_id int4,
created timestamptz DEFAULT now(),
modified timestamptz,
valid_from timestamp,
valid_to timestamp,
schema_name varchar(32),
grid_loc varchar(32),
name varchar(32),
status varchar(16),
description text,
comments text,
sort_order int2,
user_id int4 DEFAULT 0,
located text,
classification varchar(32),
sequence_id int4,
)
CREATE TABLE actions
(
serial_id numeric NOT NULL, -- primary key
related_id numeric, -- foreign key on resources.serial_id
host_id int4,
created timestamptz DEFAULT now(),
modified timestamptz,
valid_from timestamp,
valid_to timestamp,
name varchar(32),
status varchar(16) DEFAULT 'Active'::character varying,
description text,
comments text,
sort_order int2 DEFAULT 0,
user_id int4 DEFAULT 0, -- User_ID of the creator
located text,
classification varchar(32),
sequence_id int4,
in_box varchar(32),
display_group varchar(2),
)
CREATE TABLE policies
(
serial_id numeric NOT NULL, -- primary key
related_id numeric, -- foreign key on actions.serial_id
resource_id numeric, -- foreign key on resources.serial_id
owner_id numeric,
authority_id int4,
created timestamptz DEFAULT now(),
modified timestamptz,
valid_from timestamp,
valid_to timestamp,
status varchar(16) DEFAULT 'Active'::character varying,
description text,
comments text,
classification varchar(32),
user_id int4,
sequence_id int4,
inheritance text,
)
CREATE TABLE permissions
(
serial_id numeric NOT NULL, -- primary key
related_id numeric, -- foreign key on policies.serial_id
user_id int4, -- foreign key on users.serial_id
owner_id int4,
authority_id int4,
resource_id int4,
created timestamptz DEFAULT now(),
modified timestamptz,
valid_from timestamp,
valid_to timestamp,
name varchar(32),
acronym varchar(6),
status varchar(16) DEFAULT 'Active'::character varying,
inheritance text,
description text,
comments text,
sort_order int2,
user_id int4 DEFAULT 0,
located text,
classification varchar(32),
sequence_id int4,
)
CREATE TABLE users
(
serial_id numeric NOT NULL, -- primary key
created timestamptz DEFAULT now(),
modified timestamptz,
valid_from timestamp,
valid_to timestamp,
name varchar(64) NOT NULL,
acronym varchar(6),
status varchar(16),
inheritance text,
description text NOT NULL,
comments text NOT NULL,
sort_order int2 NOT NULL,
clearance varchar(32) NOT NULL,
administrator bool DEFAULT false,
user_id int4 DEFAULT 0,
next_serial_id int4 DEFAULT 1,
classification varchar(32),
)
From | Date | Subject | |
---|---|---|---|
Next Message | George Pavlov | 2006-08-01 16:05:34 | finding unused indexes? |
Previous Message | Aaron Bono | 2006-07-31 19:34:49 | Re: primary keys as TEXT |