Dziedziczenie i klucze obce Opcje

[Helios]

Witam

Mam taki problem przy tworzeniu bazy danych klientow i przypisanych do nich uslug.

Tworze tabele customers ze wspolnymi kolumnami dla klientow indywidualnych i przedsiebiorcow:

[SQL] pobierz, plaintext 
CREATE TABLE customers (
	id INT NOT NULL DEFAULT NEXTVAL('customers_id') PRIMARY KEY,
	address VARCHAR(100) NOT NULL,
	location VARCHAR(100) NOT NULL,
	postcode VARCHAR(6) NOT NULL,
	email VARCHAR(200) NOT NULL UNIQUE,
	phone VARCHAR(30) NOT NULL,
	login VARCHAR(15) NOT NULL UNIQUE,
	password VARCHAR(32) NOT NULL
);
[SQL] pobierz, plaintext 

Teraz chce rozdzielic to na klientow indywidualnych i przedsiebiorcow, wiec tworze 2 tabele dziedziczace:

[SQL] pobierz, plaintext 
CREATE TABLE corporation_customers (
	name VARCHAR(200) NOT NULL,
	nip VARCHAR(13) NOT NULL UNIQUE
) INHERITS (customers);
 
CREATE TABLE private_customers (
	surname VARCHAR(80) NOT NULL,
	name VARCHAR(80) NOT NULL,
	pesel VARCHAR(11) NOT NULL UNIQUE
) INHERITS (customers);
[SQL] pobierz, plaintext 

I teoretycznie wszystko gra do momentu, kiedy nie stworze innej tabeli z kluczem obcym wskazujacym na tabele customers:

[SQL] pobierz, plaintext 
CREATE TABLE services (
	id INT NOT NULL DEFAULT NEXTVAL('services_id'),
	description VARCHAR(255),
	customer_id INT NOT NULL,
	FOREIGN KEY(customer_id) REFERENCES customers(id)
);
[SQL] pobierz, plaintext 

W momencie kiedy wywoluje INSERT zapisujacy usluge dla danego klienta:

[SQL] pobierz, plaintext 
INSERT INTO services (description, customer_id) VALUES ('Service no 1', 1);
[SQL] pobierz, plaintext 

otrzymuje blad, ze klucz 1 nie istnieje w tabeli customers

Klucz 1 istnieje w tabeli private_customers, ktora dziedziczy po customers. Dlaczego PostgreSQL nie widzi tego wpisu jako obecnego w tabeli customers?

[SQL] pobierz, plaintext 
SELECT * FROM customers;
[SQL] pobierz, plaintext 

Pokazuje mi ten wpis. Jezeli dam INSERT bezposrednio do tabeli customers, pomijajac tabele private_customers, corporation_customers wszystko dziala bez problemu. Wniosek z tego taki, ze postgres traktuje referencje do tabeli customers bezposrednio nie uwzgledniajac wpisow w tabelach potomkach. Troche mi to teraz komplikuje sprawe, bo nie chce trzymac danych klientow indywidualnych w jednej tabeli, bo chce narzucic wymog wpisania numeru pesel, numeru nip i dla przedsiebiorcow wprowadzic dodatkowe klucze obce.

Jak to mozna rozwiazac? Ma ktos pomysl, stawiam piwo!

Pozdrawiam

[prond]

Chyba zła wiadomość (cytat z http://www.postgresql.org/docs/8.3/static/ddl-inherit.html):
Specifying that another table's column REFERENCES cities(name) would allow the other table to contain city names, but not capital names. There is no good workaround for this case.

Z dokładnością do nazw tabel i kolumn to dokładnie opisuje Twój przypadek.

[Mikz]

Podłączam się pod temat, dlatego odkopuję. Przedstawię całkowicie analogiczny do powyższego problem i mniej istotne jest tutaj to czy uzyskam rozwiązanie (bo dokumentacja jasno mówi "There is no good workaround for this case."), bardziej istotna dla mnie jest przyczyna takiego działania i dyskusja na ten temat.

Mając tabele:

Zdarzenie (id, czas, id_obiektu [referencja do: Obiekt])
Budowa (koszt) dziedziczy z: Zdarzenie

Obiekt (id, parę_innych_nie_mających_znaczenia_kolumn)
Planeta (nazwa) dziedziczy z: Obiekt

władowanie wiersza do tabeli Budowa o zdefiniowanych wartościach czas, koszt i id_obiektu, istniejącego w tabeli Planeta, kończy się oczywiście BŁĘDEM.

Pytanie:
Jaki to ma sens skoro SELECT * FROM Obiekt zwraca mi oczywiście wiersz widniejący w tabeli Planeta, bo ten wiersz tam istnieje a tabela dziedziczy.
Nawet więcej: pomimo istnienia wiersza w tabeli Planeta o id = 1 (z id ustawionym jako UNIQUE) nie mam żadnego problemu z wstawieniem wiersza o tym samym ID do tabeli Obiekt, pomimo tego że sam nextval() bierze pod uwagę wiersze z tabeli dziedziczącej.
Pytam ponownie: Jaki to ma sens?

Dlaczego, jeżeli SELECTowanie rekordów z tabeli rodzica zwraca również wiersze z tabeli dziedziczącej, przy innych operacjach działa to w sposób odwrotny?

Czy dziedziczenie w PostgreSQL jest po prostu niedorobione? Bo dla mnie przy takich znaczących uchybieniach jest po prostu bezużyteczne a wybrałem tą bazę do swojego projektu m. in. ze względu na dziedziczenie, bo dzięki niemu mógłbym fajnie zaprojektować jej strukturę w taki sposób by była ona bardziej zbliżona do tego jak będzie wyglądać struktura samej aplikacji.

Edit: Aha, w dokumentacji do której linka dał prond, napisane jest "These deficiencies will probably be fixed in some future release". Prond napisał posta w grudniu 2008 a ten zapis w dokumentacji kolejnych wersji widnieje dalej . To tego prond w swoim poście z grudnia 2008 dał link do dokumentacji wersji 8.3 która na stronie postgresql jest oznaczona z release date: "2010-05-17" tak jak i zresztą wszystkie pozostałe wersje.

Edit2: Szperając po googlu znalazłem rozwiązanie: http://projects.nocternity.net/index.py/en/psql-inheritance
Jest nim zestaw procedur pl/pgsql obchodzących problemy z dziedziczeniem w PostgreSQL. Może komuś to się przyda, mi na pewno nie, ponieważ patrząc na zapis

This code is mostly untested. It may not work as expected. I have not been using it myself lately. However, if you encounter any problems with it, please contact me! Thank you.

nie chcę ładować tego do bazy która ma obsługiwać duże ilości danych w komercyjnym projekcie.

Ten post edytował Mikz 26.06.2010, 15:34:49