CODEWITHSUNDEEP
mysqlsqldatabaseforeign-keysforeign-key-relationship
Menderft
Menderft

Reputation: 41

Finding the value of a primary key before inserting something to child table with foreign key referencing to it

Lets say I have two tables: Department and employee. I want to insert an employee with a foreign key referencing to a particular department. Primary key of department table is some random auto incremented column. Should I need to combine my insert query with another query like select idDepartment from department where dname = R&D, if so how is this accomplished. Additionally, is there any other alternative way?

Upvotes: 0

Views: 109

Answers (1)

kiks73
kiks73

Reputation: 3758

You can do it with a single insert statement like this, assuming the table has only the fields NAME and DEP_ID 

INSERT INTO EMPLOYEE (EMP_NAME, DEP_ID) SELECT 'employee name',IDDEPARTMENT FROM DEPARTMENT WHERE DNAME = 'R/A'

Upvotes: 0

Related Questions