Czy można zrobić klucz obcy MySQL do jednej z dwóch możliwych tabel?

Cóż, oto mój problem. Mam trzy stoły; regiony, kraje, stany. Kraje mogą znajdować się w regionach, państwa mogą znajdować się w regionach. Regiony są na szczycie łańcucha pokarmowego.

Teraz dodaję popularną tabelę z dwiema kolumnami; region_id i popular_nazwa_id. Czy można uczynić popular_place_id kluczem obcym dla obu krajów LUB stanów? Prawdopodobnie będę musiał dodać kolumnę popular_place_type, aby ustalić, czy identyfikator opisuje kraj czy stan.

To, co opisujesz, nazywa się skojarzeniami polimorficznymi. Oznacza to, że kolumna „klucz obcy” zawiera wartość identyfikatora, która musi istnieć w jednym z zestawu tabel docelowych. Zazwyczaj tabele docelowe są w jakiś sposób powiązane, na przykład są instancjami jakiejś wspólnej nadklasy danych. Potrzebna byłaby również inna kolumna obok kolumny klucza obcego, aby w każdym wierszu można było wskazać, do której tabeli docelowej się odwołuje.

CREATE TABLE popular_places (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  place_type VARCHAR(10) -- either 'states' or 'countries'
  -- foreign key is not possible
);

Nie ma możliwości modelowania skojarzeń polimorficznych przy użyciu ograniczeń SQL. Ograniczenie klucza obcego zawsze odnosi się do jednej tabeli docelowej.

Powiązania polimorficzne są obsługiwane przez frameworki, takie jak Rails i Hibernacja. Ale wyraźnie mówią, że musisz wyłączyć ograniczenia SQL, aby korzystać z tej funkcji. Zamiast tego aplikacja lub środowisko musi wykonać równoważną pracę, aby zapewnić spełnienie odwołania. Oznacza to, że wartość klucza obcego jest obecna w jednej z możliwych tabel docelowych.

Powiązania polimorficzne są słabe pod względem wymuszania spójności bazy danych. Integralność danych zależy od wszystkich klientów uzyskujących dostęp do bazy danych z tą samą wymuszoną logiką integralności referencyjnej, a także egzekwowanie musi być wolne od błędów.

Oto kilka alternatywnych rozwiązań, które wykorzystują wymuszoną przez bazę danych integralność referencyjną:

Utwórz jedną dodatkową tabelę dla każdego celu. Na przykład popular_states, a popular_countries, które to odniesienie statesi countriesodpowiednio. Każda z tych „popularnych” tabel odwołuje się również do profilu użytkownika.

