CODEWITHSUNDEEP
mysql
woninana
woninana

Reputation: 3481

Key column 'cat_id' doesn't exist in table

I have table category with

cat_id
category

and table products with

prod_id
products

and another table product_category with

pkey
index

I have tried to add foreign keys on product_category with this:

ALTER TABLE product_category
ADD FOREIGN KEY (cat_id)
REFERENCES categories(cat_id)

what is the problem?

Upvotes: 1

Views: 4542

Answers (5)

fede
fede

Reputation: 11

Here it is how should be fixed :

we have two tables,

table1( id_table1 int primary key, field int)ENGINE=INNODB;
table2( id_table2 int primary key, id_table1 int, field2 int)ENGINE=INNODB;

if you want to put the primary key of table1 in table2, you have to declare in table2 the id_table1

Like this :id_table1 integer

and then write your foreign key(id_table1) references table1(id_table1)

Upvotes: 1

Eljakim
Eljakim

Reputation: 6937

The issue is that your table is called category (singular) and you are trying to reference a table categories (plural), on top of that you are not using the field name from the table product_category.

See the following example that does what you want. (I have changed the fields name from product_category around so it is more clear what to use where).

mysql> create table category (cat_id int, category varchar(200));
Query OK, 0 rows affected (0.39 sec)

mysql> create table products (prod_id int, product varchar(200));
Query OK, 0 rows affected (0.40 sec)

mysql> create table product_category (pc_prod_id int, pc_cat_id int);
Query OK, 0 rows affected (0.13 sec)

mysql> alter table product_category add foreign key (pc_cat_id) references category(cat_id);
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

Upvotes: 1

Rukmi Patel
Rukmi Patel

Reputation: 2561

Your table product_category does not contain column that you have specified in query.. add it and run this query ....

there is no issue with query ..

Upvotes: 0

Jacob
Jacob

Reputation: 43229

Your table product_category does not have a cat_id field, so you cannot add a reference. Add that field with the same datatype categoriy.cat_id has and try again.

What storage engine do you use? Keep in mind that MyISAM does not support checking for foreign key constraints.

I guess pkey is supposed to be a reference to prod_id? Naming it prod_id or something similar would make it clearer what it is referencing.

Upvotes: 4

Ovais Khatri
Ovais Khatri

Reputation: 3211

You must add cat_id first in the table, and then try to add foreign key reference.

alter table Table_name
add col_name data_type constraints

Upvotes: 1

Related Questions