Prevent insert of same values in MySQL database

Question

Let say that I have table TableA(ColumnCode, ColumnA2, ColumnA3, ColumnA4, ColumnA5).

Note: ColumnCode(PrimaryKey)is not autoincrement. What I want to prevent is following scenario:

INSERT INTO TableA VALUES(1111, 'val1a', 'val2a', 'val3a', 'val4a', 'val5a')

Next insert is with the same values but different ColumnCode:

INSERT INTO TableA VALUES(1234, 'val1a', 'val2a', 'val3a', 'val4a', 'val5a')

The thing is that I want to prevent inserts like this, where I might have these situations of inserting same values just for another ColumnCode.

Any idea?

Note2: Next insert is not a problem because I'm not inserting ALL the same column values!!!

INSERT INTO TableA VALUES(1456, 'val1a', 'val2a', 'val3a', 'val32a', 'val654a')

As shown, set(ColumnA2, ColumnA3, ColumnA4, ColumnA5) of values is not a duplicate. ColumnA4 and ColumnA5 values are different. So, for me, a duplicate is only when all four of ColumnA values are in table under any ColumnCode.

Hope I cleared my question a bit?

Sylca · Accepted Answer · 2012-10-10 11:01:01Z

2

You might want to take a look at using SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

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

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Have a look at the following example ....

SQL Fiddle DEMO

CREATE TABLE TABLE1(
  col1 varchar(50) PRIMARY KEY,
  col2 varchar(50),
  col3 varchar(50),
  col4 varchar(50),
  CONSTRAINT uc UNIQUE (col2,col3,col4)
 );

this should work:

INSERT INTO TABLE1 VALUES('1','1','1','1');
INSERT INTO TABLE1 VALUES('2','1','1','2');

this should fail:

INSERT INTO TABLE1 VALUES('1','1','1','1');
INSERT INTO TABLE1 VALUES('2','1','1','1');

edited Oct 10, 2012 at 11:01

Sylca

2,5554 gold badges34 silver badges52 bronze badges

answered Oct 10, 2012 at 6:33

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sylca Over a year ago

Updated my question. Ur opinion now?

Dmitrii Dovgopolyi · Accepted Answer · 2012-10-10 06:38:12Z

0

You can use triggers in your database.

The trigger is the code, which can run some code after the query or instead of query. You can see some examples here.

But as mentioned in another answers it is simplier to use UNIQUE constraints.

answered Oct 10, 2012 at 6:38

Dmitrii Dovgopolyi

6,2972 gold badges29 silver badges44 bronze badges

1 Comment

Sylca Over a year ago

Updated my question. Ur opinion now?

phininity · Accepted Answer · 2012-10-10 06:42:37Z

0

You should probably be able to add a multi-column unique constraint. Something Like:

alter table TableA add CONSTRAINT UNIQUE(ColumnA2, ColumnA3, ColumnA4, ColumnA5)

edited Oct 10, 2012 at 6:42

answered Oct 10, 2012 at 6:34

phininity

1,6832 gold badges14 silver badges9 bronze badges

Comments

Kailash Yadav · Accepted Answer · 2012-10-10 06:45:26Z

0

alter table TableA add CONSTRAINT UNIQUE(ColumnA2, ColumnA3, ColumnA4, ColumnA5)

answered Oct 10, 2012 at 6:45

Kailash Yadav

1,9402 gold badges18 silver badges37 bronze badges

Comments

Nirav Ranpara · Accepted Answer · 2012-10-10 06:49:20Z

0

Please remove all unique key

alter table TableA  drop unique key;

answered Oct 10, 2012 at 6:49

Nirav Ranpara

13.8k4 gold badges43 silver badges55 bronze badges

Comments

Leo · Accepted Answer · 2012-10-10 07:02:17Z

0

You can use one checksum column of all column which need to be unique. And update in on all insert and update. Check if there ckecksum column exists before you update and insert any new row.. In ms sql i would do it like this

    CREATE TABLE [dbo].[test]
    (
      [ID] [int] NOT NULL ,
      [Index] [tinyint] NOT NULL ,
      [C0] [real] NOT NULL ,
      [C1] [real] NOT NULL ,
      [C2] [real] NOT NULL ,
      [C3] [real] NOT NULL ,

    )
ON  [PRIMARY]

GO
ALTER test  ADD UQCHECKSUM AS BINARY_CHECKSUM(
[Index],
[C0] ,
[C1] ,
[C2] ,
[C3] ,
[) PERSISTED
Go  
ALTER TABLE test    ADD CONSTRAINT [UNK_UQCHECKSUM] UNIQUE (UQCHECKSUM)
GO

Pls check this for mysql http://www.bluegecko.net/mysql/using-checksums-to-ensure-table-consistency-in-mysql/

answered Oct 10, 2012 at 7:02

Leo

1091 silver badge8 bronze badges

1 Comment

Sylca Over a year ago

Updated my question. Ur opinion now?

solaimuruganv · Accepted Answer · 2012-10-10 10:23:53Z

0

column ColumnCode is PK so its onl;y accept unique value for this column,

all the column ColumnA1-5 are not unique column thats the reason it accepting all the duplicate value, if you want to avoid the duplicate entry in any of the column create unique key on tht column .

alter table tableA add unique index(ColumnA2, ColumnA3, ColumnA4, ColumnA5);

this would solve ur problem

edited Oct 10, 2012 at 10:23

answered Oct 10, 2012 at 6:36

solaimuruganv

30.3k1 gold badge22 silver badges23 bronze badges

1 Comment

Sylca Over a year ago

Updated my question. Ur opinion now?

Leo · Accepted Answer · 2012-10-10 10:31:09Z

0

CREATE TABLE [dbo].[test]
    (
      [ID] [int] NOT NULL ,
      [C1] [varchar] NOT NULL ,
      [C2] [varchar] NOT NULL ,
      [C3] [varchar] NOT NULL ,
      [C4] [varchar] NOT NULL ,
      [C5] [varchar] NOT NULL ,
      [ChecksumColumn] [varchar] NOT NULL //new column
    )

Create a insert and update trigger on test table to find the checksum of columns (C1,C2,C3,c4,c5) and insert into ChecksumColumn Use CRC32(C1,C2,C3,c4,c5) to get the check sum

Before insert and update check the below condition Select top 1 from test where ChecksumColumn = CRC32(C1,C2,C3,c4,c5)

if exist -- Duplicate records else Go ahead and update the ChecksumColumn with CRC32(C1,C2,C3,c4,c5)

answered Oct 10, 2012 at 10:31

Leo

1091 silver badge8 bronze badges

Collectives™ on Stack Overflow

Prevent insert of same values in MySQL database

8 Answers 8

SQL Fiddle DEMO

1 Comment

1 Comment

Comments

Comments

Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

1 Comment

1 Comment

Comments

Comments

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related