CODEWITHSUNDEEP
mysql
Daniel
Daniel

Reputation: 103

MySQL error 'Key Column doesnt exist in table'

heres my input into the command line you can see I tried to add a foreign key in different ways and keep getting the same error what am I doing wrong?

mysql> create table membership(

-> m_no char(3) primary key,
-> m_fname varchar(15) not null,
-> m_lname varchar(15) not null,
-> m_street varchar(30) not null,
-> m_city varchar(20) not null,
-> m_st char(2) not null,
-> m_balance varchar(3));

Query OK, 0 rows affected (1.06 sec)

mysql> create table rental(

-> r_no char(4) primary key,
-> r_date date not null,
-> foreign key (m_no) references membership(m_no));
ERROR 1072 (42000): Key column 'm_no' doesn't exist in table

mysql> create table rental(
-> r_no char(4) primary key,
-> r_date date not null,
-> foreign key (m_no) references membership);
ERROR 1072 (42000): Key column 'm_no' doesn't exist in table

mysql> create table rental(
-> r_no char(4) primary key,
-> r_date date not null,
-> foreign key (m_no) references membership)
-> engine=innodb;
ERROR 1072 (42000): Key column 'm_no' doesn't exist in table


mysql> create table rental(

-> r_no char(4) primary key,
-> r_date date not null,
-> foreign key (m_no) references membership(m_no))
-> engine=innodb;
ERROR 1072 (42000): Key column 'm_no' doesn't exist in table

mysql> create table rental(

-> r_no char(4) primary key,
-> r_date date not null);

Query OK, 0 rows affected (0.22 sec)

mysql> alter table rental add foreign key (m_no) references membership(m_no);

ERROR 1072 (42000): Key column 'm_no' doesn't exist in table

mysql>

Upvotes: 1

Views: 9066

Answers (3)

Vaibhav Desai
Vaibhav Desai

Reputation: 2728

The error statement is clear. You do not have a column named 'm_no' in your rental table.

Is this what you were after:

Create table rental
(
    r_no char(4) primary key,
    r_date date not null,
    foreign key (r_no) references membership(m_no)
);

Upvotes: 2

Taryn
Taryn

Reputation: 247720

You have to change the foreign key reference to the r_no column in the rental table:

create table rental(
 r_no char(4) primary key,
 r_date date not null,
 foreign key (r_no) references membership(m_no));

See SQL Fiddle with Demo

Upvotes: 1

lc.
lc.

Reputation: 116498

It is exactly as the error message states: rental doesn't have a column named m_no.

Either reference an existing column (i.e. FOREIGN KEY (r_no) REFERENCES membership(m_no)), or add an m_no column to rental. I am not sure which you are trying to do.

Upvotes: 1

Related Questions