Postgres, widok i klucz obcy do niego
Grzegorz Szyszlo - 05-04-2006 00:03
Postgres, widok i klucz obcy do niego
Czesc. Mam pewien problem w postgres 7.4.7 . Czemu taki stary? Bo to
debian/stable.
Mniejsza z tym.
Wpadłem na pewien "Genialny" pomysł, aby wiele słowników o identycznej
strukturze
władować do dwóch tabel. Po władowaniu, sumarycznie mam ok. 100 wierszy
danych.
Na to nakładam widoki, symulujące poprzednio utworzone słowniki.
No i mam problem. Wg. definicji, tabele odwołują się kluczem obcym
do takich słowników. No i klops. Nie bardzo wiem jak to obejść,
bo w takim słowniku klucz główny jest kluczem złożonym z 2ch kolumn.
Poniżej jest konstrukcja bazy, a jeszcze niżej jak to wygląda
po władowaniu do shella psql:
============konstrukcja bazy
-- Slownik uniwersalny - nazwy slownikow
CREATE TABLE SlownikUn (
id_SlownikUn INT NOT NULL PRIMARY KEY, -- id typu
NazwaUn TEXT NOT NULL UNIQUE, -- nazwa typu
Opis TEXT -- rozszerzony
komentarz opisujący słownik
);
-- Slownik uniwersalny - wlasciwy
CREATE TABLE SlownikU (
id_SlownikU INT NOT NULL, -- id pozycji slownika
id_SlownikUn INT NOT NULL REFERENCES SlownikUn, -- odwolanie do
nazwy slownika
Waga INT, -- waga pozycji, mniej
- wazniejsza
NazwaSlP TEXT NOT NULL, -- nazwa pozycji w slowniku
Widoczny BOOLEAN DEFAULT TRUE, -- wskaznik widocznosci
pozycji
PRIMARY KEY (id_SlownikU,id_SlownikUn)
);
-- zeby nie bylo powtorek
CREATE UNIQUE INDEX SlownikU_indx1 ON SlownikU (id_SlownikU,
id_SlownikUn) WHERE Widoczny;
CREATE UNIQUE INDEX SlownikU_indx2 ON SlownikU (id_SlownikU,
id_SlownikUn,NazwaSlP) WHERE Widoczny;
-- jeden z widokow definiujacy wirtualny slownik
CREATE OR REPLACE VIEW ZrodloN (ID_ZrodloN,Waga,Widoczny,Nazwa)
AS SELECT ID_SlownikU,Waga,Widoczny,NazwaSlP FROM SlownikU WHERE
ID_SlownikUn=14;
-- definicja slownika wirtualnego
INSERT INTO SlownikUn (id_SlownikUn,NazwaUn,Opis)
VALUES(14,'ZrodloN','Nazwa źródła danych zasilających bazę');
-- Wartości słownika 14,ZrodloN(azwy zrodel)
COPY SlownikU (id_SlownikUn,id_SlownikU,Waga,NazwaSlP) FROM stdin
DELIMITER ',';
14,11,110,SystemProdukcyjny
14,12,120,Zaplecze
14,13,130,Arkusze
\.
-- Definicja zrodel danych
CREATE TABLE Zrodlo (
ID_Zrodlo SERIAL PRIMARY KEY, -- wewnetrzny identyfikator zrodla
NazwaW TEXT NOT NULL UNIQUE CHECK (NazwaW<>''), -- Nazwa źródła
IDENT_Org TEXT NOT NULL UNIQUE CHECK (IDENT_Org<>''), -- Identyfikator
źródła dla procedury importu
ID_ZrodloN INT NOT NULL REFERENCES ZrodloN, -- wskazanie na nazwę
ogólną źródła danych (klasa źródła)
Waga INT NOT NULL DEFAULT 0, -- do sortowania
Widoczny BOOLEAN NOT NULL DEFAULT TRUE -- czy to źródło ma być
widoczne (nie skasowane)
);
================= dialog z psql
Linux> psql tymczas
tymczas=> -- Slownik uniwersalny - nazwy slownikow
tymczas=> CREATE TABLE SlownikUn (
tymczas(> id_SlownikUn INT NOT NULL PRIMARY KEY, -- id typu
tymczas(> NazwaUn TEXT NOT NULL UNIQUE, -- nazwa typu
tymczas(> Opis TEXT -- rozszerzony
komentarz opisujący słownik
tymczas(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"slownikun_pkey" for table "slownikun"
NOTICE: CREATE TABLE / UNIQUE will create implicit index
"slownikun_nazwaun_key" for table "slownikun"
CREATE TABLE
tymczas=> -- Slownik uniwersalny - wlasciwy
tymczas=> CREATE TABLE SlownikU (
tymczas(> id_SlownikU INT NOT NULL, -- id pozycji
slownika
tymczas(> id_SlownikUn INT NOT NULL REFERENCES SlownikUn, --
odwolanie do nazwy slownika
tymczas(> Waga INT, -- waga
pozycji, mniej - wazniejsza
tymczas(> NazwaSlP TEXT NOT NULL, -- nazwa
pozycji w slowniku
tymczas(> Widoczny BOOLEAN DEFAULT TRUE, -- wskaznik
widocznosci pozycji
tymczas(> PRIMARY KEY (id_SlownikU,id_SlownikUn)
tymczas(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"slowniku_pkey" for table "slowniku"
CREATE TABLE
tymczas=> -- zeby nie bylo powtorek
tymczas=> CREATE UNIQUE INDEX SlownikU_indx1 ON SlownikU (id_SlownikU,
id_SlownikUn) WHERE Widoczny;
CREATE INDEX
tymczas=> CREATE UNIQUE INDEX SlownikU_indx2 ON SlownikU (id_SlownikU,
id_SlownikUn,NazwaSlP) WHERE Widoczny;
CREATE INDEX
tymczas=> -- jeden z widokow definiujacy wirtualny slownik
tymczas=> CREATE OR REPLACE VIEW ZrodloN (ID_ZrodloN,Waga,Widoczny,Nazwa)
tymczas-> AS SELECT ID_SlownikU,Waga,Widoczny,NazwaSlP FROM SlownikU
WHERE ID_SlownikUn=14;
CREATE VIEW
tymczas=> -- definicja slownika wirtualnego
tymczas=> INSERT INTO SlownikUn (id_SlownikUn,NazwaUn,Opis)
VALUES(14,'ZrodloN','Nazwa źródła danych zasilających bazę');
INSERT 97726 1
tymczas=> -- Wartości słownika 14,ZrodloN(azwy zrodel)
tymczas=> COPY SlownikU (id_SlownikUn,id_SlownikU,Waga,NazwaSlP) FROM
stdin DELIMITER ',';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 14,11,110,SystemProdukcyjny
>> 14,12,120,Zaplecze
>> 14,13,130,Arkusze
>> \.
tymczas=> -- Definicja zrodel danych
tymczas=> CREATE TABLE Zrodlo (
tymczas(> ID_Zrodlo SERIAL PRIMARY KEY, -- wewnetrzny identyfikator
zrodla
tymczas(> NazwaW TEXT NOT NULL UNIQUE CHECK (NazwaW<>''), -- Nazwa
źródła
tymczas(> IDENT_Org TEXT NOT NULL UNIQUE CHECK (IDENT_Org<>''), --
Identyfikator źródła dla procedury importu
tymczas(> ID_ZrodloN INT NOT NULL REFERENCES ZrodloN, -- wskazanie
na nazwę ogólną źródła danych (klasa źródła)
tymczas(> Waga INT NOT NULL DEFAULT 0, -- do sortowania
tymczas(> Widoczny BOOLEAN NOT NULL DEFAULT TRUE -- czy to źródło ma
być widoczne (nie skasowane)
tymczas(> );
NOTICE: CREATE TABLE will create implicit sequence
"zrodlo_id_zrodlo_seq" for "serial" column "zrodlo.id_zrodlo"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"zrodlo_pkey" for table "zrodlo"
NOTICE: CREATE TABLE / UNIQUE will create implicit index
"zrodlo_nazwaw_key" for table "zrodlo"
NOTICE: CREATE TABLE / UNIQUE will create implicit index
"zrodlo_ident_org_key" for table "zrodlo"
ERROR: referenced relation "zrodlon" is not a table
tymczas=>
Chodzi o ostatni ERROR: referenced relation "zrodlon" is not a table
Jak w takiej konstrukcji wygenerowac klucz obcy? moze wskazywac
na oryginalna tabele slownikU, ale nie potrafie sobie poradzic.
pewnie trzeba uzyc triggera.
potrzebuje jedynie, aby taki klucz nie dopuscil do usuniecia
pozycji w slowniku, i nie pozwalal na wybor indeksu spoza slownika.
nie przewiduje updateow na polach ID.
po co to wszystko? slownikow jest cale mnostwo, i to sa male slowniki.
pomyslalem nie wiem czy slusznie, ze warto to zbic w mniejsza ilosc tabel.
Jesli ktos jest w stanie mi pomoc, to chetnie skorzystam.
Pozdrawiam, znik.
Grzegorz Szyszlo - 07-04-2006 00:03
Grzegorz Szyszlo wrote:
Naprawdę nikt nie wie?
> Czesc. Mam pewien problem w postgres 7.4.7 . Czemu taki stary? Bo to
> debian/stable.
> Mniejsza z tym.
>
> Wpadłem na pewien "Genialny" pomysł, aby wiele słowników o identycznej
> strukturze
> władować do dwóch tabel. Po władowaniu, sumarycznie mam ok. 100 wierszy
> danych.
> Na to nakładam widoki, symulujące poprzednio utworzone słowniki.
> No i mam problem. Wg. definicji, tabele odwołują się kluczem obcym
> do takich słowników. No i klops. Nie bardzo wiem jak to obejść,
> bo w takim słowniku klucz główny jest kluczem złożonym z 2ch kolumn.
> Poniżej jest konstrukcja bazy, a jeszcze niżej jak to wygląda
> po władowaniu do shella psql:
>
> ============konstrukcja bazy
>
> -- Slownik uniwersalny - nazwy slownikow
> CREATE TABLE SlownikUn (
> id_SlownikUn INT NOT NULL PRIMARY KEY, -- id typu
> NazwaUn TEXT NOT NULL UNIQUE, -- nazwa typu
> Opis TEXT -- rozszerzony komentarz
> opisujący słownik
> );
> -- Slownik uniwersalny - wlasciwy
> CREATE TABLE SlownikU (
> id_SlownikU INT NOT NULL, -- id pozycji slownika
> id_SlownikUn INT NOT NULL REFERENCES SlownikUn, -- odwolanie do
> nazwy slownika
> Waga INT, -- waga pozycji, mniej -
> wazniejsza
> NazwaSlP TEXT NOT NULL, -- nazwa pozycji w slowniku
> Widoczny BOOLEAN DEFAULT TRUE, -- wskaznik widocznosci
> pozycji
> PRIMARY KEY (id_SlownikU,id_SlownikUn)
> );
> -- zeby nie bylo powtorek
> CREATE UNIQUE INDEX SlownikU_indx1 ON SlownikU (id_SlownikU,
> id_SlownikUn) WHERE Widoczny;
> CREATE UNIQUE INDEX SlownikU_indx2 ON SlownikU (id_SlownikU,
> id_SlownikUn,NazwaSlP) WHERE Widoczny;
>
> -- jeden z widokow definiujacy wirtualny slownik
> CREATE OR REPLACE VIEW ZrodloN (ID_ZrodloN,Waga,Widoczny,Nazwa)
> AS SELECT ID_SlownikU,Waga,Widoczny,NazwaSlP FROM SlownikU WHERE
> ID_SlownikUn=14;
>
> -- definicja slownika wirtualnego
> INSERT INTO SlownikUn (id_SlownikUn,NazwaUn,Opis)
> VALUES(14,'ZrodloN','Nazwa źródła danych zasilających bazę');
> -- Wartości słownika 14,ZrodloN(azwy zrodel)
> COPY SlownikU (id_SlownikUn,id_SlownikU,Waga,NazwaSlP) FROM stdin
> DELIMITER ',';
> 14,11,110,SystemProdukcyjny
> 14,12,120,Zaplecze
> 14,13,130,Arkusze
> \.
>
> -- Definicja zrodel danych
> CREATE TABLE Zrodlo (
> ID_Zrodlo SERIAL PRIMARY KEY, -- wewnetrzny identyfikator zrodla
> NazwaW TEXT NOT NULL UNIQUE CHECK (NazwaW<>''), -- Nazwa źródła
> IDENT_Org TEXT NOT NULL UNIQUE CHECK (IDENT_Org<>''), -- Identyfikator
> źródła dla procedury importu
> ID_ZrodloN INT NOT NULL REFERENCES ZrodloN, -- wskazanie na nazwę
> ogólną źródła danych (klasa źródła)
> Waga INT NOT NULL DEFAULT 0, -- do sortowania
> Widoczny BOOLEAN NOT NULL DEFAULT TRUE -- czy to źródło ma być
> widoczne (nie skasowane)
> );
>
>
> ================= dialog z psql
>
>
> Linux> psql tymczas
> tymczas=> -- Slownik uniwersalny - nazwy slownikow
> tymczas=> CREATE TABLE SlownikUn (
> tymczas(> id_SlownikUn INT NOT NULL PRIMARY KEY, -- id typu
> tymczas(> NazwaUn TEXT NOT NULL UNIQUE, -- nazwa typu
> tymczas(> Opis TEXT -- rozszerzony
> komentarz opisujący słownik
> tymczas(> );
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "slownikun_pkey" for table "slownikun"
> NOTICE: CREATE TABLE / UNIQUE will create implicit index
> "slownikun_nazwaun_key" for table "slownikun"
> CREATE TABLE
> tymczas=> -- Slownik uniwersalny - wlasciwy
> tymczas=> CREATE TABLE SlownikU (
> tymczas(> id_SlownikU INT NOT NULL, -- id pozycji
> slownika
> tymczas(> id_SlownikUn INT NOT NULL REFERENCES SlownikUn, --
> odwolanie do nazwy slownika
> tymczas(> Waga INT, -- waga
> pozycji, mniej - wazniejsza
> tymczas(> NazwaSlP TEXT NOT NULL, -- nazwa
> pozycji w slowniku
> tymczas(> Widoczny BOOLEAN DEFAULT TRUE, -- wskaznik
> widocznosci pozycji
> tymczas(> PRIMARY KEY (id_SlownikU,id_SlownikUn)
> tymczas(> );
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "slowniku_pkey" for table "slowniku"
> CREATE TABLE
> tymczas=> -- zeby nie bylo powtorek
> tymczas=> CREATE UNIQUE INDEX SlownikU_indx1 ON SlownikU (id_SlownikU,
> id_SlownikUn) WHERE Widoczny;
> CREATE INDEX
> tymczas=> CREATE UNIQUE INDEX SlownikU_indx2 ON SlownikU (id_SlownikU,
> id_SlownikUn,NazwaSlP) WHERE Widoczny;
> CREATE INDEX
> tymczas=> -- jeden z widokow definiujacy wirtualny slownik
> tymczas=> CREATE OR REPLACE VIEW ZrodloN (ID_ZrodloN,Waga,Widoczny,Nazwa)
> tymczas-> AS SELECT ID_SlownikU,Waga,Widoczny,NazwaSlP FROM SlownikU
> WHERE ID_SlownikUn=14;
> CREATE VIEW
> tymczas=> -- definicja slownika wirtualnego
> tymczas=> INSERT INTO SlownikUn (id_SlownikUn,NazwaUn,Opis)
> VALUES(14,'ZrodloN','Nazwa źródła danych zasilających bazę');
> INSERT 97726 1
> tymczas=> -- Wartości słownika 14,ZrodloN(azwy zrodel)
> tymczas=> COPY SlownikU (id_SlownikUn,id_SlownikU,Waga,NazwaSlP) FROM
> stdin DELIMITER ',';
> Enter data to be copied followed by a newline.
> End with a backslash and a period on a line by itself.
> >> 14,11,110,SystemProdukcyjny
> >> 14,12,120,Zaplecze
> >> 14,13,130,Arkusze
> >> \.
> tymczas=> -- Definicja zrodel danych
> tymczas=> CREATE TABLE Zrodlo (
> tymczas(> ID_Zrodlo SERIAL PRIMARY KEY, -- wewnetrzny identyfikator
> zrodla
> tymczas(> NazwaW TEXT NOT NULL UNIQUE CHECK (NazwaW<>''), -- Nazwa
> źródła
> tymczas(> IDENT_Org TEXT NOT NULL UNIQUE CHECK (IDENT_Org<>''), --
> Identyfikator źródła dla procedury importu
> tymczas(> ID_ZrodloN INT NOT NULL REFERENCES ZrodloN, -- wskazanie
> na nazwę ogólną źródła danych (klasa źródła)
> tymczas(> Waga INT NOT NULL DEFAULT 0, -- do sortowania
> tymczas(> Widoczny BOOLEAN NOT NULL DEFAULT TRUE -- czy to źródło ma
> być widoczne (nie skasowane)
> tymczas(> );
> NOTICE: CREATE TABLE will create implicit sequence
> "zrodlo_id_zrodlo_seq" for "serial" column "zrodlo.id_zrodlo"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "zrodlo_pkey" for table "zrodlo"
> NOTICE: CREATE TABLE / UNIQUE will create implicit index
> "zrodlo_nazwaw_key" for table "zrodlo"
> NOTICE: CREATE TABLE / UNIQUE will create implicit index
> "zrodlo_ident_org_key" for table "zrodlo"
> ERROR: referenced relation "zrodlon" is not a table
> tymczas=>
>
> Chodzi o ostatni ERROR: referenced relation "zrodlon" is not a table
> Jak w takiej konstrukcji wygenerowac klucz obcy? moze wskazywac
> na oryginalna tabele slownikU, ale nie potrafie sobie poradzic.
> pewnie trzeba uzyc triggera.
> potrzebuje jedynie, aby taki klucz nie dopuscil do usuniecia
> pozycji w slowniku, i nie pozwalal na wybor indeksu spoza slownika.
> nie przewiduje updateow na polach ID.
>
> po co to wszystko? slownikow jest cale mnostwo, i to sa male slowniki.
> pomyslalem nie wiem czy slusznie, ze warto to zbic w mniejsza ilosc tabel.
> Jesli ktos jest w stanie mi pomoc, to chetnie skorzystam.
>
> Pozdrawiam, znik.
=?iso-8859-2?q?Marcin_St=EApnicki?= - 07-04-2006 00:03
Dnia Tue, 04 Apr 2006 19:40:59 +0200, Grzegorz Szyszlo napisał(a):
> po co to wszystko? slownikow jest cale mnostwo, i to sa male slowniki.
> pomyslalem nie wiem czy slusznie, ze warto to zbic w mniejsza ilosc tabel.
> Jesli ktos jest w stanie mi pomoc, to chetnie skorzystam.
Ja właśnie z powodu problemów na które się natknąłeś
zrezygnowałem jakiś czas temu z tego typu pomysłu. Jedynym i to
średnim rozwiązaniem wydawało mi się użycie constraintów CHECK, ale
imho nie warto.
P.S. Jeżeli chcesz mieć nowego Postgresa w stable a nie chcesz
kompilować go samodzielnie - http://www.backports.org/.
--
| Where there's a boy to give his heart | http://apcoln.linuxpl.org
| There's a woman to tear it apart | http://biznes.linux.pl
|---------------------------------------| http://www.juanperon.info
| JID: http://www.naszedzieci.org
Grzegorz Szyszlo - 08-04-2006 00:04
Marcin Stępnicki wrote:
> Dnia Tue, 04 Apr 2006 19:40:59 +0200, Grzegorz Szyszlo napisał(a):
>
>>po co to wszystko? slownikow jest cale mnostwo, i to sa male slowniki.
>>pomyslalem nie wiem czy slusznie, ze warto to zbic w mniejsza ilosc tabel.
>>Jesli ktos jest w stanie mi pomoc, to chetnie skorzystam.
>
> Ja właśnie z powodu problemów na które się natknąłeś
> zrezygnowałem jakiś czas temu z tego typu pomysłu. Jedynym i to
> średnim rozwiązaniem wydawało mi się użycie constraintów CHECK, ale
> imho nie warto.
Tez tak probowalem, ale .... no wlasnie jest pewne ale. w CHECK
musialbym uzyc nieobslugiwanego w 7.4 subselect, jedynie trigger
zalatwilby definitywnie sprawe.
z pomyslu bede jednak korzystal. zrobilem jeden klucz glowny
z jedna kolumna w tabeli uniwersalnej, a dodatkowa kolumna
definiuje mi grupę, subsłownik, który zbiera wiersze do niego
należące. widoków używam w celach pomocniczych, ale w references
wskazuję wprost klucz w slowniku uniwersalnym. jak na razie sie
sprawdza. pozostaje problem testowania, czy pozycja nalezy
do wlasciwego subsłownika. za pomocą check sprawdzic sie tego nie da.
pewnie w pozniejszym terminie dopisze triggera, teraz niestety nie mam
na to zbyt wiele czasu bo projekt w toku.
pozostale wieksze slowniki sa zrobione osobno.
w kazdym razie dzieki za opinie ;)
> P.S. Jeżeli chcesz mieć nowego Postgresa w stable a nie chcesz
> kompilować go samodzielnie - http://www.backports.org/.
wyprobuje ;) mam nadzieje ze swiezy pgadmin tez tam jest ;))
jeszcze raz dzieki.
znik.
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
|
[PostgreSQL] - jak =?ISO-8859-2?Q?zabezpieczy=E6_interesy_tw?==?ISO-8859-2?Q?=F3rcy_systemu_=3F=3F=3F?=
postgresql - int/int
postgresql Select count(*) czy raczej Select count(ID)
[PostgreSQL] jak =?ISO-8859-2?Q?pobra=E6_warto=B6=E6_zwracan?==?ISO-8859-2?Q?=B1_przez_funkcj=EA=3F?=
[postgresql] INSERT OR UPDATE - jak =?ISO-8859-2?Q?b=EAdzie_na?==?ISO-8859-2?Q?jlepiej=3F?=
[postgresql] kilka =?ISO-8859-2?Q?rekord=F3w_subquery_jako_?==?ISO-8859-2?Q?string?=
[PostgreSQL] Jak =?ISO-8859-2?Q?po=B3=B1czy=E6_funkcje_z_w?==?ISO-8859-2?Q?idokiem?=
Postgres - replikcja master-master
Dopasowanie do "najlepszego" dopasowania :) [ PostgreSQL]
Problemy z =?ISO-8859-2?Q?instalacj=B1_PostgreSQL_na_syste?==?ISO-8859-2?Q?mach_Windows?=
zanotowane.pldoc.pisz.plpdf.pisz.platanvarne633.opx.pl
Cytat
Decede mihi sole - nie zasłaniaj mi słonca.
Gdy kogoś kochasz, jesteś jak stworzyciel świata - na cokolwiek spojrzysz, nabiera to kształtu, wypełnia się barwą, światłem. Powietrze przytula się do ciebie, choćby był mróz, a ty masz w sobie tyle radości, że musisz ją rozdawać wokoło, bo się w tobie nie mieści
Hoc fac - tak czyń.
A tergo - od tyłu; z tyłu.
I czarne włosy posiwieją. Safona |
|
