Foreign key mit mehreren Spalten aus verschiedenen Tabellen

Kann man nicht in SQL. Ich denke, Sie könnte wenn SQL unterstützte Behauptungen. (Der SQL-92-standard definiert Behauptungen. Niemand unterstützt Sie noch, so weit ich weiß.)

Arbeiten, um das problem zu beheben, verwenden überlappende Einschränkungen.

-- Nothing special here.
create table animal_types (
  animal_type varchar(15) primary key
);

create table animals (
  name varchar(15) primary key,
  animal_type varchar(15) not null references animal_types (animal_type),
  -- This constraint lets us work around SQL's lack of assertions in this case.
  unique (name, animal_type)
);

-- Nothing special here.
create table animal_food_types (
  animal_type varchar(15) not null references animal_types (animal_type),
  food_type varchar(15) not null,
  primary key (animal_type, food_type)
);

-- Overlapping foreign key constraints.
create table animals_preferred_food (
  animal_name varchar(15) not null,
  -- This column is necessary to implement your requirement. 
  animal_type varchar(15) not null,
  pref_food varchar(10) not null,
  primary key (animal_name, pref_food),
  -- This foreign key constraint requires a unique constraint on these
  -- two columns in "animals".
  foreign key (animal_name, animal_type) 
    references animals (animal_name, animal_type),
  -- Since the animal_type column is now in this table, this constraint
  -- is simple.
  foreign key (animal_type, pref_food) 
    references animal_food_types (animal_type, food_type)
);

Je nachdem, was DBMS du verwendest (bitte Editiere deine Frage, um diese aufzunehmen), würde Sie wahrscheinlich wollen, um eine unique-Einschränkung erstellen, die auf die ANIMAL_TYPE und PREFERED_FOOD Spalten.

Etwas wie dieses:

ALTER TABLE PREFERED_FOOD
ADD CONSTRAINT uc_FoodAnimal UNIQUE (ANIMAL_TYPE,PREFERED_FOOD)

Wenn man die (Natürliche) JOIN von TIEREN und PREFERRED_FOOD, dann bekommst du eine Tabelle, in der für jedes Tier, seine Art und seine bevorzugte Nahrung sind aufgeführt.

Du willst, dass die Kombination "gültig" für jedes einzelne Tier, wo "gültig" bedeutet "erscheinen in der Aufzählung der gültigen Tierart/Lebensmittel-Typ-Kombinationen, die aufgeführt sind in der NAHRUNG.

So eine Einschränkung, das ist etwas ähnliches wie ein FK, aber dieses mal sind die "foreign key" erscheint nicht in einem base-Tabelle, sondern in einer Verknüpfung von zwei Tabellen. Für diese Art der Einschränkung, die SQL-Sprache hat sich CHECK-Einschränkungen und ASSERTIONEN.

Die GELTENDMACHUNG version ist die einfachste. Es ist eine Einschränkung wie (ich habe etwas liberal mit den Attribut-Namen, um zu vermeiden, bloße Attribut benennt das verschleiern der Punkt)

CREATE ASSERTION <name for your constraint here>
 CHECK NOT EXISTS (SELECT ANIMAL_TYPE, FOOD_TYPE
                     FROM ANIMALS NATURAL JOIN PREF_FOOD
                    WHERE (ANIMAL_TYPE, FOOD_TYPE) NOT IN
                          SELECT ANIMAL_TYPE, FOOD_TYPE FROM FOOD_TYPE);

Aber Ihre Durchschnittliche SQL-engine wird nicht unterstützt Behauptungen. So Sie haben zu Verwendung von CHECK-Einschränkungen. Für die PREF_FOOD Tabelle, zum Beispiel die CHECK-Einschränkung müssen Sie Aussehen könnte so etwas wie

CHECK EXISTS (SELECT 1
                FROM FOOD NATURAL JOIN ANIMAL
               WHERE ANIMAL_TYPE = <animal type of inserted row> AND
                     FOOD_TYPE = <food type of inserted row>);

In der Theorie sollte diese genügen, um zu erzwingen, Ihre Einschränkung, aber dann wieder Ihre Durchschnittliche SQL-engine wird wieder einmal keine Unterstützung für diese Art von CHECK-Einschränkung, da die Verweise auf die Tabellen andere als die, die die Einschränkung definiert ist.

So die Optionen, die Sie haben, ist der resort eher Komplex (*) setups wie Pfeifen, oder der Durchsetzung der Einschränkung der Verwendung von Triggern (und Sie haben zu schreiben, eine ganze Menge von Ihnen (drei oder sechs zumindest noch nicht durchdacht im detail), und Ihre nächste beste option ist, um dies durchzusetzen, im code der Anwendung, und noch einmal gibt es drei oder sechs (mehr oder weniger) unterschiedliche Orte, an denen die gleiche Anzahl von unterschiedlichen Prüfungen umgesetzt werden müssen.

In all diesen drei Szenarien, werden Sie vorzugsweise dokumentieren wollen, die Existenz der Einschränkung, und was genau es ist, an einem anderen Ort. Keiner der drei wird es sehr offensichtlich, zu einer Dritten Lesung dieses design, was zum Teufel das alles soll.

(*) "Komplex" ist vielleicht nicht genau das richtige Wort, aber beachten Sie, dass diese Lösungen stützen sich auf bewusste Redundanz, so bewusst unter 3NF mit dem design. Und das bedeutet, dass Ihr design ist ausgesetzt-update-Anomalien, was bedeutet, dass es dann schwieriger wird für den Benutzer, die Datenbank zu aktualisieren UND konsistent halten (gerade wegen der bewusste Redundanzen).