Newsgroup: it.comp.software.database
Titolo: Postgres - foreign key su una vista

Salve a tutti,
Sto disegnando un DB con Postgres (7.1) e sto incontrando delle 
difficolta' con i references...
Siccome in molte tabelle ho dei campi che fanno riferimento a
delle lookup-table, ho deciso di inserire tutti i valori di lookup
in un'unica tabella fatta nel seguente modo:
CREATE TABLE lookup_tables (
    id_table INTEGER NOT NULL,
    id_key   SERIAL,
    key      VARCHAR(20),
    descr    VARCHAR(64)
);
INSERT INTO lookup_tables
VALUES ( 0 , 0 , 'index' , 'Index for all tables' );
CREATE VIEW v_lookup_tables AS
    SELECT id_key AS id_lookup ,
           key AS name,
           descr
    FROM lookup_tables
    WHERE id_table=0 ;
per ogni lookup-table, ci sara' un valore id_table che sara' comune
a tutte le coppie key-descr appartenenti alla lookup.
Non so se sono stato chiaro ma, ad esempio, alla fine ottengo questo:
fabio=# select * from lookup_tables ;
  id_table | id_key |   key   |        descr
----------+--------+---------+----------------------
         0 |      0 | index   | Index for all tables
         0 |      1 | country | lookup for countries
         1 |      2 | IT      | Italy
         1 |      3 | FR      | France
         1 |      4 | UK      | United Kingdom
         1 |      5 | US      | United States
         0 |      6 | conn    | connection type
         6 |      7 | modem   | modem connection
         6 |      8 | isdn    | isdn connection
         6 |      9 | adsl    | adsl connection
         6 |     10 | other   | other
(11 rows)
fabio=# select * from v_lookup_tables ;
  id_lookup |  name   |        descr
-----------+---------+----------------------
          0 | index   | Index for all tables
          1 | country | lookup for countries
          6 | conn    | connection type
(3 rows)
Per semplificare la gestione ho creato inoltre un paio di 
stored-procedures per creare lookup-tables e inserirvi dei valori.
ad esempio: add_lookup_table( 'conn' , 'connection type' );
inserisce una riga e crea una view v_lookup_conn
che filtra solo le righe relative a questa lookup
fabio=# select * from v_lookup_conn ;
  id_key |  key  |      descr
--------+-------+------------------
       7 | modem | modem connection
       8 | isdn  | isdn connection
       9 | adsl  | adsl connection
      10 | other | other
(4 rows)
Fin qui tutto bene.
Se provo pero' a creare una tabella le cui colonne fanno riferimento
a certe lookup ottengo un'errore :
CREATE TABLE user_data (
  name    VARCHAR(64) NOT NULL,
  address VARCHAR(64) NOT NULL,
  city    VARCHAR(64) NOT NULL,
  id_country  INTEGER NOT NULL REFERENCES v_lookup_country (id_key)
);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY 
check(s)
ERROR:  UNIQUE constraint matching given keys for referenced table 
"v_lookup_country" not found
Ora, la colonna id_key e' un SERIAL (per la tabella lookup_tables)
quindi dovrebbe avere un constraint di unicita'.
Di che si lamenta allora Postgres ?
C'e' un modo per ovviare a questo fatto ?
Sono alle prime armi con SQL e magari mi sfugge qualche cosa...
Grazie per l'attenzione,
Fabio Lopiano.
-- 
Posted from [194.214.173.28] 
via Mailgate.ORG Server - 
http://www.Mailgate.ORG

C'e' un modo per ovviare a questo fatto ?
Io generalmente creo prima tutte le tabelle definendo PK, UNIQUE, INDEX,
CHECK, ma solo successivamente metto i vincoli d'integrità con ALTER TABLE.
Questo perchè se non crei le tabelle nella giusta sequenza tira fuori gli
errori dato che non trova i references.
Evito anche di usare il serial, più che altro perchè almeno posso decidere
io come nominare la sequence.
Ciao.
 
Faser
--------------------
FABIO SERRA
*Questo testo deve essere valutato secondo il senso della RFC-3*
Per rispondere in e-mail sostituire "invalid" con "faser"
http://www.cfmentor.com
--------------------

  
La view era gia' stata creata (in effetti l'errore riguarda la presunta 
non unicita' della colonna referenziata).
Per il momento ho cambiato il reference con la colonna della tabella
principale (lookup_tables.id_key) ma spero di trovare una soluzione 
migliorein futuro.
Per creare tabelle e viste uso degli script sql (uno per ogni tabella o 
vista), costruisco quindi (tramite uno shell script) un Makefile che
eseguira' gli script necessari nell' ordine giusto (genero le dipendenze
nel Makefile in base ai REFERENCES).
Evito invece di fare modifiche strutturali a mano per non perderne una
storia (gli script invece sono gestiti tramite CVS)
Per cancellare tutto e ricostruire tutto lo schema da capo mi basta
make clean
make
E' un sistema un po' rudimentale ma per il momento funziona
Quando pero' il database conterra' dei dati significativi,
temo che dovro' trovare una soluzione piu sofisticata...
(Tipo generare uno script per fare degli alter table tra due
  versioni differenti della stessa table)
Esistono dei Tool che facilitano queste operazioni di gestione ?
Fabio



 Non lo so e mi interesserebbe. Io avevo usato un pň ERWin, ma dato che non
supporta postgres alla fine mi toccava fare a mano e quindi per l'ho
mollato.
