Question 20462 in Mysql Question posted in
The official MySQL documentation can be found here.

Add foreign key to large data type like varchar(2000) mysql table

I have two table ‘affiliate_stats’ and ‘transaction’,
‘affiliate_stats’ have column ‘affiliate_sales’ varchar(2000) (child table),
and ‘transaction’ have column ‘_id’ varchar(100) primary key (parent table)

when I add foerign key to ‘affiliate_sales’ referenced to ‘_id’
by
ALTER TABLE affiliate_stats ADD CONSTRAINTfk_affili_sales FOREIGN KEY (affiliate_sales) REFERENCES transaction(_id);

get me this error Specified key was too long; max key length is 3072 bytes
I know should two column size be equail, but in my case i need size different how to handle this problem also.
Searched in many sources and there is no clear answer and none of the solutions work for me.

Tags:

2

Answers


  1. 0

    Alter your column affiliate_sales to be VARCHAR(100).

    ALTER TABLE `affiliate_stats` 
  MODIFY COLUMN affiliate_tales VARCHAR(100) NOT NULL,
  ADD CONSTRAINT `fk_affili_sales` 
  FOREIGN KEY (affiliate_sales) 
  REFERENCES transaction(`_id`);

    If it’s a foreign key to transaction(_id), then it doesn’t need to be VARCHAR(2000) because it could never hold a string longer than 100 characters anyway.

    Before you do this, make sure there are no strings currently in that column longer than 100 characters.

    Re your comment:

    If you intend to store an "array" (i.e. a string with a comma-separated list) in the column, you should understand that you can’t make a foreign key on it anyway. A foreign key requires that the column relate one value to a row in the transaction table. You can only store one id in the column if you add a foreign key constraint to it.

    It sounds like you really have a many-to-many relationship between affiliate_stats and transaction. You need a third table to model a many-to-many relationship.

    Login or Signup to reply.
  2. 0

    Specified key was too long; max key length is 3072 byte

    varchar(2000) stores 2000 characters, but each character is not necessarily 1 byte. Simple European characters like a, 1, and ? are all one byte. However, ü or å or اَلْعَرَبِيَّةُ‎ take up multiple bytes.

    I know should two column size be equail, but in my case i need size different how to handle this problem also.

    This is possible, string types do not need to be of the same length.

    However, it is unnecessary. It is impossible to have a key larger than 100 characters. Your primary key is varchar(100), so no foreign key will ever be larger than 100 characters.

    However, you seem to want to store two different types of data in this column. One is some sort of array type, and one is a foreign key. You can’t. A foreign key ensures that every value has a matching primary key value.

    Instead, use an auto-incrementing primary key to link your tables together. It’s simpler, faster, uses less storage, unambiguous, and never changes.

    Add a new column as primary key to both tables. Then reference that.

    You can’t store multiple keys in one column. MySQL needs to be able to confirm that each foreign key has a matching primary key, and it can only do that by checking if they’re exactly equal.

    Instead, if each affilate_stats row is for multiple affiliate_sales, you need a join table. This is called a 1-to-many relationship. One affiliate_stats row relates to many affiliate_sales.

    -- I'm assuming we've changed to bigint primary keys.
create table affiliate_stats_sales (
    affiliate_sales_id bigint not null,
    foreign key(affiliate_sales_id) references affiliate_sales(id),

    affiliate_stats_id bigint not null,
    foreign key(affiliate_stats_id) references affiliate_stats(id)
);

    Now for every affiliate_sale that you want to add to a row of affiliate_stats, insert a row into affiliate_stats_sales.

    -- Relate affiliate_sale 100 to affiliate_stats 20
insert into affiliate_stats_sales(affiliate_sales_id, affiliate_stats_id) values (100, 20)

    Sales and stats are related by joining with that table. For example, if you want to see the stats for sale 100.

    select affiliate_stats.*
from affiliate_stats
join affiliate_stats_sales
  on affiliate_stats_sales.affiliate_stats_id = affiliate_stats.id
where affiliate_sales.id = 100

    It’s a bit difficult at first, but very powerful. That’s how relational databases work.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search