unique constraints for multiple columns in mysql [duplicate]

Question

Guys i have that table in DB

-------------------------------------------------------------
ID | COMPANY_ID | NAME | DESCRIPTION | BARCODE 
-------------------------------------------------------------

i want to make a constraints that unique barcodes should be enetered with same companyID .... so how to do that in mysql ??

NB: i cam enter multiple company_id in the table

Can same company_id be entered multiple times in the table ? — Madhur Bhaiya
– Madhur Bhaiya, Commented Sep 23, 2018 at 17:09

Madhur Bhaiya · Accepted Answer · 2018-09-23 17:47:28Z

7

You need to add a Composite UNIQUE constraint on COMPANY_ID and BARCODE, to your table. This basically means that it will not allow rows having duplicate combination of values, for the said two fields. It can still allow duplicate values individually for either of them.

Eg: (1, 'abc') combination will not be able to exist in more than one row. However, (1, 'abc'), (1, 'def') can exist. So it allows duplicate values individually (Company_id in this example)

Do the following (change table and column name(s) accordingly):

ALTER TABLE your_table_name 
ADD CONSTRAINT unique_barcode_company UNIQUE (COMPANY_ID, BARCODE)

edited Sep 23, 2018 at 17:47

answered Sep 23, 2018 at 17:13

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

firstName lastName Over a year ago

but by this i can enter same barcode in same company_id and thats what i dont want to happen

Madhur Bhaiya Over a year ago

@firstNamelastName No it wont allow same barcode for same company_id, because they will be duplicates then, and this constraint stops duplicate(s) to be created in the table

firstName lastName Over a year ago

but it doesnt allow duplicates companyID !!!

Madhur Bhaiya Over a year ago

@firstNamelastName It will allow. For eg: company id = 1, it will allow (1, 'abc') , (1, 'cdf'), (1, 'efg) etc

Collectives™ on Stack Overflow

unique constraints for multiple columns in mysql [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related