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
  • 18,365
  • 21
  • 80
  • 122
user3189916
  • 758
  • 1
  • 7
  • 26
  • Do **not** store comma separated values - just don't. It's bad design and you will *never* be able to apply proper foreign key constraints. –  Jun 26 '14 at 11:22
  • It is not a good idea. You shold normalize your database model. If you want to store the brand_id as a csv- value you have to remove the forein key. – Jens Jun 26 '14 at 11:23
  • removed foreign key. Thanks for the help – user3189916 Jun 26 '14 at 11:34

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
  • 201,996
  • 37
  • 293
  • 362