Unique Constraint on existing table with duplicate data

  • Hi,

    I have a table called GN_CarParkingAreas

    Fields:

    1. CarParkId int PK

    2. CarParkNo varchar(10) not null

    3. DevelopmentID int not null

    4. Notes varchar(100) null

    There are 10 records in the table.

    Now I need to add a unique constraint in the table with UNIQUE(CarParkNo,DevelopmentID)

    There are rows in the table violating this.

    I need to check this constraint only for the new records and ignore the values existing in the table.

    I tried

    ALTER TABLE GN_CarParkingAreas

    WITH NOCHECK ADD CONSTRAINT UC_CarParkNo UNIQUE (CarParkNo,DevelopmentID)

    GO

    but falied with error

    Msg 1505, Level 16, State 1, Line 2

    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.GN_CarParkingAreas' and the index name 'UC_CarParkNo'. The duplicate key value is (1, 40).

    Msg 1750, Level 16, State 0, Line 2

    Could not create constraint. See previous errors.

    The statement has been terminated.

    How can I do this? Please help.

    Thanks In Advance,

    Anju Renjith.

  • Anju Renjith (1/29/2013)


    ...

    I need to check this constraint only for the new records and ignore the values existing in the table.

    You can't have part of a table with constraint and part without. Create a new table with constrain for new data and you can eg: create a view that will hide for select that there are two tables.

  • It seems you have duplicate data in the columns and you will not able to create the unique index on a column with duplicate rows.

    You might consider delete the duplicate data

    Suppose,

    Pk colmn, col1, col2

    1 , 2, 40

    2 , 2, 40

    So the pk clustered index on pkcolumn is fine and unique cannot be created on col1 and col2..

    Hope it helps.

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • Sorry, i haven't seen that line to ignore the current data ...and create a unique index..sorry that is not possible for the table with that data.

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • I use SQL server 2008 R2

  • If you want to create a index for certain search criteria then you could use filtered index(non clustered) for specific type of data.

    may be helpful

    here[/url]

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • NOCHECK only applies to foriegn keys and check constraints, not unique constraints. Details are in the Books Online. In order to satisfy the value UNIQUE, it kind of has to be, you know, unique. Filtered indexes is an option, but may cause issues. Why can't you just fix the data? That's the better approach.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanx all for ur quick response!!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply