CODEWITHSUNDEEP
mysql
user3201500
user3201500

Reputation: 1618

Make a column UNIQUE with multiple column check

i am trying to create a MySQL table with name tbl_users. In which i have 2 columns mobile and altmobile, from these two columns i would like to make sure none of the Mobile number is repeated. Each number should be unique in both the column and should check from both the column before inserting into the table.

Is it possible to do?

I am new in these advance stuff. Rather then doing it on a server-side language (PHP) i want to handle this on database level itself, because this table will have more then 500k rows. So to make sure the process will run faster i would like to do it on database level itself.

Suppose i have a number 236463646 this number should be check in both the column mobile and mobilealt so the same number entry can not get reenter into the database.

Here is how my table structure looks like:

enter image description here

Thank you!

Upvotes: 4

Views: 62

Answers (2)

Walter_Ritzel
Walter_Ritzel

Reputation: 1397

So, you can write a trigger like below. This trigger is configure to be activated before the insert on the table occurs and check in a IF statement if the 2 mobile fields are equal. If they are, the system will throw an exception that will stop the insert process.

delimiter |

CREATE TRIGGER unique_email BEFORE INSERT ON tbl_users
  FOR EACH ROW
  BEGIN
     DECLARE msg varchar(255); 
     IF new.mobile = new.altmobile THEN
        SET msg = 'The mobile number must be unique.' 
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; 
     END IF;
  END;
|

Also, please read this material to understand the concept and how to implement: mysql triggers tutorial.

Upvotes: 0

Awita
Awita

Reputation: 334

I'd put the mobile numbers in one column on a separate table and reference them from the users table. This way you can easily apply a unique constraint on the mobile numbers column.

Upvotes: 2

Related Questions