CREATE TABLE popular_states (
  state_id INT NOT NULL,
  user_id  INT NOT NULL,
  PRIMARY KEY(state_id, user_id),
  FOREIGN KEY (state_id) REFERENCES states(state_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

CREATE TABLE popular_countries (
  country_id INT NOT NULL,
  user_id    INT NOT NULL,
  PRIMARY KEY(country_id, user_id),
  FOREIGN KEY (country_id) REFERENCES countries(country_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

Oznacza to, że aby uzyskać wszystkie ulubione ulubione miejsca użytkownika, należy wykonać zapytanie do obu tych tabel. Oznacza to jednak, że możesz polegać na bazie danych w celu wymuszenia spójności.

Utwórz placestabelę jako supertable. Jak wspomina Abie, drugą alternatywą jest to, że twoje popularne miejsca odnoszą się do tabeli places, która jest rodzicem zarówno dla, jak statesi dla countries. Oznacza to, że zarówno państwa, jak i kraje mają również klucz obcy do places(możesz nawet ustawić ten klucz obcy również jako klucz podstawowy statesi countries).

CREATE TABLE popular_areas (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  PRIMARY KEY (user_id, place_id),
  FOREIGN KEY (place_id) REFERENCES places(place_id)
);

CREATE TABLE states (
  state_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (state_id) REFERENCES places(place_id)
);

CREATE TABLE countries (
  country_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

Użyj dwóch kolumn. Zamiast jednej kolumny, która może odwoływać się do jednej z dwóch tabel docelowych, użyj dwóch kolumn. Te dwie kolumny mogą być NULL; w rzeczywistości tylko jeden z nich powinien być nie- NULL.

CREATE TABLE popular_areas (
  place_id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  state_id INT,
  country_id INT,
  CONSTRAINT UNIQUE (user_id, state_id, country_id), -- UNIQUE permits NULLs
  CONSTRAINT CHECK (state_id IS NOT NULL OR country_id IS NOT NULL),
  FOREIGN KEY (state_id) REFERENCES places(place_id),
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

Jeśli chodzi o teorię relacyjną, stowarzyszenia polimorficzne naruszają pierwszą normalną formę , ponieważ popular_place_idw rzeczywistości jest to kolumna o dwóch znaczeniach: albo stan, albo kraj. Nie będzie przechowywać danej osoby agei ich phone_numberw jednej kolumnie, i z tego samego powodu nie należy przechowywać zarówno state_idi country_idw jednej kolumnie. Fakt, że te dwa atrybuty mają kompatybilne typy danych, jest przypadkowy; wciąż oznaczają różne byty logiczne.

Powiązania polimorficzne również naruszają trzecią postać normalną , ponieważ znaczenie kolumny zależy od dodatkowej kolumny, która określa tabelę, do której odnosi się klucz obcy. W trzeciej postaci normalnej atrybut w tabeli musi zależeć tylko od klucza podstawowego tej tabeli.

Ponownie skomentuj @SavasVedova:

Nie jestem pewien, czy podążam za twoim opisem, nie widząc definicji tabeli lub przykładowego zapytania, ale wygląda na to, że masz po prostu wiele Filterstabel, z których każda zawiera klucz obcy, który odwołuje się do Productstabeli centralnej .

CREATE TABLE Products (
  product_id INT PRIMARY KEY
);

CREATE TABLE FiltersType1 (
  filter_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE FiltersType2 (
  filter_id INT  PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

...and other filter tables...

Łączenie produktów z określonym typem filtra jest łatwe, jeśli wiesz, do którego typu chcesz dołączyć:

SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)

Jeśli chcesz, aby typ filtra był dynamiczny, musisz napisać kod aplikacji, aby zbudować zapytanie SQL. SQL wymaga, aby tabela była określona i ustalona w momencie pisania zapytania. Nie można dynamicznie wybierać połączonej tabeli na podstawie wartości znalezionych w poszczególnych wierszach Products.

Jedyną inną opcją jest dołączenie do wszystkich tabel filtrów za pomocą sprzężeń zewnętrznych. Te, które nie mają pasującego identyfikatora_produktu, zostaną zwrócone jako pojedynczy wiersz wartości null. Ale nadal musisz na stałe zakodować wszystkie połączone tabele, a jeśli dodasz nowe tabele filtrów, musisz zaktualizować kod.

SELECT * FROM Products
LEFT OUTER JOIN FiltersType1 USING (product_id)
LEFT OUTER JOIN FiltersType2 USING (product_id)
LEFT OUTER JOIN FiltersType3 USING (product_id)
...

Innym sposobem dołączenia do wszystkich tabel filtrów jest wykonanie tego szeregowo:

SELECT * FROM Product
INNER JOIN FiltersType1 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType3 USING (product_id)
...

Ale ten format nadal wymaga zapisywania odniesień do wszystkich tabel. Nie można tego obejść.

To nie jest najbardziej eleganckie rozwiązanie na świecie, ale możesz użyć dziedziczenia konkretnego stołu, aby to zadziałało.

Koncepcyjnie proponujesz pojęcie klasy „rzeczy, które mogą być popularnymi obszarami”, z których dziedziczą twoje trzy typy miejsc. Można reprezentować ten jako tabela o nazwie, na przykład, placesgdzie każdy wiersz ma związek z rzędu jeden do jednego w regions, countrieslub states. (Atrybuty, które są wspólne między regionami, krajami lub stanami, jeśli takie istnieją, mogą zostać wypchnięte do tej tabeli miejsc.) Twoje popular_place_idodwołanie byłoby wówczas kluczem obcym do wiersza w tabeli miejsc, który następnie doprowadziłby Cię do regionu, kraju lub stan.

Rozwiązaniem, które proponujesz w drugiej kolumnie opisującej rodzaj skojarzenia, jest sposób, w jaki Railsy radzą sobie z skojarzeniami polimorficznymi, ale ogólnie nie jestem fanem tego. Bill wyjaśnia szczegółowo, dlaczego skojarzenia polimorficzne nie są twoimi przyjaciółmi.

Oto poprawka do „supertable” podejścia Billa Karwina, przy użyciu złożonego klucza, ( place_type, place_id )aby rozwiązać postrzegane naruszenia normalnej formy:

CREATE TABLE places (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) NOT NULL
     CHECK ( place_type = 'state', 'country' ),
  UNIQUE ( place_type, place_id )
);

CREATE TABLE states (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) DEFAULT 'state' NOT NULL
     CHECK ( place_type = 'state' ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
  -- attributes specific to states go here
);

CREATE TABLE countries (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) DEFAULT 'country' NOT NULL
     CHECK ( place_type = 'country' ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
  -- attributes specific to country go here
);

CREATE TABLE popular_areas (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  UNIQUE ( user_id, place_id ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
);

Czego ten projekt nie może zapewnić, że dla każdego wiersza placesistnieje wiersz w stateslub countries(ale nie oba). Jest to ograniczenie kluczy obcych w SQL. W systemie DBMS w pełni zgodnym ze standardami SQL-92 można zdefiniować odroczone ograniczenia między tabelami, które pozwoliłyby na osiągnięcie tego samego, ale jest niezgrabne, wymaga transakcji i taki system DBMS musi jeszcze wejść na rynek.

Zdaję sobie sprawę, że ten wątek jest stary, ale zobaczyłem to i przyszło mi do głowy rozwiązanie i myślałem, że go tam wyrzucę.

Regiony, kraje i stany to lokalizacje geograficzne, które żyją w hierarchii.

Możesz całkowicie uniknąć tego problemu, tworząc tabelę domen o nazwie typ_lokalizacji geograficznej, którą zapełnisz trzema wierszami (Region, Kraj, Stan).

Następnie zamiast trzech tabel lokalizacji utwórz pojedynczą tabelę lokalizacji geograficznej, która ma klucz obcy identyfikator_typu lokalizacji geograficznej (dzięki czemu wiesz, czy instancja to Region, Kraj lub Stan).

Modeluj hierarchię, czyniąc tę ​​tabelę samoreferencyjną, tak aby instancja stanu przechowywała klucz fKey do swojej instancji nadrzędnej Country, która z kolei przechowuje fKey do instancji nadrzędnej regionu. Instancje regionu miałyby wartość NULL w tym kluczu. Nie różni się to od tego, co zrobiłbyś z trzema tabelami (miałbyś 1 - wiele relacji między regionem a krajem oraz między krajem a stanem), z wyjątkiem tego, że wszystko jest w jednej tabeli.

Popularna tabela_użytkownika byłaby tabelą rozdzielczości zakresu między użytkownikiem a georgraphical_location (tak wielu użytkowników może polubić wiele miejsc).

Soooo…

CREATE TABLE [geographical_location_type] (
    [geographical_location_type_id] INTEGER NOT NULL,
    [name] VARCHAR(25) NOT NULL,
    CONSTRAINT [PK_geographical_location_type] PRIMARY KEY ([geographical_location_type_id])
)

-- Add 'Region', 'Country' and 'State' instances to the above table


CREATE TABLE [geographical_location] (
   [geographical_location_id] BIGINT IDENTITY(0,1) NOT NULL,
    [name] VARCHAR(1024) NOT NULL,
    [geographical_location_type_id] INTEGER NOT NULL,
    [geographical_location_parent] BIGINT,  -- self referencing; can be null for top-level instances
    CONSTRAINT [PK_geographical_location] PRIMARY KEY ([geographical_location_id])
)

CREATE TABLE [user] (
    [user_id] BIGINT NOT NULL,
    [login_id] VARCHAR(30) NOT NULL,
    [password] VARCHAR(512) NOT NULL,
    CONSTRAINT [PK_user] PRIMARY KEY ([user_id])
)


CREATE TABLE [popular_user_location] (
    [popular_user_location_id] BIGINT NOT NULL,
    [user_id] BIGINT NOT NULL,
    [geographical_location_id] BIGINT NOT NULL,
    CONSTRAINT [PK_popular_user_location] PRIMARY KEY ([popular_user_location_id])
)

ALTER TABLE [geographical_location] ADD CONSTRAINT [geographical_location_type_geographical_location] 
    FOREIGN KEY ([geographical_location_type_id]) REFERENCES [geographical_location_type] ([geographical_location_type_id])



ALTER TABLE [geographical_location] ADD CONSTRAINT [geographical_location_geographical_location] 
    FOREIGN KEY ([geographical_location_parent]) REFERENCES [geographical_location] ([geographical_location_id])



ALTER TABLE [popular_user_location] ADD CONSTRAINT [user_popular_user_location] 
    FOREIGN KEY ([user_id]) REFERENCES [user] ([user_id])



ALTER TABLE [popular_user_location] ADD CONSTRAINT [geographical_location_popular_user_location] 
    FOREIGN KEY ([geographical_location_id]) REFERENCES [geographical_location] ([geographical_location_id])

Nie byłem pewien, jaki był docelowy DB; powyższe to MS SQL Server.

Mam dwie tabele:

1. piosenki

a) Numer piosenki b) Tytuł piosenki ....

2. listy odtwarzania a) Numer listy odtwarzania b) Tytuł listy odtwarzania ...

i mam trzeci

3. piosenki_do_playlist_relation

Problem polega na tym, że niektóre rodzaje list odtwarzania mają link do innych list odtwarzania. Ale w mysql nie mamy klucza obcego powiązanego z dwiema tabelami.

Moje rozwiązanie: wstawię trzecią kolumnę w pliku song_to_playlist_relation. Ta kolumna będzie logiczna. Jeśli 1, to piosenka, w przeciwnym razie będzie link do tabeli list odtwarzania.

Więc:

3. piosenki_do_playlist_relation

a) Numer_listy (int) b) Czy piosenka (boolean) c) Numer względny (numer piosenki lub numer listy odtwarzania) (int) ( nie klucz obcy do żadnej tabeli)

 # utwórz piosenki stołowe 
    zapytania . dołączająca ( "SET SQL_MODE = NO_AUTO_VALUE_ON_ZERO," ) 
    zapytania . append ( "CREATE TABLE songs( NUMBERint (11) NOT NULL, SONG POSITIONint (11) NOT NULL, PLAY SONGtinyint (1) NOT NULL DEFAULT '1', SONG TITLEvarchar (255) ZESTAW ZNAKÓW utf8 ZGŁOSZENIE utf8_general_ci NOT NULL, DESCRIPTIONvarchar (1000) ZESTAW ZNAKÓW utf8 UKŁADAJ utf8_general_ci NOT NULL, ARTISTvarchar (255) CHARACTER SET utf8 UKŁADAJ utf8_general_ci NOT NULL default 'Άγνωστος καλλιτέχνης', AUTHORvarchar (255) CHARACTER SET utf8 UKŁADAJ utf8_general_ci NOT NULL default 'Άγνωστος στιχουργός', COMPOSERvarchar (255) CHARACTER SET utf8 sortowania nie utf8_general_ci NULL DEFAULT „Άγνωστος συνθέτης”,ALBUMvarchar (255) ZNAK ZESTAWU utf8 ZESTAW UF8 utf8_general_ci NOT NULL DEFAULT 'Άγνωστο άλμπουμ', YEARint (11) NOT NULL DEFAULT '33', RATINGint (11) NOT NULL DEFAULT '5', IMAGEvarchar (600) ZNAK NOTATKI utf8 ZESTAW UF8 utf8 , SONG PATHvarchar (500) ZNAK ZESTAWU utf8 ZBIERZ utf8_general_ci NOT NULL, SONG REPEATint (11) NOT NULL DEFAULT '0', VOLUMEfloat NOT NULL DEFAULT '1', SPEEDfloat NOT NULL DEFAULT '1') ENGINE = InnoDB DEFAULT CHARSET = utf8; " ) 
    zapytania . dołącz ( „ZMIEŃ TABELĘ songsDODAJ KLUCZ PODSTAWOWY ( NUMBER), DODAJ UNIKALNY KLUCZ POSITION( SONG POSITION), DODAJ UNIKALNY KLUCZ TITLE( SONG TITLE), DODAJ UNIKALNY KLUCZ PATH( SONG PATH);”) 
    zapytania. append ( „ALTER TABLE songsMODIFY NUMBERint (11) NOT NULL AUTO_INCREMENT;” )


