LU08c – Fremdschlüssel direkt beim Erstellen (CREATE TABLE)

DROP TABLE IF EXISTS Trip;
DROP TABLE IF EXISTS Country;
DROP TABLE IF EXISTS Continent;
 
CREATE TABLE Continent (
  ContinentID   INT AUTO_INCREMENT PRIMARY KEY,
  ContinentName VARCHAR(50) NOT NULL UNIQUE
);
 
CREATE TABLE Country (
  CountryID     INT AUTO_INCREMENT PRIMARY KEY,
  CountryName   VARCHAR(100) NOT NULL UNIQUE,
  ContinentID   INT NOT NULL,
  CONSTRAINT fk_country_continent
    FOREIGN KEY (ContinentID)
    REFERENCES Continent(ContinentID)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
);
 
CREATE TABLE Trip (
  TripID     INT AUTO_INCREMENT PRIMARY KEY,
  CountryID  INT NOT NULL,
  StartDate  DATE NOT NULL,
  EndDate    DATE NOT NULL,
  Price      DECIMAL(10,2) NOT NULL,
  CONSTRAINT fk_trip_country
    FOREIGN KEY (CountryID)
    REFERENCES Country(CountryID)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
);

-- Kontinent
INSERT INTO Continent (ContinentName) VALUES ('Europa');
 
-- Länder (DE/CH/AT/FR/IT)
INSERT INTO Country (CountryName, ContinentID)
SELECT x.CountryName, c.ContinentID
FROM (SELECT 'Deutschland' CountryName UNION ALL
      SELECT 'Schweiz'    UNION ALL
      SELECT 'Österreich' UNION ALL
      SELECT 'Frankreich' UNION ALL
      SELECT 'Italien') x
JOIN Continent c ON c.ContinentName='Europa';
 
-- Reisen (Beispieldaten, Datum & Preis in EUR/CHF gemischt ist ok, wir speichern nur Zahl)
INSERT INTO Trip (CountryID, StartDate, EndDate, Price)
SELECT CountryID, '2025-07-12', '2025-07-20', 1250.00 FROM Country WHERE CountryName='Italien';
INSERT INTO Trip (CountryID, StartDate, EndDate, Price)
SELECT CountryID, '2025-10-05', '2025-10-11',  820.00 FROM Country WHERE CountryName='Deutschland';
INSERT INTO Trip (CountryID, StartDate, EndDate, Price)
SELECT CountryID, '2025-02-15', '2025-02-19',  590.00 FROM Country WHERE CountryName='Schweiz';

SELECT ContinentID, ContinentName FROM Continent;
SELECT CountryID, CountryName, ContinentID FROM Country ORDER BY CountryName;
SELECT TripID, CountryID, StartDate, EndDate, Price FROM Trip ORDER BY StartDate;