How do I find which unique index has failed from within a stored procedure?
I have created a table with a single column primary key and three single column unique indexes:
CREATE TABLE tableName (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
col1 VARCHAR(50) NOT NULL,
col2 VARCHAR(50) NOT NULL,
col3 VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
UNIQUE INDEX id_UNIQUE (id ASC),
UNIQUE INDEX col1_UNIQUE (col1 ASC),
UNIQUE INDEX col2_UNIQUE (col2 ASC),
UNIQUE INDEX col3_UNIQUE (col3 ASC)
) ENGINE = InnoDB;
I have create a stored procedure that handles Error '1062': 'Duplicate entry %s for key %d':
DELIMITER $$
CREATE PROCEDURE insertRecord (
col1Value VARCHAR(50),
col2Value VARCHAR(50),
col3Value VARCHAR(50),
OUT idValue INT,
OUT errColName VARCHAR(50)
) BEGIN
DECLARE EXIT HANDLER FOR 1062 BEGIN
SET errColName = 'Insert Column Name Here';
END;
INSERT tableName SET
col1 = col1Value,
col2 = col2Value,
col3 = col3Value;
SET idValue = LAST_INSERT_ID();
END;
$$
DELIMITER ;
I have called the stored procedure 5 times with different data to return various results:
1st INSERT attempt is successful
-- Initialize parameters
SET @col1Val = 'MySQL',
@col2Val = 'Is',
@col3Val = 'Cool',
@Id = NULL,
@ErrColName = NULL;
-- 1st Insert Attempt
CALL insertRecord(@col1Val, @col2Val, @col3Val, @Id, @ErrColName);
-- Expected result: @Id = 1, @ErrColName = NULL
SELECT @Id '@Id', @ErrColName '@ErrColName';
2nd INSERT Attempt fails because col1 is not unique
-- Re-initialize parameters
SET @col1Val = 'MySQL', -- Intended to generate an error
@col2Val = 'Is',
@col3Val = 'Cool',
@Id = NULL,
@ErrColName = NULL;
-- 2nd Insert Attempt
CALL insertRecord(@col1Val, @col2Val, @col3Val, @Id, @ErrColName);
-- Expected result: @Id = NULL, @ErrColName = 'col1'
SELECT @Id '@Id', @ErrColName '@ErrColName';
3rd INSERT Attempt fails because col2 is not unique
-- Re-initialize parameters
SET @col1Val = 'SQL',
@col2Val = 'Is', -- Intended to generate an error
@col3Val = 'Cool',
@Id = NULL,
@ErrColName = NULL;
-- 3rd Insert Attempt
CALL insertRecord(@col1Val, @col2Val, @col3Val, @Id, @ErrColName);
-- Expected result: @Id = NULL, @ErrColName = 'col2'
SELECT @Id '@Id', @ErrColName '@ErrColName';
4th INSERT Attempt fails because col3 is not unique
-- Re-initialize parameters
SET @col1Val = 'SQL',
@col2Val = 'For',
@col3Val = 'Cool', -- Intended to generate an error
@Id = NULL,
@ErrColName = NULL;
-- 4th Insert Attempt
CALL insertRecord(@col1Val, @col2Val, @col3Val, @Id, @ErrColName);
-- Expected result: @Id = NULL, @ErrColName = 'col3'
SELECT @Id '@Id', @ErrColName '@ErrColName';
5th INSERT Attempt is successful
-- Re-initialize parameters
SET @col1Val = 'SQL',
@col2Val = 'For',
@col3Val = 'Life',
@Id = NULL,
@ErrColName = NULL;
-- 5th Insert Attempt
CALL insertRecord(@col1Val, @col2Val, @col3Val, @Id, @ErrColName);
-- Expected result: @Id = 5, @ErrColName = NULL
SELECT @Id '@Id', @ErrColName '@ErrColName';
What do I need to do in the stored procedure to find which unique index has failed from within a stored procedure?
Thanks in advance.
I found the answer under Which unique key is hit with my insert?. It is not ideal however does solve the problem.
The following is the applied solution:
...
DECLARE EXIT HANDLER FOR 1062 BEGIN
-- Check if col1Value is already in use
IF EXISTS (
SELECT 1
FROM tableName
WHERE col1 = col1value
LIMIT 1
) THEN
SET errColName = 'Col1';
-- Check if col2Value is already in use
ELSEIF EXISTS (
SELECT 1
FROM tableName
WHERE col2 = col2value
LIMIT 1
) THEN
SET errColName = 'Col2';
-- Check if col3Value is already in use
ELSEIF EXISTS (
SELECT 1
FROM tableName
WHERE col3 = col3value
LIMIT 1
) THEN
SET errColName = 'Col3';
END IF;
END;
...