locked
Violation of primary key constraint RRS feed

  • Question

  • Hi,

     

    I've a set of source tables on one server and a set of destination tables on another.

    When I move data from one server to next, I get an error saying "Violation of Primary Key Constraint". This happens for only one table among the 6 tables that I have. I dont understand why it throws me this error. Though theres an error for this table, the data from source table moves into this destination table.

     

    Am I missing something?

     

     

    Thanks,

    Subha Fernando

     

    Friday, January 4, 2008 6:21 PM

Answers

All replies

  • That means that some of the rows already exist in the destination table

    use a left join or not exist when transferring the data over

     

    Denis The SQL Menace

    http://sqlservercode.blogspot.com

    http://sqlblog.com/blogs/denis_gobo/default.aspx

    Friday, January 4, 2008 6:26 PM
  • Does the table in question have the same primary key on both servers?
    Friday, January 4, 2008 6:27 PM
  • This is not possible as I truncate the destination before moving new data.

     

    Friday, January 4, 2008 6:29 PM
  • this simply means that there is something wrong in relational schema that you created

    your action is either making primary key NULL or violating the constraints

    You may refer to msdn for detail explanation

    http://msdn2.microsoft.com/en-us/library/aa937371(SQL.80).aspx#sql:referential_integrity__ri_

     

    Friday, January 4, 2008 6:32 PM
  • I'm not sure on this one.

     

    My source has:

    /****** Object: Index [PK__table__7597BDC7] Script Date: 01/04/2008 12:32:34 ******/

    ALTER TABLE [dbo].[PM_table] ADD PRIMARY KEY NONCLUSTERED

    (

    [tableID] ASC,

    [tableName] ASC

    ) ON [PRIMARY]

     

    My destination has:

    /****** Object: Index [PK_table] Script Date: 01/04/2008 12:31:04 ******/

    ALTER TABLE [X].[table] ADD CONSTRAINT [PK_table] PRIMARY KEY CLUSTERED

    (

    [tableID] ASC,

    [tableNAME] ASC

    )WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

     

     

    The only difference I see is the clustered/non-clustered thing. All the 6 tables have been created this way. I don't understand why it then fails for the last table. It actually moves the data from source to destination and exactly the same number of rows as well.

     

    Thanks,

    Subha

    Friday, January 4, 2008 6:34 PM
  •  

    Is it possible that you forgot to truncate or are moving the same table twice (my hunch is the latter)

    try running a DBCC CHECKTABLE to make sure the table is not corrupt

     

    Denis The SQL Menace

    http://sqlservercode.blogspot.com

    http://sqlblog.com/blogs/denis_gobo/default.aspx

    Friday, January 4, 2008 6:40 PM