Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: UNIQUE constraint WITH NOCHECK and 'NUMERIC_ROUNDABORT' error

    Hi,

    We have a table with duplicates. To prevent any more from entering the system, it would have been great if we could just add a UNIQUE constraint WITH NOCHECK on the table. But you can't do that with a unique constraint.

    As one possible solution, I have added an extra column to the table. That extra column stores the id of the record if there is at least one duplicate record with a lower id, and created a unique index on the PK + that extra column. I was looking for a solution without an extra column, but that generates an error. (If you are puzzled, the code is at the bottom of the post.)

    DaTable is a small test-table with a few duplicate and a not-duplicate record.
    Code:
    DROP TABLE dbo.DaTable;
    
    CREATE TABLE dbo.DaTable (
    	ID INT NOT NULL	IDENTITY CONSTRAINT PK_DaTable PRIMARY KEY,
    	Naam varchar(30) NOT NULL
    ) ;
    GO
    
    INSERT  INTO dbo.DaTable(Naam) VALUES  
    ('Tom'),
    ('Sjerk'),
    ('Sjerk'),
    ('Youssef'),
    ('Youssef'),
    ('Youssef')
    ;
    GO
    
    SELECT * from dbo.DaTable
    
    CREATE UNIQUE INDEX UNQ_DaTable_UniekeNieuweNamen ON dbo.DaTable(Naam) WHERE id > 6;
    GO
    --Msg 1934, Level 16, State 1, Line 1
    --CREATE INDEX failed because the following SET options have incorrect settings: 'NUMERIC_ROUNDABORT'. 
    Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query 
    notifications and/or XML data type methods and/or spatial index operations.
    I have no idea where this 'NUMERIC_ROUNDABORT' error comes from or what to do to solve it. Where does the loss of precision occur??

    Or perhaps you know a better way to deal with the problem (existing table with duplicates, and you want it to stop from getting worse).

    (This is my first solution, with the extra column. All comments are in Dutch, if you'd wonder)
    Code:
    DROP TABLE dbo.DaTable;
    
    CREATE TABLE dbo.DaTable
      (
        ID INT NOT NULL	IDENTITY 
    		CONSTRAINT PK_DaTable PRIMARY KEY,
        Naam VARCHAR(30) NOT NULL
      ) ;
    GO
    
    INSERT  INTO dbo.DaTable(Naam) VALUES  
    ('Sjerk'),
    ('Sjerk'),
    ('Youssef'),
    ('Youssef'),
    ('Youssef'),
    ('Tom') 
    ;
    GO
    
    -- markeer duplicaten
    ALTER TABLE dbo.DaTable ADD Duplicaat_id int NULL ;
    GO
    
    UPDATE  dbo.DaTable
    SET     Duplicaat_id = ID
    WHERE   EXISTS ( SELECT *
                     FROM   dbo.DaTable AS DT
                     WHERE  DT.Naam = dbo.DaTable.Naam
                            AND DT.ID < dbo.DaTable.ID )
    ;
    GO
    
    SELECT * FROM dbo.DaTable
    
    CREATE UNIQUE INDEX UNQ_DaTable_UniekeNieuweNamen ON dbo.DaTable(Naam, Duplicaat_id);
    GO
    
    -- bestaande naam toevoegen, geeft fout: ok
    INSERT  INTO dbo.DaTable (Naam)
    VALUES  ('Tom');
    --Msg 2601, Level 14, State 1, Line 1
    --Cannot insert duplicate key row in object 'dbo.DaTable' with unique index 'UNQ_DaTable_UniekeNieuweNamen'. The duplicate key value is (Tom, <NULL>).
    
    -- bestaande naam toevoegen die reeds een duplicaat heeft , geeft fout: ok
    INSERT  INTO dbo.DaTable (Naam)
    VALUES  ('Sjerk');
    --Msg 2601, Level 14, State 1, Line 2
    --Cannot insert duplicate key row in object 'dbo.DaTable' with unique index 'UNQ_DaTable_UniekeNieuweNamen'. The duplicate key value is (Sjerk, <NULL>).
    
    -- een nieuwe naam toevoegen, insert lukt : ok
    INSERT  INTO dbo.DaTable (Naam)
    VALUES  ('Wim');
    
    -- lukt maar één keer, geeft fout: ok
    INSERT  INTO dbo.DaTable (Naam)
    VALUES  ('Wim');
    --Msg 2601, Level 14, State 1, Line 1
    --Cannot insert duplicate key row in object 'dbo.DaTable' with unique index 'UNQ_DaTable_UniekeNieuweNamen'. The duplicate key value is (Wim, <NULL>).
    Last edited by Wim; 11-07-16 at 07:57.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Hey,

    Did some research. To use filtered indexes, you must execute

    Code:
    set numeric_roundabort off
    But I don't want to.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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