none
Cannot add data to existing table

    Question

  • Hello All,

    I have a very trivial issue.  I have an existing test table in SQL and I use DBVisualizer client to connect to the server.

    I have only six records in the table that I am testing.  I have a field called "SITE".  Within this field I have the following data:

    Site:

    NYC

    LA

    BOS

    I am trying to import a csv file with 6 row of records, where I have records in there where 'site' = "BOS".

    But when  I try to import, I get the following error:

    "Violation of Unique Key constraint 'ix1_test'. Cannot insert duplicate key in object 'dbo.test'."

    When I try to add the records manually, SQL allows me to add all data into the row, except when I try to add "BOS" into the site field.  Then the error happens.

    What I do not understand is, I imported a previous csv with nine (9) records with multiple rows of records with "BOS" in the site field and had no issues.

    Why is this occurring now?

    Any help would be great!

    Thanks everyone!

    Saturday, March 15, 2014 3:09 PM

Answers

  • Thanks for the help Dan.  But it seems like I need the SQL admin to make some changes to the table.  What should I tell the SQL Admin to do on my test table?

    Or since this is a test table, and I believe I have full access to it, is there anything I can run not to make those fields part of a unique constraint?

    I can't really make a recommendation without knowing more about the data.  I see that this is a test table, but is the intent to model something in the real world? In the latter case, you might want more than one table (e.g. rooms, devices, and device types).  That would allow you to keep an inventory of computer devices and their locations with any number of devices per room.

    If your intent is to just play around inserting and selecting data without regard to data integrity, you can drop the constraint with the command below, assuming you have permissions to do so.

    ALTER TABLE dbo.test DROP CONSTRAINT idx1_test;

    I do have one minor recommendation for your admin about the constraint naming, though.  Although primary key and unique constraints are implemented as a unique index, I suggest a naming convention other than one that suggests an index.  This will help avoid confusion with indexes that are used to improve performance rather than ensure data integrity.  For example, you could name this unique constraint something like UQ_test_1 rather than ix1_test.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by AdminSFGNYC Tuesday, March 18, 2014 6:30 PM
    Sunday, March 16, 2014 2:25 AM

