unique constraints for multiple columns in mysql

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, Sep 23 '18 at 17:09

Madhur Bhaiya · Accepted Answer · 2018-09-23T17:47:28.863

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 '18 at 17:47

answered Sep 23 '18 at 17:13

Madhur Bhaiya

28,155
10
49
57

but by this i can enter same barcode in same company_id and thats what i dont want to happen – firstName lastName Sep 23 '18 at 17:17
1

@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 – Madhur Bhaiya Sep 23 '18 at 17:18
but it doesnt allow duplicates companyID !!! – firstName lastName Sep 23 '18 at 17:24
@firstNamelastName It will allow. For eg: company id = 1, it will allow (1, 'abc') , (1, 'cdf'), (1, 'efg) etc – Madhur Bhaiya Sep 23 '18 at 17:25

unique constraints for multiple columns in mysql

1 Answers1