CODEWITHSUNDEEP
mysql-workbench
Amanda Ramos
Amanda Ramos

Reputation: 29

I am gettng the error code 1170 when I try to alter a table to add a foreign key

I am trying to add a foreign key to a table but I am getting the following error message: Error Code 1170 BLOB/TEXT column 'member_id' used in key specification without a key length.

here is what I have

ALTER TABLE fact_pharmacy
ADD CONSTRAINT fk_members FOREIGN KEY (member_id)
REFERENCES dim_members(member_id) 
ON DELETE CASCADE
ON UPDATE CASCADE;

dim_member enter image description here

fact_pharmacy enter image description here

Upvotes: 0

Views: 243

Answers (1)

jurez
jurez

Reputation: 4667

What is happening here is that when you are creating a foreign key you are actually creating an index in the background.

MySQL is designed in such a way that keys that make up an index must have a limited length (in the range of few hundred bytes). Columns of TEXT/BLOB are designed to support very large, gigabyte-sized data. So MySQL is kindly telling you to make up your mind - either confirm that you are fine with taking just a small part of your blobs for index, or change it into something that would actually fit, say VARCHAR.

You can, of course, do that, but I strongly suspect that whoever designed your tables did a terrible job by choosing TEXT/BLOB for member_id. So instead of trying to fix this error I would rather fix the underlying issue and redesign the schema to use short and efficient keys for member_id.

(EDIT: after you updated your question and posted the schema, you can see the problem - change member_id in fact_pharmacy to be the same type as in dim_members, that is, int).

Upvotes: 1

Related Questions