Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2013
    Posts
    3

    Question Unanswered: Table with Duplicate Values - Can I create a unique key here?

    I have table I have a table with following fields

    FIELDKEY int autoincrement
    FIELD1 varchar
    FIELD2 varchar
    FIELD3 varchar
    FIELD4 varchar

    The values for FIELD1 to FIELD4 have duplicate rows in them. I plan to do a

    ALTER TABLE <your table>
    ADD CONSTRAINT unique_field1_2_3_4 UNIQUE(FIELD1 , FIELD2 , FIELD3 , FIELD4)

    Question: Will the ALTER Table Work even if there are existing duplicate values for FIELD1, FIELD2,FIELD3 and FIELD4? If it won't work what can i do since this is production data and the values cannot be deleted due to foreign keys

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... the values cannot be deleted due to foreign keys
    What foreign keys are defined?
    Please publish DDLs(CREATE/ALTER TABLE, CREATE INDEX, so on...) including related tables.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by robbiwan View Post
    Will the ALTER Table Work even if there are existing duplicate values
    Of course no.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Feb 2012
    Posts
    23
    Alter table won't work because of the duplicate keys.

    I'd say delete the duplicate row, but you can't do that without thinking about the FIELDKEY, because that's the only one that is different in regard to the other fields in the table.

    Thus: Query the table for the duplicates and look at the FIELDKEYs on the duplicate rows. Is any other table referencing the FIELDKEY column on this table? Why and can this be updated to a single record in this table that is holding the same value for the other 4 columns (FIELD 1 to 4)

Posting Permissions

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