#create table playlists
queries.append("CREATE TABLE `playlists` (`NUMBER` int(11) NOT NULL,`PLAYLIST POSITION` int(11) NOT NULL,`PLAYLIST TITLE` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`PLAYLIST PATH` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;")
queries.append("ALTER TABLE `playlists` ADD PRIMARY KEY (`NUMBER`),ADD UNIQUE KEY `POSITION` (`PLAYLIST POSITION`),ADD UNIQUE KEY `TITLE` (`PLAYLIST TITLE`),ADD UNIQUE KEY `PATH` (`PLAYLIST PATH`);")
queries.append("ALTER TABLE `playlists` MODIFY `NUMBER` int(11) NOT NULL AUTO_INCREMENT;")

#create table for songs to playlist relation
queries.append("CREATE TABLE `songs of playlist` (`PLAYLIST NUMBER` int(11) NOT NULL,`SONG OR PLAYLIST` tinyint(1) NOT NULL DEFAULT '1',`RELATIVE NUMBER` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;")
queries.append("ALTER TABLE `songs of playlist` ADD KEY `PLAYLIST NUMBER` (`PLAYLIST NUMBER`) USING BTREE;")
queries.append("ALTER TABLE `songs of playlist` ADD CONSTRAINT `playlist of playlist_ibfk_1` FOREIGN KEY (`PLAYLIST NUMBER`) REFERENCES `playlists` (`NUMBER`) ON DELETE RESTRICT ON UPDATE RESTRICT")

playlists_query = "WYBIERZ s1. *, s3. *, s4. * Z utworów jako s1 INNER JOIN` utwory z listy odtwarzania` jako s2 ON s1`NUMBER` = s2`Numer referencyjny` INNER DOŁĄCZ `listy odtwarzania` jak s3 ON s3 .`NUMBER` = s2. NUMER LISTY LISTY` WEWNĘTRZNA DOŁĄCZ `listy odtwarzania jako s4 NA s4`NUMBER` = s2. NUMER WZGLĘDNY WEDŁUG s3`. POZYCJA LISTY LISTY``,` s1`.