CODEWITHSUNDEEP
mysqlsqldatabasedatabase-administration
David Lemporo
David Lemporo

Reputation: 101

inserting into database with foreign key constraints

I am having trouble with the following issue:

I have two tables (for ex.)

 Table CARS:
 carID      |      Car Make
 1          |      Honda
 2          |      Misubishu

where carID is primary key

 Table MODELS
 modelID     |      mName       |      carID
 1           |      accord      |       1

where modelID is primary key and carID is foreign key (to CARS).

Now lets say that I want to insert Honda Civic into MODELS table.

I would insert civic and get the carID that matches Honda from the CARS table

I have been playing with INSERT SELECT statements, but I cannot get it to work; I have been doing something like this: https://dba.stackexchange.com/questions/46410/how-do-i-insert-a-row-which-contains-a-foreign-key

which seems to be exactly what I want but I still get sqlexceptions regarding f-key.

Thanks!

Upvotes: 1

Views: 1096

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

If modelID is auto_incremented you could do:

INSERT INTO models(mName,carID)
SELECT 'Civic',carID
FROM cars 
WHERE `Car Make` = 'Honda';

sqlfiddle demo

If modelID is not auto_incremented, you would have to pass the id also.

Upvotes: 1

Related Questions