1

I am having two tables brand and vendors.

The brand table is as follows (brand_id is VARCHAR):

brand_id      brand_name
---------    --------------
01               KFC
02               MCD
03               Cream stone

I created another table vendor which has brand_id as foreign key with statement:

CREATE TABLE vendor(vendor_id VARCHAR(20), 
        name VARCHAR(50), 
        brand_id VARCHAR(10), 
        PRIMARY KEY (vendor_id), 
        FOREIGN KEY(brand_id) REFERENCES vendor_brand(brand_id));

Now my requirement is i want to store comma separated brand_id's in vendor table as:

vendor_id         name                  brand_id
---------    --------------          ---------------
1               Hi Bakers               01, 02
2               Test Confectioners      02
3               Cream Parlour           01, 02, 03

Iam getting Error #1452 on inserting. How to make comma separated values?

Sadikhasan
  • 17,858
  • 20
  • 77
  • 117
user3189916
  • 718
  • 1
  • 6
  • 24

1 Answers1

6

That does not work to store multiple foreign keys in a column. And you should absolutely not do it even if it weren't keys. Never, never, never store multiple values in one column! This will only give you headaches with your future selects and it is really slow performance-wise.

A better approach would be to have another new table called 

vendor_brands
-------------
vendor_id
brand_id
juergen d
  • 195,137
  • 36
  • 275
  • 343