Thread: How to set up a simple relational database

Can someone demonstrate for me how to set up a simple relational database
using the psql interactive prompt and two tables.

For example: table 1 can be staff member and table 2 can be salary package
with a one-to-many relationship (there would be a foreign key in table Staff
Member which determined what salary package a staff member had.

Can someone show me the commands to create the two tables, establish the
relationship between them and query the two tables so that it lists the
staff member and his/her wages (using the relationship)?

It seems hard to find examples anywhere.

James

create table staff (name text, packageid int);
create table package (id int, amount text);
SELECT * from staff, package where staff.packageid=package.id;

James Macken wrote:
> 
> Can someone demonstrate for me how to set up a simple relational database
> using the psql interactive prompt and two tables.
> 
> For example: table 1 can be staff member and table 2 can be salary package
> with a one-to-many relationship (there would be a foreign key in table Staff
> Member which determined what salary package a staff member had.
> 
> Can someone show me the commands to create the two tables, establish the
> relationship between them and query the two tables so that it lists the
> staff member and his/her wages (using the relationship)?
> 
> It seems hard to find examples anywhere.
> 
> James
> 
> ************

> create table staff (name text, packageid int);
> create table package (id int, amount text);
> SELECT * from staff, package where staff.packageid=package.id;


But that omits the "establish the relationship part", by which I
suspect James means "ensure foreign key integrity".
I must I admit I would like to see a simple exposition of that (using
the refint package, presumably) for this example - or is it not worth
it if 7.0 is bringing native foreign key integrity checks?


> James Macken wrote:
> >
> > Can someone demonstrate for me how to set up a simple relational
database
> > using the psql interactive prompt and two tables.

> > create table staff (name text, packageid int);
> > create table package (id int, amount text);
> > SELECT * from staff, package where staff.packageid=package.id;
> But that omits the "establish the relationship part", by which I
> suspect James means "ensure foreign key integrity".
> I must I admit I would like to see a simple exposition of that (using
> the refint package, presumably) for this example - or is it not worth
> it if 7.0 is bringing native foreign key integrity checks?
Yes, right.

Until Jan and his developer friends are ready, I suggest you using this
small script (called "refint") for creating reference integrity; before
using it, you should enter this: 

CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS 
'/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" 
LANCOMPILER 'PL/pgSQL';
CREATE FUNCTION "check_primary_key" ( ) RETURNS opaque AS 
'/usr/doc/postgresql-6.5.2/contrib/spi/refint.so' LANGUAGE 'C';
CREATE FUNCTION "check_foreign_key" ( ) RETURNS opaque AS 
'/usr/doc/postgresql-6.5.2/contrib/spi/refint.so' LANGUAGE 'C';

(Please check for the right folders.)

Then create the script "refint":

#/bin/bash
# Reference integrity workaround for v6.5.3, will be obsolete soon
# Usage: refint primary_table number primary_key foreign_table \
#               foreign_key referencing_mode > output_file
# Remark: "number" also can be "" if there is only one foreign table
# for a primary one. "referencing_mode" can be "cascade", "restrict" or
# "setnull".

echo -n CREATE TRIGGER \"$1_ri1$2\" BEFORE DELETE ON \"$1\" FOR EACH ROW
echo -n " EXECUTE PROCEDURE check_foreign_key ('1', '$6', '$3',"
echo " '$4', '$5');"

echo -n CREATE FUNCTION \"$1_ri2$2\" "(" ")" RETURNS opaque AS
echo -n " 'begin if new.$3 != old.$3 then update $4 set $5 = new.$3"
echo " where $5 = old.$3; end if; return new; end;' LANGUAGE 'plpgsql';"

echo -n CREATE TRIGGER \"$1_ri2$2\" AFTER UPDATE ON \"$1\" FOR EACH ROW
echo " EXECUTE PROCEDURE $1_ri2$2();"

echo -n CREATE TRIGGER \"$1_ri3$2\" BEFORE INSERT OR UPDATE ON \"$4\"
echo -n " FOR EACH ROW EXECUTE PROCEDURE check_primary_key"
echo " ('$5', '$1', '$3');"

---------------------------- end of script ----------------------------

After creating this, you can create the reference integrity check with

refint staff 1 packageid package id restrict > refint.sql

By the end, start "psql" with your database and load the refint.sql file:

\i refint.sql

I hope this will help you.

Regards,
Zoltan