0

Got an odd problem I cant solve after browsing dozens of forum posts, and my local SQL Books.

I've got two tables, and want to add a foreign key to one of them. The foreign key and primary key share the same datatype and charset and yet I cannot add the Foreign Key at all.

addon_account

name type comments
id int(11) Primary Key
name varchar(60) Primary Key
label varchar(255)
shared int(11)

addon_account_data

name type comments
id int(11) Primary Key
account_name varchar(60) Primary Key
money double
owner varchar()

The query I ran:

ALTER TABLE `addon_account_data` ADD FOREIGN KEY (`account_name`) REFERENCES `addon_account`(`name`) ON DELETE RESTRICT ON UPDATE RESTRICT;

Can't get it to work. Tosses out the same issue the entire time.

shaedrich
  • 5,457
  • 3
  • 26
  • 42
Wellendox
  • 13
  • 1
  • 3
  • 1
    Can you please run `show create table addon_account` and `show create table addon_account_data` and add the results to your question? – GMB Oct 22 '19 at 19:44
  • 1
    InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order‘ - https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html – P.Salmon Oct 22 '19 at 20:50
  • This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS A FK constraint says subrows in a table appear elsewhere as PK/UNIQUE. So your declaration doesn't make sense. How did you think you were constraining the DB? – philipxy Oct 22 '19 at 23:43

3 Answers3

0

I am not exactly a MySQL guy, but:

I believe the problem is that you are referencing only part of the primary key: Your table addon_account has a composite key PK(id, name).

So, to put your relationship to work, you will need to add 'account_id' as part of the foreign key as well:

ALTER TABLE addon_account_data ADD FOREIGN KEY (account_id, account_name) REFERENCES addon_account(id, name)

This thread deals with something similar.

I hope this helps.

EDITED

I have installed a MySQL server instance on my local machine... (MySQL 8).

I have run the script below, and it worked (giving warnings about integer display being a deprecated feature, so I would recommend ommitting it):

CREATE TABLE addon_account(
id INT(11) NOT NULL,
`name` VARCHAR(60) NOT NULL,
label VARCHAR(255),
shared INT(11),
CONSTRAINT pk_addon_account PRIMARY KEY(id, `name`));


CREATE TABLE addon_account_data (
id INT(11) NOT NULL,
account_name VARCHAR(60) NOT NULL,
account_id INT(11),
money DOUBLE,
`owner` VARCHAR(255),
CONSTRAINT pk_addon_account_data PRIMARY KEY(id, account_name),
CONSTRAINT fk_addon_account_account_data FOREIGN KEY(account_id, account_name)
    REFERENCES addon_account(id, `name`));

Could you try it and see if this works for you?

I am not that familiar with MySQL.

Diego Grigol
  • 131
  • 6
  • Does not seem to work, however I nailed the problem down. If I remove the PK id, it works. If somebody has an answer on how to add a FK on a composite PK it'll be great though! – Wellendox Oct 22 '19 at 19:55
  • This is really odd, then. I have no MySQL database here, I will to the table creation at night (Brazil), and then edit my answer with whatever I find. – Diego Grigol Oct 22 '19 at 19:58
  • Having dealt with MySQL Databases for a few years now this is frustrating and confusing me aswell. Though I wont stop testing around here rn. I need this to work.. got about 50 more tables to go through, and every table with composite pk's has this issue. – Wellendox Oct 22 '19 at 20:00
  • If I can give you an advice.... I have made this script reflecting the very same schema you exposed in your question. In order to avoid some headaches in the future, I would make the "addon_account_data" PRIMARY KEY containing the "account_id" attribute as well, if different account ids can use the same account name. – Diego Grigol Oct 22 '19 at 20:53
0

You are creating a foreign key on addon_account_data(account_name) that references addon_account(name). You have a composite primary the referred table : addon_account(id, name).

This is not allowed in MySQL, as explained in the documentation:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.

Possible solutions:

  • add an additional column in the referring table: addon_account_data(account_id, account_name) and create a composite primary key to the corresponding columns in addon_account

  • create an index on addon_account(name) (probably the simplest solution)

  • change the order of the columns in the primary key of the referred table, like: addon_account(name, id) (you might want to first consider the impacts this may have in terms of performance)

GMB
  • 216,147
  • 25
  • 84
  • 135
0

make sure that the 2 tables have the same collation like COLLATE='utf8_general_ci'

Aymanbacha Bacha
  • 1
  • 1