Add constraint so the column accepts only comma separated values from another column

Question

I have these two tables:

CREATE TABLE `car_shop` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `selling_brands` varchar(25) DEFAULT NULL,
  `some_col` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `cars` (
  `car_id` int(11) NOT NULL AUTO_INCREMENT,
  `car_make` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`car_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

The second one is populated with following:

+--------+----------+
| car_id | car_make |
+--------+----------+
|      1 | BMW      |
|      2 | Audi     |
|      3 | Toyota   |
+--------+----------+

I need to add such a constraint that column selling_brands would accept only values populated in car table (BMW, Audi, Toyota).

I imagine it that way:

+----+----------------+----------+
| id | selling_brands | some_col |
+----+----------------+----------+
|  1 | BMW, Audi      | shop 1   |
|  2 | Toyota         | shop 2   |
+----+----------------+----------+

I was trying to add constraint as follows, but it doesn't work:

ALTER TABLE car_shop
ADD FOREIGN KEY (selling_brands) 
REFERENCES cars(car_make)

I'm getting: ERROR: (conn:24) Cannot add foreign key constraint Error Code: 1215

Possible duplicate of [Cannot add foreign key constraint - MySQL ERROR 1215 (HY000)](https://stackoverflow.com/questions/29248057/cannot-add-foreign-key-constraint-mysql-error-1215-hy000) — Nick, Apr 25 '18 at 23:49
You're telling us your second mistake, your first is trying to put comma separated values in a field. — Uueerdo, Apr 25 '18 at 23:53
No, your first mistake is trying to break 3NF. Don't do this. — user207421, Apr 26 '18 at 00:10

score 1 · Accepted Answer · answered Apr 25 '18 at 23:58

Putting comma-separated values, or any sort of lists really, into a database is almost always a bad idea. Usually, when you think you need a "list", what you really need is another table; in your case:

cars
- car_id
- info about the car

shops
- shop_id
- info about the shop

car_shop
- car_id
- shop_id

If a shop "has" more than one car, more than one row in car_shop will reference its shop_id. If a car is in more than one shop, more than one row in car_shop will have its car_id. car_shop could also be used to contain information such as quantity, price, etc...

Add constraint so the column accepts only comma separated values from another column

1 Answers1