Add foreign keys to big data types such as varchar(2000) mysql tables

Question

I have two tables "affiliate_stats" and "transaction", 'affiliate_stats' has column 'affiliate_sales' varchar(2000) (subtable), and "transaction" has column "_id" varchar(100) primary key (parent table)

When I add a foreign key to "affiliate_sales" that references "_id" go through Change tableaffiliate_statsAdd constraintfk_affili_salesForeign key (affiliate_sales) references transaction (_id);

get me this error Specified key was too long; max key length is 3072 by tes I know both columns should be equal in size, but in my case I need different sizes to handle this. Searched in many sources, no clear answer, and none of the solutions worked for me.

Answer 1

Change column affiliate_sales to 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 is a foreign key to transaction(_id), then it does not need to be VARCHAR(2000) as it can never hold a string of more than 100 characters anyway.

Before performing this operation, make sure that there is currently no string in the column that is longer than 100 characters.

---

Reply to your comment:

If you plan to store "arrays" (i.e. strings with comma separated lists) in a column, you should understand that you cannot create a foreign key on it anyway. A foreign key requires a column to associate a value with a row in the transaction table. If you add a foreign key constraint, only one id can be stored in the column.

It sounds like there is indeed a many-to-many relationship between affiliate_stats and transaction. You need a third table to model the many-to-many relationship.

Answer 2

varchar(2000) Stores 2000 characters, but each character is not necessarily 1 byte. Simple European characters such as a, 1 and ? are all one byte. However, ü or å or َََُِِّْ‎ takes up multiple bytes.

---

It is possible that the string types do not need to be the same length .

However, this is not necessary. The key cannot exceed 100 characters. Your primary key is varchar(100), so the foreign key cannot exceed 100 characters.

However, it seems you want to store two different types of data in this column. One is a certain array type, and the other is a foreign key. you can not. Foreign keys Ensure that each value has a matching primary key value.

---

Instead, use auto-incrementing primary keys to link the tables together. It's simpler, faster, uses less storage, is unambiguous and never changes.

Add a new column as the primary key of both tables. Then quote it.

---

You cannot store multiple keys in a column. MySQL needs to be able to confirm that each foreign key has a matching primary key, and can only do this by checking that they are exactly equal.

Instead, if each affiliate_stats row corresponds to multiple affiliate_sales, you need a joined table. This is called a one-to-many relationship. One row of affiliate_stats is related 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, insert a row in affiliate_stats_sales for each affiliate_sale that you want to add to the affiliate_stats row.

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

Sales and statistics data are related by joining this table. For example, if you want to see statistics for sales of 100 units.

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

A little difficult at first, but very powerful. This is how relational databases work.