Change columnaffiliate_salestoVARCHAR(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 totransaction(_id), then it does not need to beVARCHAR(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 associateavalue with a row in thetransactiontable. 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 betweenaffiliate_statsandtransaction. You need a third table to model the many-to-many relationship.

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 thatthe string types do not need to be the same length.

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

However,it seems you want to store two different types of datain this column. One is a certain array type, and the other is a foreign key. you can not.Foreign keysEnsurethat eachvalue has a matching primary key value.

---

Instead, useauto-incrementing primary keysto 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 ajoined 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.