Thread: Making references

Hi
Can you tell me what I've done wrong writting that base.
Queries doesn't work.
That's bash script code which makes tah base
#!/bin/sh
createdb med_db;
psql med_db <<!

create table DANE_LEKARZY
(
    NR_LEKARZA       int4         not null,
    NAZWISKO_LEK     CHAR(30)              not null,
    IMIE_LEK         CHAR(30)              not null,
    SPECJALIZACJA    CHAR(30)              not null,
    primary key (NR_LEKARZA)
);

create unique index DANE_LEKARZY_PK on DANE_LEKARZY (NR_LEKARZA);


create table LECZENIE
(
    NR_LECZENIA      int4         not null,
    NR_WIZYTY        int4                 ,
    ZALECENIA        text        not null,
    LEKI             text        not null,
    SKIEROWANIA      text        not null,
    ZWOLNIENIA       text        not null,
    primary key (NR_LECZENIA),
    foreign key  (NR_WIZYTY)
       references WIZYTY (NR_WIZYTY)
);

create unique index LECZENIE_PK on LECZENIE (NR_LECZENIA );

create index RELATION_98_FK on LECZENIE (NR_WIZYTY );

create table NAMIARY
(
    NR_NAMIARU       int4         not null,
    PESEL            CHAR(11)                      ,
    ULICA            CHAR(30)              not null,
    KOD              CHAR(6)               not null,
    MIASTO           CHAR(30)              not null,
    TELEFON_DOM      CHAR(30)              not null,
    TELEFON_KOM      CHAR(30)              not null,
    primary key (NR_NAMIARU),
    foreign key  (PESEL)
       references DANE_PACJENTA (PESEL)
);

create unique index NAMIARY_PK on NAMIARY (NR_NAMIARU );

create index RELATION_94_FK on NAMIARY (PESEL );




create table DANE_PACJENTA
(
    PESEL            CHAR(11)              not null,
    DAN_NR_LEKARZA   int4 ,
    NAM_NR_NAMIARU   int4                 ,
    NAZWISKO_LEK     CHAR(30)              not null,
    IMIE_LEK         CHAR(30)              not null,
    DATA_URODZENIA   DATE                  not null,
    NR_KASY_CHORYCH  CHAR(5)               not null,
    NR_LEKARZA_FK    int4               not null,
    NR_NAMIARU_FK    int4               not null,
    NR_WIZYTY_FK     int4               not null,
    primary key (PESEL),
    foreign key  (DAN_NR_LEKARZA)
       references DANE_LEKARZY (NR_LEKARZA),
    foreign key  (NAM_NR_NAMIARU)
       references NAMIARY (NR_NAMIARU)
);

create unique index DANE_PACJENTA_PK on DANE_PACJENTA (PESEL );

create index RELATION_93_FK on DANE_PACJENTA (DAN_NR_LEKARZA );

create index RELATION_94_FK2 on DANE_PACJENTA (NAM_NR_NAMIARU );

create table WIZYTY
(
    NR_WIZYTY        int4         not null,
    PESEL            CHAR(11)                      ,
    LEC_NR_LECZENIA  int4                 ,
    DATA_WIZYTY      DATE                  not null,
    ROZPOZNANIE      text        not null,
    NR_LECZENIA_FK   int4 not null,
    primary key (NR_WIZYTY),
    foreign key  (PESEL)
       references DANE_PACJENTA (PESEL),
    foreign key  (LEC_NR_LECZENIA)
       references LECZENIE (NR_LECZENIA)
);

create unique index WIZYTY_PK on WIZYTY (NR_WIZYTY );

create index RELATION_96_FK on WIZYTY (PESEL );

create index RELATION_98_FK2 on WIZYTY (LEC_NR_LECZENIA );
\q
!
THANX

--
////////////////////////\\\\\\\\\\\\\\\\\\\\\\\\\
Bart�omiej �wi�tek
WEBMASTER
www.radom.net/bswiatek
email:bswiatek@radom.net
bswiatek@kki.net.pl
\\\\\\\\\\\\\\\\\\\\\\\\/////////////////////////

On Mon, 26 Mar 2001, bswiatek wrote:

Assuming you're doing these in this order, this can't
succeed.  LECZENIE referenes WIZYTY which isn't
created yet as do some other tables, which causes
those tables not to exist later when you reference
them.  If you want to make circular trees of references
you need to use ALTER TABLE ADD CONSTRAINT to
add the foreign key constraints after the create
tables.

> Hi
> Can you tell me what I've done wrong writting that base.
> Queries doesn't work.
> That's bash script code which makes tah base