I have a MySQL INNOB table with 20 columns and I want to add a foreign key constraint to around 10 of the columns.
I know I can create 10 separate tables with the foreign key data and then add these as constraints to the initial table - this is possible and I've done this before.
Question - this will result in 10 extra tables. Is it possible to combine these 10 tables (with foreign key data) into 1 table and then reference the table/column as the constraint?
I ask as I could see my database ending up with a lot of foreign key tables.
You could theoretically use one table, but this would be a very bad design, as it would not provide a simple enumerated "domain" for each unique data type, and it would multiply each of your values for each unique combination of all the other columns in the table, or in other words, approximately to the 10th power of the average number of elements per column.
To put this into concrete terms, if you have only 5 unique values per column, and 10 columns, that would result in a table with 9,765,625 records!!! I'm sure you see this would be terribly inefficient!
This multiplication of data would be necessary to prevent extraneous NULL or empty string values in your columns, which would corrupt your foreign key relationship.
Creating a distinct "domain table" for each unique set of data is common, and if you have only 10 of them in your database, that's pretty "light".
If you don't want domain tables for each constraint, you could always use ENUM('value1', 'value2', ...) as your column data-type instead of a foreign key (which would not require you to create any additional tables).