---

Hej alle,

I forbindelse med et projekt har vi udarbejdet en SQL-database, som skal
implementeres i PostgreSQL. Det viser sig dog, at Postgre ikke understøtter
subselects i CHECK-constraints, hvilke vi har en del af.

Som eksempel er nedenstående oprettelse af to tabeller (vi laver iøvrigt en
fodbolddatabase). Den sidste linie skaber problemet...

Spørgsmålet er så: findes der et alternativ til CHECK/subselect, som virker
i PostgreSQL? REFERENCES kan naturligvis bruges, men den skaber - som
bekendt - en FOREIGN KEY, og det er ikke helt optimalt i vores
minimalistiske databasedesign...

CREATE TABLE Teams(
   name VARCHAR(30) PRIMARY KEY,
   homeField VARCHAR(30),
   color VARCHAR(15),
   town VARCHAR(10)
   );

CREATE TABLE Matches(
   day DATE PRIMARY KEY,
   round INT,
   homeTeam VARCHAR(30) REFERENCES Teams(name),
   awayTeam VARCHAR(30),
   CHECK (awayTeam IN (SELECT name FROM Teams))
   );

På forhånd tak!

/Kenneth

---

On Wed, 02 Jul 2003 21:51:43 +0200, Kenneth Brun Nielsen wrote:

> Spørgsmålet er så: findes der et alternativ til CHECK/subselect, som virker
> i PostgreSQL? REFERENCES kan naturligvis bruges, men den skaber - som
> bekendt - en FOREIGN KEY, og det er ikke helt optimalt i vores
> minimalistiske databasedesign...

Hvorfor er en fremmednøgle dog et problem? I bruger i øvrigt allerede én i
jeres "homeTeam" attribut.

/Troels

---

"Troels Arvin" <troels@arvin.dk> skrev i en meddelelse
news:pan.2003.07.03.10.54.37.283245@arvin.dk...
> On Wed, 02 Jul 2003 21:51:43 +0200, Kenneth Brun Nielsen wrote:
>
> > Spørgsmålet er så: findes der et alternativ til CHECK/subselect, som
virker
> > i PostgreSQL? REFERENCES kan naturligvis bruges, men den skaber - som
> > bekendt - en FOREIGN KEY, og det er ikke helt optimalt i vores
> > minimalistiske databasedesign...
>
> Hvorfor er en fremmednøgle dog et problem? I bruger i øvrigt allerede én i
> jeres "homeTeam" attribut.
>

Tjaae, nu ved jeg ikke meget om processering af queries, men jeg kunne da
forestille mig, at afviklingen ville blive langsommere - eller om ikke
andet, så mere ressourcekrævende - hvis flere (unødvendige) nøgler bliver
implementeret.

Hovedårsagen til at vi vil undgå fremmednøgler, er dog af teoretisk
karakter: Vi vil ganske enkelt kun benytte fremmednøgler, hvor det er
nødvendigt!

Jeg beder til, at svar til tråden ikke omhandler processering, hastighed og
deslige. Jeg er kun interesseret i teorien, og gentager mit oprindelige
spørgsmål:
Findes der et alternativ til CHECK/subselect, som virker i PostgreSQL og
ikke inkluderer brug af REFERENCES?

/Kenneth

---

On Thu, 03 Jul 2003 15:29:30 +0200, Kenneth Brun Nielsen wrote:

> Tjaae, nu ved jeg ikke meget om processering af queries, men jeg kunne da
> forestille mig, at afviklingen ville blive langsommere - eller om ikke
> andet, så mere ressourcekrævende - hvis flere (unødvendige) nøgler bliver
> implementeret.

Det er altså småting, vi taler om her. Og kun ved updates/inserts.
Tommelfingerregel: Tænk i god implementation/kodning før optimering.
Optimér, hvis det viser sig at der er performanceproblemer, og optimér da
dér, hvor det batter.

> Vi vil ganske enkelt kun benytte fremmednøgler, hvor det er
> nødvendigt!

Og det er netop nødvendigt dér, hvor du efterlyser dem...

> Findes der et alternativ til CHECK/subselect, som virker i PostgreSQL og
> ikke inkluderer brug af REFERENCES?

Så vidt jeg ved bliver svaret da "nej". Hvorfor mener du i øvrigt at
CHECK-constraints er mindre krævende end fremmednøgle-constraints?

/Troels

---

