2

Here is the code

BEGIN TRANSACTION

INSERT INTO [cresql].[dbo].[AR_Transactions] 
       (DateTime , Dirty, Store_ID, Trans_Type,  Cashier_ID, CustNum, Trans_Amount, Prev_Cust_Balance) 
    SELECT   
       DATEADD(MINUTE, -30, Getdate()), 1, 1001, 'C', 100199,     
       CustNum, -Acct_Balance, Acct_Balance 
    FROM  
       [cresql].[dbo].[Customer] 
    WHERE 
       Acct_Balance <> 0  

UPDATE [cresql].[dbo].[Customer] 
SET Acct_Balance = -500 
WHERE Acct_Balance <> 0  

COMMIT TRANSACTION

but I am getting this error

Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'pkAR_Transactions'. Cannot insert duplicate key in object 'dbo.AR_Transactions'.
The statement has been terminated.

I am not a pro at SQL...any ideas what I am doing wrong

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Matt Elhotiby
  • 43,028
  • 85
  • 218
  • 321
  • 1
    which field is the primary key? – Marco May 23 '11 at 21:06
  • Trans_ID is the primary key i think...as i said i dont know SQL...i am a mysql and php developer and i am foriegn to sql server 2008 – Matt Elhotiby May 23 '11 at 21:09
  • 1
    As the message clearly says: you're trying to insert a **duplicate value** into whatever is your primary key on that table. Solution: just don't do it! The primary key of a table **must always** be unique - you **cannot** under any circumstances have two identical values for the primary key in two of your rows in the table. – marc_s May 23 '11 at 21:09
  • 1
    _Trans_ID_ is the primary key, but I don't see it in your procedure... – Marco May 23 '11 at 21:10
  • What datatype is `Trans_ID` ?? We're not seeing that anywhere in your statement - is it auto-generated by SQL Server?? – marc_s May 23 '11 at 21:10
  • Trans_ID is a bigint and i cant tell if its auto increment...it doesnt look like it and i didnt design this table – Matt Elhotiby May 23 '11 at 21:26

1 Answers1

2

By design, your table [cresql].[dbo].[AR_Transactions] is supposed to hold only one value for the primary key - in your case, this seems to be a combination of columns that make up this PK.

This is not a SQL issue, but seems to be a design concept.

You should find out what combination of keys is making up your primary key - it may be that the design states that you can insert once and update later on for that combination (although I would not consider this to be good design).

EDIT

Since Trans_Id is the PK, and you are not using that column in your insert, it is either being inserted as IDENTITY, as a DEFAULT (brrrrr), or using a TRIGGER.

IDENTITY: Check to see if the IDENTITY specification is screwed up. If so, just reset it. Here is how to How can I reseed an identity column in a T-SQL table variable?

DEFAULT: This is an odd choice, and you may have to ask the one who designed the database, read documentation, or just simply figure it out on your own. In any case, this is a very uncommon choice.

TRIGGER: Find the trigger and read through the code to see what it is doing. Here is how to find a list of triggers What is the most portable way to check whether a trigger exists in SQL Server?

Alternate scenario:

There could be a trigger that inserts into an AUDIT table that may have the problem. Check if the table has any other triggers and see what they do.

Raj More
  • 47,048
  • 33
  • 131
  • 198
  • how do i find out what they are – Matt Elhotiby May 23 '11 at 21:10
  • 1
    @Tamer: you decide which field(s) is/are primary key... so you should (must) know when you designed your db... – Marco May 23 '11 at 21:13
  • 2
    @Marco: someone else could have designed the table. – Raj More May 23 '11 at 21:14
  • 1
    @Tamer: Use SQL Server Management Studio to connect to the server/database. Then use the query give here http://stackoverflow.com/questions/1525672/determine-a-tables-primary-key-using-tsql – Raj More May 23 '11 at 21:16
  • i tried this SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name WHERE tc.CONSTRAINT_TYPE = 'Primary Key' ............but this didnt give me the AR_Transactions tables primary key – Matt Elhotiby May 23 '11 at 21:25
  • you should get down voted for this alone. "DEFAULT: Go find whoever designed the table and ask them" More often than not this is not possible. – Cheap Funeral Aug 30 '19 at 16:09
  • @CheapFuneral, you're right. I will edit my answer. – Raj More Sep 04 '19 at 14:42