0

I have three relations:

Recipe(id:Int, name:String), Wine(maker:String, name:String, year:Int, varietal:String, description:String), and DessertWine(maker:String, name:String, year:Int, sweetness:Int). DessertWine ISA Wine.

I setup a foreign key:

ALTER TABLE DessertWine
ADD FOREIGN KEY (maker, name, year) references Wine (maker, name, year)
ON DELETE CASCADE;

To add a DessertWine row, the fields would be maker, name, year, varietal, description, sweetness), but these are split between Wine and DessertWine. Manually, I would add the Wine row first, then add the DessertWine row. Wondering how I would populate DessertWines from a data file using LOAD DATA INFILE. Is there some way to add a record to both tables at the same time, perhaps using a trigger?

jonathannah
  • 21
  • 4
  • I tried to use HTML underline to identify the primary keys, but did not work. For Recipe, the key is 'id. For Wine and DessertWine, the keys are 'maker', name', 'year'. – jonathannah Nov 29 '17 at 14:25
  • 1
    I think that probably you should choose one question at a time. Anyway, please [edit] the post to tag what DBMS you are using. – underscore_d Nov 29 '17 at 14:44
  • 1
    Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – underscore_d Nov 29 '17 at 14:44
  • 1
    My first question was answered in the article you shared, so I trimmed to a single question about loading data into the table. – jonathannah Nov 29 '17 at 14:53
  • Hi. Google your last sentence with various subsets of tags. Clearly this is not the first time someone has been in this situation. PS Underlining PKs is not particularly helpful, just use words to give PK, CKs, UNIQUEs, NOT NULLs, FKs & other constraints. But read the edit help re simple format options. You should give DDL anyway--see [mcve]. – philipxy Dec 02 '17 at 01:31

0 Answers0