All replies

  • I am trying to import a csv file with 6 row of records, where I have records in there where 'site' = "BOS".

    But when  I try to import, I get the following error:

    "Violation of Unique Key constraint 'ix1_test'. Cannot insert duplicate key in object 'dbo.test'."

    When I try to add the records manually, SQL allows me to add all data into the row, except when I try to add "BOS" into the site field.  Then the error happens.

    What is the definition of "idx_test"?  Please post the script for that unique constraint to verify the constraint is on site, and site alone.

    In brief, you will get the unique constraint violation error if either a row for the index key value already exists in the table or if the source data contains multiple rows for the same key.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, March 15, 2014 3:53 PM
  • Thanks Dan for the reply.  I am no SQL person, so I do not know what script you are referring to.  Is this what you mean?.. I did do the following:

    select * from information_schema.table_constraints where table_name='TEST'

    CONSTRAINT_CATALOG    CONSTRAINT_SCHEMA    CONSTRAINT_NAME    TABLE_CATALOG    TABLE_SCHEMA    TABLE_NAME    

    DBS                                 dbo                                PK_test                      DBS                       dbo                      test   

    DBS                                 dbo                                ix1_test                      DBS                       dbo                      test

    CONSTRAINT_TYPE    IS_DEFERRABLE    INITIALLY_DEFERRED

    PRIMARY KEY             NO                       NO

    UNIQUE                     NO                        NO

    Saturday, March 15, 2014 4:14 PM
  • My guess you had the constraint created with NO CHECK option so it didnt complain on the existing violation. But subsequent inserts it found the duplicates and hence it broke the constraint.Did you create the constraint at a later point after creation of table when it had some data?


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, March 15, 2014 5:58 PM
  • Thanks Dan for the reply.  I am no SQL person, so I do not know what script you are referring to.  Is this what you mean?

    Not exactly but the query below will provide the information:

    SELECT * 
    FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
    WHERE TABLE_NAME = 'TEST';


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, March 15, 2014 6:19 PM
  • Ok Dan, here you go:

    TABLE_CATALOG    TABLE_SCHEMA    TABLE_NAME    COLUMN_NAME    CONSTRAINT_CATALOG    CONSTRAINT_SCHEMA CONSTRAINT_NAME
    DBS                dbo                test        computer    DBS                    dbo                    ix1_test
    DBS                dbo                test        laptop        DBS                    dbo                    ix1_test
    DBS                dbo                test        room        DBS                    dbo                    ix1_test
    DBS                dbo                test        site        DBS                    dbo                    ix1_test
    DBS                dbo                test        id            DBS                    dbo                    PK_test

    The table-columns was copied from an existing table using the same columns, but not the data from the previous table.  Not sure if that makes a difference.

    Saturday, March 15, 2014 8:23 PM
  • I am no SQL admin, and the table was created by a SQL admin, and the columns were copied from another table, but not the data.  I did not create any constraints as I do not know how to do that or what that is for.  I am using this test table to test the import function via the gui.

    Hope this helps!

    Thx!

    Saturday, March 15, 2014 8:25 PM
  • The unique constraint is not on site alone, but the combination of computer, laptop, room and site.  You were probably able to insert multiple rows for site BOS previously because the values for one or more of other columns was different than existing rows.

    Your SQL admin was wise to create a constraint to ensure duplicate data cannot be inserted.  But I can't say if this is the proper constraint since I know nothing about your data or what you are modeling.  Is the intent to have one row per room?  In that case, I think site and room would be enough to identify a particular location.  But since computer and laptop are part of the unique key, the intent may be to have more than one row per room (e.g. one row per device).  In the latter case, I think there may be a flaw in the table design.  But if those columns are just indicators of whether the room contains those device types, those columns shouldn't be part of the unique constraint because that implies one row per room.

    Perhaps we can offer suggestions for you and your SQL admin if you provide more details.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, March 16, 2014 12:04 AM
  • Thanks for the help Dan.  But it seems like I need the SQL admin to make some changes to the table.  What should I tell the SQL Admin to do on my test table?

    Or since this is a test table, and I believe I have full access to it, is there anything I can run not to make those fields part of a unique constraint?

    Thanks again for all your help thus far.  What you wrote above made sense.  The previous records I imported were into different fields that the ones I am trying to do now. 

    :-)

    Sunday, March 16, 2014 1:04 AM
  • Thanks for the help Dan.  But it seems like I need the SQL admin to make some changes to the table.  What should I tell the SQL Admin to do on my test table?

    Or since this is a test table, and I believe I have full access to it, is there anything I can run not to make those fields part of a unique constraint?

    I can't really make a recommendation without knowing more about the data.  I see that this is a test table, but is the intent to model something in the real world? In the latter case, you might want more than one table (e.g. rooms, devices, and device types).  That would allow you to keep an inventory of computer devices and their locations with any number of devices per room.

    If your intent is to just play around inserting and selecting data without regard to data integrity, you can drop the constraint with the command below, assuming you have permissions to do so.

    ALTER TABLE dbo.test DROP CONSTRAINT idx1_test;

    I do have one minor recommendation for your admin about the constraint naming, though.  Although primary key and unique constraints are implemented as a unique index, I suggest a naming convention other than one that suggests an index.  This will help avoid confusion with indexes that are used to improve performance rather than ensure data integrity.  For example, you could name this unique constraint something like UQ_test_1 rather than ix1_test.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by AdminSFGNYC Tuesday, March 18, 2014 6:30 PM
    Sunday, March 16, 2014 2:25 AM
  • try this way, romove all data in this table ok, import youy CSV, it will work.
    Then leave the table with the imported data, and retry to import the data again, you will get the error


    So The problem is :


    1. you have a unique index on your table, so click on your database node is SQL Server Management Studio, and expand it, clique also on your table node, and click on index, and verify for each index, if it is unique ok.
    remove all index for just a test, not forget to backup the DB :-)

    Or

    2. you have a identity column like a Primary key, SQL Server generate automatically the value of this type of column.
    Sunday, March 16, 2014 4:16 AM
  • Thanks Dan for pointing me in the right direction.  I reached out to the SQL admin to take a look at the table.  He noticed the discrepancy you had pointed out and will rectify the issue.

    Thanks again and my apologies for the late reply.

    Tuesday, March 18, 2014 6:31 PM