0

Suppose, I have item(name, code, price, provider) and provider(name, code, address)

I make item table using 

CREATE TABLE item 
(
  name varchar(50) NOT NULL, code varchar(30) NOT NULL, price float NOT NULL, provider varchar(50) not null,
  PRIMARY KEY(code),
  foreign key(provider) references provider(name)
);

But, now I want something like foreign key(provider) references provider(code).

273K
  • 29,503
  • 10
  • 41
  • 64
  • Can you give some examples of what you've tried to achieve this, and what effect you're trying to achieve? – rwp Mar 31 '18 at 04:51
  • Check that all the conditions for creating a FK are met - https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html – P.Salmon Mar 31 '18 at 10:56

2 Answers2

1

If you need to remove the previous constraint you can do:

ALTER TABLE item
drop foreign key item_ibfk_1

'item_ibfk_1' is the fk symbol corresponding to your original constraint. To find this fk symbol for your table, you can run following command:

SHOW CREATE table item;

Then you need to add the new constraint

ALTER TABLE item
ADD FOREIGN KEY(provider) REFERENCES provider(code);
appsdownload
  • 751
  • 7
  • 20
  • `mysql> alter table item add foreign key(provider) references provider(code); ERROR 1005 (HY000): Can't create table 'handicraftstore.#sql-52d_7b' (errno: 150)` –  Mar 31 '18 at 10:15
  • That is a different error. I tried the query that I posted, and it worked for me. Maybe this can help you: [link] (https://stackoverflow.com/questions/2799021/mysql-error-1005-hy000-cant-create-table-foo-sql-12c-4-errno-150) – appsdownload Mar 31 '18 at 16:41
0

You can do:

ALTER TABLE item
ADD FOREIGN KEY(code) REFERENCES provider(code);

Note: You have to make sure that the provider.code and provider.name are unique keys. MySql allows foreign keys constraints only for unique or primary key columns.

appsdownload
  • 751
  • 7
  • 20
  • `item.code` is not foregn key, it is primary key. `item.provider` is foreign key referencing `provider.code` –  Mar 31 '18 at 05:19
  • I have commented another answer. Please check it out and let me know if it was helpful. – appsdownload Mar 31 '18 at 06:11