Re: Joining a result set from four (4) tables - Mailing list 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
DEFAULTnow(), 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
DEFAULT0, 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,
createdtimestamptz DEFAULT now(), modified timestamptz, valid_from timestamp, valid_to timestamp, name varchar(32),
statusvarchar(16) DEFAULT 'Active'::character varying, description text, comments text, sort_order int2 DEFAULT 0,
user_idint4 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(),
modifiedtimestamptz, valid_from timestamp, valid_to timestamp, status varchar(16) DEFAULT 'Active'::character varying,
descriptiontext, 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, --
foreignkey on users.serial_id owner_id int4, authority_id int4, resource_id int4, created timestamptz DEFAULT now(),
modifiedtimestamptz, 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
DEFAULT0, 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,
descriptiontext NOT NULL, comments text NOT NULL, sort_order int2 NOT NULL, clearance varchar(32) NOT NULL,
administratorbool DEFAULT false, user_id int4 DEFAULT 0, next_serial_id int4 DEFAULT 1, classification varchar(32),
 
)