Re: Joining a result set from four (4) tables

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