SQL error code 1054 unknown column

Question

This is my query:

create table if not exists applications (
    idResearch int not null, 
    idProf char(10) not null,
    primary key (idResearch, idProf),
    foreign key (idResearch) references research(idResearch),
    foreign key (idProf) references professor(idProf)
);

and this is the table:

INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('10', '123456789');
INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('10', '432156789');
INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('10', '789654321');
INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('11', '876098432');
INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('11', '234567890');
INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('12', '432156789');
INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('12', '789654321');
INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('13', '876098432');
INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('14', '876098432');
INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('14', '987654321');
INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('15', '432156789');
INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('16', '123456789');
INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('16', '432156789');
INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('16', '789654321');
INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('17', '789654321');
INSERT INTO `university`.`applications` (`idResearch`, `idProf`) VALUES ('17', '432156789');

15:26:13 create table if not exists applications ( idResearch int not null, idProf char(10) not null, primary key (idResearch, idProf), foreign key (idResearch) references research(idResearch), foreign key (idProf) references professor(idProf) ) Error Code: 1215. Cannot add foreign key constraint 0.109 sec

And where are you getting the error? Note: I am a big fan of id columns being auto-incremented columns. — Gordon Linoff
0 118 15:01:50 INSERT INTO university.applications (idResearch, idProf) VALUES ('10', '123456789') Error Code: 1054. Unknown column 'idProf' in 'field list' 0.000 sec — Roy Azikri
I don't see anything wrong with your posted INSERT statement. are you sure there is no special character? — Rahul
Your code (with minor modifications) works on SQL Fiddle (sqlfiddle.com/#!9/25331). I suspect you have a bad character in the create table statement. Or two databases with different table structures. — Gordon Linoff

Syed Abubacker S Syed Abubacker S · Accepted Answer · 2015-06-05T06:37:44

There is no problem with the syntax in your query, it could be data issue.

Before executing the insert the query, check the following

Table 'research' & column 'idResearch' exist
Table 'professor' & column 'idProf' exist
The reference values (in the insert query) for the columns idResearch, idProf should exist in the research and professor tables resp.

Truncate\delete and drop the 'applications' table manually and then create it once again. Then execute your insert query.

SQL error code 1054 unknown column

2 Answers