"Troels Arvin" <troels@arvin.dk> skrev i en meddelelse
news:pan.2003.07.03.13.42.35.558009@arvin.dk...
> On Thu, 03 Jul 2003 15:29:30 +0200, Kenneth Brun Nielsen wrote:
>
> > Tjaae, nu ved jeg ikke meget om processering af queries, men jeg kunne
da
> > forestille mig, at afviklingen ville blive langsommere - eller om ikke
> > andet, så mere ressourcekrævende - hvis flere (unødvendige) nøgler
bliver
> > implementeret.
>
> Det er altså småting, vi taler om her. Og kun ved updates/inserts.
> Tommelfingerregel: Tænk i god implementation/kodning før optimering.
> Optimér, hvis det viser sig at der er performanceproblemer, og optimér da
> dér, hvor det batter.
>
Det er muligt, at det er småting, men nu er det kursus, som
databaseimplementeringen en del af

> > Vi vil ganske enkelt kun benytte fremmednøgler, hvor det er
> > nødvendigt!
>
> Og det er netop nødvendigt dér, hvor du efterlyser dem...
>
Det er da ikke nødvendig mht. nøgler. En kamp (match) i vores database, er
entydig bestemt udfra dato og hjemmehold (et hold spiller næppe flere kampe
på een dag). Udeholdet (awayTeam) er underordnet. At tage udeholdet med som
key vil være overflødigt.

> > Findes der et alternativ til CHECK/subselect, som virker i PostgreSQL og
> > ikke inkluderer brug af REFERENCES?
>
> Så vidt jeg ved bliver svaret da "nej".

OK- tak for svaret!

Hvorfor mener du i øvrigt at
> CHECK-constraints er mindre krævende end fremmednøgle-constraints?
>
Hmmm, det tror jeg ikke jeg har udtalt mig om. Der er vist flere sider af
den sag, men fra min side af ser det ud som om CHECK-constraints kun er
aktuelle i forb. med oprettelse af data/rækker, hvorimod nøgler bliver brugt
i forb. med databaseopslag. Det er muligt, at jeg tager fejl, men under alle
omstændigheder er det irrelevant i det projekt, som jeg udarbejder. Vi har
udarbejdet en såkaldt E/R-model (ved ikke om det hedder det på dansk), og
derudfra implementerer vi databasen v.h.a. SQL. I E/R-modellen har vi
adskillige "weak entity sets" (WES), som forårsager brug af fremmednøgler -
og derudover en række "referential integrities"(RI), som forårsager brug af
CHECK-constraints. RI er en forudsætning for WES, hvorimod det omvendte ikke
er tilfældet. Således er WES en ægte delmængde af RI - og dermed er WES et
strengere krav end RI (nu taler vi billedsprog). Mit postulat er derfor, at
REFERENCES i nogen sammenhænge vil være "uøkonomisk" at bruge som
stedfortræder for CHECK...

/Kenneth

---

On Wed, 02 Jul 2003 21:51:43 +0200, Kenneth Brun Nielsen wrote:

> Det viser sig dog, at Postgre ikke understøtter
> subselects i CHECK-constraints, hvilke vi har en del af.
[...]
>    awayTeam VARCHAR(30),
>    CHECK (awayTeam IN (SELECT name FROM Teams))
[...]

Selvom jeg fortsat synes, at det er lidt skørt at I ikke bare benytter
jer af et REFERENCES-udtryk i stedet for et CHECK-udtryk, så er
her noget, der burde kunne fungere. Det er et hack, der gør at man narrer
PostgreSQL til faktisk at udføre et subselct alligevel.

/Troels

-- ==============================================================
-- For this to work, you must have added plpgsql support
-- to the database. See here, if you don't know how to do that:
-- http://www.postgresql.org/docs/7.3/static/xplang-install.html

-- (uncomment these to clean up:)
-- drop table Matches;
-- drop table Teams;
-- drop function result_not_empty (varchar);

create function result_not_empty (varchar) returns boolean AS '
declare
exec_rec record;
begin
for exec_rec in execute $1 loop
return ''t'';
end loop;
return ''f'';
end;
' language 'plpgsql';

CREATE TABLE Teams (
"name" VARCHAR(30) PRIMARY KEY,
homeField VARCHAR(30),
"color" VARCHAR(15),
"town" VARCHAR(10)
);

drop table matches;
CREATE TABLE Matches (
"day" DATE PRIMARY KEY,
"round" INT,
homeTeam VARCHAR(30) REFERENCES Teams(name),
awayTeam VARCHAR(30) check (
result_not_empty('
select name from Teams where name=''' || awayTeam || '''
')
)
);

-- example data:

insert into Teams values ('holdnavn', );
insert into Teams values ('holdnavn', 'banenavn', 'gul', 'herlev');
-- should succeed:
insert into Matches values ('2003-07-11',5,'holdnavn','holdnavn');
-- should FAIL:
insert into Matches values ('2003-07-30',5,'holdnavn','whatever');
-- ==============================================================