table duplicates

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) ?
nevyana2006Asked:
Who is Participating?
 
ssisworoConnect With a Mentor Commented:
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
 
nevyana2006Author Commented:
thank you, i found that for char and varchar i can use column(len) which worked for me.
0
 
ssisworoCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.