MySQL column depending on another column

258
May 21, 2017, at 11:52 PM

I have a table in MySQL like this

ID  |  Attribute1  |  Attribute2
--- | ------------ | ------------
15  |  value1      | value2

and then I have another table like this:

ID  |  Attribute  
--- | ------------ 
15  |  value1
15  |  value2

Is there a way to have Attribute1 in table 1 to be choosable only among the corresponding values of the other table?

Let's do an example:

ID  |  Attribute
--- |  -----------
 1  |  Red
 1  |  Yellow
 2  |  Red
 2  |  Blue
ID         |  Attribute1
---------------------------
Apple      |   Red
BlueBerry  |   Blue

I don't want to be possible to choose "blue" for the "Apple".

Answer 1

You can add a constraint:

ALTER TABLE table1 ADD CONSTRAINT fk_t1_attr1 FOREIGN KEY (id, attribute1)
                                              REFERENCES table2 (id, attribute);

Thus you are only allowed to enter id/attribute pairs that exist in table1.

READ ALSO
Importing CSV data using PHP/MySQL

Importing CSV data using PHP/MySQL

I'm having a bit of a problem trying to import data from a CSV and have a couple of questions on it that I haven't managed to solve myself yet

349
Mysql is not running

Mysql is not running

I installed directadmin on ubuntu server, mysqlsock location is in /tmp folder

400
SQL - Insert data alternate days [on hold]

SQL - Insert data alternate days [on hold]

Currently I am developing an app for my friend's startupThe company distributes milk to users based on different plans like monthly plan, alternate days plan etc

261