[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

table duplicates

Posted on 2011-04-28
3
Medium Priority
?
383 Views
Last Modified: 2012-05-11
hi, i am trying to set a table in mysql with unique index on a substring of a column value - is that possible.
reason is to avoid duplicates entered in the table. Sample duplicate row:
ref#(varchar),datetime(varchar),location(varchar),destination(varchar)
0023,281101,flo,vir
0023,281130,flo,vir
unique(ref#,substr(datetime,1,4),location,dest) ?
0
Comment
Question by:nevyana2006
  • 2
3 Comments
 

Author Comment

by:nevyana2006
ID: 35484061
thank you, i found that for char and varchar i can use column(len) which worked for me.
0
 
LVL 3

Expert Comment

by:ssisworo
ID: 35484082
If indeed the problem is to maintain the uniqueness of the data. Perhaps by creating a trigger to help, as follows :

------------------------------

CREATE TRIGGER dbo.TRG_TABLEDUP_01 ON dbo.TABLEDUP
AFTER INSERT
AS
DECLARE @creditrating tinyint, @vendorid int;
IF EXISTS (SELECT *
           FROM dbo.TABLEDUP p
           JOIN inserted AS i
           ON p.REFF = i.REFF AND
              substring(p.DATETIME,1,4) = substring(i.DATETIME,1,4) AND
              p.LOCATION = i.LOCATION AND
              p.DESTINATION = i.DESTINATION  
          )
BEGIN
RAISERROR ('Duplicate Constrain by trigger.', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;

------------------

I hope I can help
0
 
LVL 3

Accepted Solution

by:
ssisworo earned 2000 total points
ID: 35484094
If indeed the problem is to maintain the uniqueness of the data. Perhaps by creating a trigger to help, as follows :

------------------------------

CREATE TRIGGER dbo.TRG_TABLEDUP_01 ON dbo.TABLEDUP
AFTER INSERT
AS
IF EXISTS (SELECT *
           FROM dbo.TABLEDUP p
           JOIN inserted AS i
           ON p.REFF = i.REFF AND
              substring(p.DATETIME,1,4) = substring(i.DATETIME,1,4) AND
              p.LOCATION = i.LOCATION AND
              p.DESTINATION = i.DESTINATION  
          )
BEGIN
RAISERROR ('Duplicate Constrain by trigger.', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;

------------------

I hope I can help
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 17 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question