Can I cancel an INSERT statement if one of the columns is duplicate-CodePudding

Imagining I have these two INSERT statements, the first one was yesterday and the second one is today:

INSERT INTO table(id, field1, field2, field3) 
VALUES (1, "John", "Doe", "12345")

INSERT INTO table(id, field1, field2, field3) 
VALUES (1, "Mary", "May", "12345")

Is there a way to make these INSERT statements not insert rows if there is already an equal value in any row in field3? This means the second record wouldn't be inserted.

I have searched for this but only found cases where they use the primary key as comparison.

CodePudding user response：

you can use unique

ALTER TABLE table ADD UNIQUE (field3);

The UNIQUE constraint ensures that all values in a column are different.

Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint.

CodePudding user response：

You can use INSERT with NOT EXISTS to achieve this for example:

INSERT INTO table (id, field1, field2, field3)
SELECT * FROM (SELECT 1  as id, 'John' as field1, 'Doe' as field2, '12345' as field3) AS tmp
WHERE NOT EXISTS (
    SELECT id FROM table WHERE field3 = '12345'
);

INSERT INTO table (id, field1, field2, field3)
SELECT * FROM (SELECT 1  as id, 'Mary' as field1, 'May' as field2, '12345' as field3) AS tmp
WHERE NOT EXISTS (
    SELECT id FROM table WHERE field3 = '12345'
);