1

Once a month I get an xml file with customers billings for the last month. I need to store this information in a database. I have a table named transactions. My primary key is the customers_nr. Currently when I'm inserting data, I'm getting error

violoation to primary key......

I'm using a table adapter for inserting. Basically I only need to keep filling the database with information. So is there any command for handling this? Or how should I do

Example of informaton

customer_nr: 12345
Billing_name: Microsoft
Billing_city: Seattle
Billing_amount 300
Billing_name: Mcdonalds
Billing_city: Seattle
Billing_amount 25

customer_nr: 4321
Billing_name: Ikea
Billing_city: New York
Billing_amount 1200
Improve this question
1
  • So what should happen if you receive a new entry for a customer with a customer id that already exists?!?!?!?! Commented Apr 16, 2011 at 12:26

3 Answers 3

Reset to default
2

The error means that you have duplicate value for your primary key in at least two records .

remember that primary key enforces uniqueness for data inside it.

so you can not insert two records in a table with same value for primary key. you can change your primary key to a new field (for example an auto number field) so you removed uniqueness constraint from your old primary key field and then you can insert duplicates in it!

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Yes i know this. How to avoid it?
you can not, the only way is to move your primary key constraint to other field.
@dumbel = change your primary key. The customers_nr (which I am guessing is an identifier for the customer) cannot by itself be a primary key of your transaction table, since you will have multiple transactions for any given customer. Do you have a transaction identifier, such as TransactionID or transaction_num? You may want to use that instead of customers_nr for your primary key. Basically, you need to find the column or combination of columns in your transaction table that are unique by row. That column(s) should be your primary key.
as JeremyDWill said, just create a new column "TransactionId" and set auto number to true. then change your primary key to new column. Transaction column should have its own primary key not customer's
0

billing datetime i more suitable for the primary key in this relation, alt. customer_nr + billing_datetime.

If you don't have any date and time for billing, or just know the month, you could use customer_nr, year, month as a composite primary key.

Improve this answer

Comments

0

Using the customer number is then obviously not a unique value, meaning it cannot be used as a Primary Key.

Create an IDENTITY column, or utilize a GUID with newsequentialid() (pref. as the field default) to achieve a unique key in the table.

Example:

You have the database structure as follows:

  • TransactionID (This is the new field, uniqueidentifer with newsequentialid() as the default value)
  • Customer_Nr
  • Billing_name
  • Billing_city
  • Billing_amount
  • Billing_name
  • Billing_city
  • Billing_amount

You would then go as follows:

INSERT INTO transactions (Customer_Nr, Billing_name, Billing_city, Billing_amount)
VALUES ('12345', 'Microsoft', 'Seattle', 300);

As you see, you won't need to change any code with values due to the automatically generated (unique) primary key value.

A note regarding newsequentialid() is that if your data requires security / privacy, do not use newsequentialid(), as it is trivial to guess sequences.

Improve this answer

3 Comments

If you create a new identity - please by all means use newsequentialid() as the default! Your index fragmentation will be much less than with the random newid() function
Yes! If you create a primary key (clustering key) on uniqueidentifier the index fragmentation will be horrible - unless you use newsequentialid() as default value - then it's bearable
@marc - Also worth noting that newsequentialid() is a big nono if security/privacy is a concern. If you know, how does newsequentialid() react to a crashed DB server?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.