Re: [SQL] [GENERAL] Schema design / joins

[silly sad]

__Orgs__
  id
  name

__Seasons__
  id
  org_id  fk(orgs.id)
  name

__Teams__
  id
  season_id  fk(seasons.id)
  name

__TeamFees__
  id
  team_id  fk(teams.id)
  *org_id<--- (?put extra fk here to avoid many joins?)


NO.

instead of it
use triggers before insert/update

CREATE FUNCTION foo() RETURNS TRIGGER AS $$
BEGIN
  SELECT org_id INTO new.org_id FROM __seasons__ WHERE id=new.season_id;
END;
$$ LANGUAGE plpgsql;

et cetera.

AND now other way lead you to the future.

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Foreign key constraint referencing a parent table

[David Weilers]

Hi,

I have a problem when inserting rows into my table tblfactuurpost that 
references a table tblfactuur which is a parent to tblclientfactuur.  
The actual row is inserted into tblfactuur.

The problem is that i cannot insert the row into tblfactuurpost (which 
references the parent table tblfactuur) because PSQL complains the 
referencing ID (2) is missing while it is actually *not* missing at all.

Thanks to anyone who can shed some light ...

I have the following table set-up:

CREATE TABLE tblfactuur (
id serial PRIMARY KEY,
number int NOT NULL,
year int NOT NULL CHECK (year>=1900 AND year<=2999),

vanaf date NOT NULL,
tot date NOT NULL CHECK (tot > vanaf),

total int -- totaalbedrag in centen
);

CREATE TABLE tblclientfactuur (
client int NOT NULL REFERENCES tblclient (id) ON DELETE RESTRICT
, PRIMARY KEY (id)
) INHERITS (tblfactuur);

CREATE TABLE tblfactuurpost (
id serial PRIMARY KEY,
factuur int NOT NULL REFERENCES tblfactuur (id) ON DELETE CASCADE 
DEFERRABLE,

omschrijving varchar(128),

btw smallint DEFAULT 19, -- standaard 19% btw
aantal decimal(8,1) NOT NULL CHECK (aantal >= 0), -- aantal
perstuk int NOT NULL CHECK (perstuk >= 0), -- bedrag per stuk in 
centen
amount int NOT NULL CHECK (amount >= 0), -- totaal bedrag in centen

position smallint CHECK (position >= 0)
);

This is the data in tblfactuur:

(db)=> select * from tblfactuur;
 id | number | year |   vanaf|tot | total
++--+++
  2 |  1 | 2010 | 2010-03-01 | 2010-04-01 | 397800
(1 row)

I try to insert: 

insert into tblfactuurpost (factuur, omschrijving, btw, aantal, perstuk, 

amount, position)
select ?, ?, ?, ?, ?, ?, count(*) from tblfactuurpost where factuur 

= ?;
parameters: (2, Gespecialiseerde 24 uur zorg ma. t/m vr., 19, 156, 1425, 

397800, 2)

Regards, David


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql