CODEWITHSUNDEEP
mysqlsqldatabase
Devendra
Devendra

Reputation: 1884

reference key insertion in mysql database

i've 2 tables structure line show in image.

NG_SCHOOL.PHONE_ID is a foreign key to NG_PHONE_NUMBER.PHONE_NUMBER_ID, as shown in the schema.

enter image description here

now i want to insert data in table using following query

INSERT INTO ng_school (SCHOOL_ID, SCHOOL_SYSTEM_ID, NAME, ZIP, CITY, PHONE,  LEAGUE_NAME, MINIMUM_GRADE_ID, MAXIMUM_GRADE_ID) VALUES ('testSchoolA','testSchoolSystem1','Elementary School A','90210','City of Los Angeles','213 555 1000','School A Athletic','K','GRADE6');

but it gives error

Error Code: 1054 - Unknown column 'PHONE' in 'field list'

how to insert this data into table?

Upvotes: 1

Views: 54

Answers (2)

Steven Ryssaert
Steven Ryssaert

Reputation: 1967

With your query, you are trying to insert a phone number in a column that doesn't exist in the table ng_school. ng_school only contains a REFERENCE to a phone number(phone_id) , but not the phone number itself.

What you'll need to do is insert the phone number first in ng_phone_number, get the PHONE_NUMBER_ID value and insert that in ng_school.

In short:

1/ INSERT INTO ng_phone_number ....

2/ SELECT LAST PHONE_NUMBER_ID or LAST_INSERT_ID()

3/ INSERT INTO ng_school(PHONE_ID) VALUES ([phone_number_id goes here])

Upvotes: 3

Manoj Purohit
Manoj Purohit

Reputation: 3453

INSERT INTO ng_school (SCHOOL_ID, SCHOOL_SYSTEM_ID, NAME, ZIP, CITY, **PHONE**,  LEAGUE_NAME, MINIMUM_GRADE_ID, MAXIMUM_GRADE_ID) VALUES ('testSchoolA','testSchoolSystem1','Elementary School A','90210','City of Los Angeles','213 555 1000','School A Athletic','K','GRADE6');

Cause you don't have phone column in your table.. it's PHONE_ID

Upvotes: 2

Related Questions