Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    4

    Unanswered: Adding a UNIQUE CONSTRAINT to a column having duplicate data

    Hi i have a requirement where i need to add a UNIQUE CONSTRAINT to a column that has duplicate data in it. How can i achieve this and is there any other way to achieve it rather by any other Contraint?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    What do you want to happen to the rows violating the constraint?

  3. #3
    Join Date
    May 2012
    Posts
    4

    Unique constraint

    Actually i hav a table names "contact" where 'email' is the column having duplicate data. My requirement is i have to add a UNIQUE CONSTRAINT to email column without deleting the duplicates...is it possible in MYSQL? if so how to achieve it? Hoping a quick reply ..Thanx in advance

  4. #4
    Join Date
    May 2012
    Posts
    4
    Here the old data should not be deleted but the column should not accept duplicate data for new entries to 'email' column..thats the exact requirement...

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Either you want duplicates or you don't.

    You can't allow duplicates for some rows and disallow them for others.

    The only workaround I can think of is to add a new column (e.g. allow_duplicate) which indicates if an email is allowed as a duplicate. Then set that column to 1 for all existing rows and then create a unique constraint on (email, allow_duplicate).

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    tidy up your data before attemtping to enforce such a constraint.
    OR
    acept your data has duplicates and therefore cannot enforce such a constraint within the DB

    you could, if you so wish check that the email doesn't exist before comitting any additions or changes. that woudl involve your front end (the application layer) enforcing such a check
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •