Add foreign keys to big data types such as varchar(2000) mysql tables
P粉378264633
2023-09-05 17:33:34
<p>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) </p>
<p>When I add a foreign key to "affiliate_sales" that references "_id"
go through
<code>Change table</code>affiliate_stats<code>Add constraint</code>fk_affili_sales<code>Foreign key (affiliate_sales) references transaction (</code>_id<code>);</code> </p>
<p>get me this error <code>Specified key was too long; max key length is 3072 by tes</code>
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. </p>
Change column
affiliate_sales
toVARCHAR(100)
.If it is a foreign key to
transaction(_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 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
andtransaction
. 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 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.
Now, insert a row in affiliate_stats_sales for each affiliate_sale that you want to add to the affiliate_stats row.
Sales and statistics data are related by joining this table. For example, if you want to see statistics for sales of 100 units.
A little difficult at first, but very powerful. This is how relational databases work.