mySQL Unique constraint

Question

I've a simple table with 3 columns .

| prefix | phoneNumber | verifiedDate |
------------------------------------------

verifiedDate could hold Null

how can I force this constraint on my database? One row for every UNIQUE prefx & phoneNumber exist if verifiedDate is not NULL.

duplication of prefx & phoneNumber are only allowed if there is no row that hold prefx & phoneNumber and verifiedDate is not NULL!

Would be so easy with other DBMS, but MySQL does not support partial indexes. — , Mar 15 '12 at 09:06

Justin Pihony · Answer 1 · 2012-03-15T01:17:54.397

0

The only way that I know to do this is by using SQL Triggers.

Here are some good resources for mysql specifically:

MySQL documentation
MySQL example half way down the page is the code

edited Mar 15 '12 at 01:17

answered Mar 15 '12 at 00:54

Justin Pihony

66,056
18
147
180

I tried that but i think i have a syntax error CREATE TRIGGER upd_check AFTER UPDATE ON phonenumbers DELETE FROM `phonenumbers` WHERE `prefix`=new.prefix AND `phoneNumber`=new.phoneNumber AND `verifiedDate` IS NULL; – nullException Mar 15 '12 at 01:12
Updated my answer with new links. It appears that in mySQL you have to say ... ON [TABLE] FOR EACH ROW [trigger stuff]. Otherwise this is fine – Justin Pihony Mar 15 '12 at 01:18
#1442 - Can't update table 'phonenumbers' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. – nullException Mar 15 '12 at 01:31
Hrmmm, it appears that you have to resort to some trickery to accomplish this in mySQL ( http://stackoverflow.com/questions/229765/triggers-that-cause-inserts-to-fail-possible )..and I would suggest using the BEFORE UPDATE so that the update basically fails. – Justin Pihony Mar 15 '12 at 01:39

score 0 · Answer 2 · answered Mar 15 '12 at 08:44

The general idea is to write a query that returns 'bad data' then in a trigger test that this query is the empty set e.g. something like

DECLARE unique_tally INT;
SET unique_tally = 0;  

SELECT COUNT(*) 
  INTO unique_tally
  FROM (
        SELECT prefix, phoneNumber, COUNT(*) AS tally
          FROM SimpleTable
         WHERE verifiedDate IS NOT NULL
         GROUP 
            BY prefix, phoneNumber
       ) AS DT1
 WHERE tally > 1;

IF ( unique_tally > 0 ) THEN
   -- Fail

mySQL Unique constraint

2 Answers2