CODEWITHSUNDEEP
sqlsql-server
dtaskin
dtaskin

Reputation: 53

Duplicate primary key in SQL Server

I am trying to do an invoice for customers who bought products. I want same ID_Invoice and inside there will be different products that customer bought.

I want it to looks like this,

|ID_Invoice | ID_Reg | ID_Product | ProductType | Price |
+-----------+--------+------------+-------------+-------+
|    1      |   1    |  12345     |  Software   |  300  |
|    1      |   1    |  12346     |  Software   |  400  |
|    2      |   3    |  12347     |  Software   |  350  |
|    3      |   4    |  22346     |  Hardware   |  600  |

The same customer (ID_Reg) can buy 2 different products and it will be in same invoice (ID_Invoice).

But when I try to insert values into Invoice I get an error

Violation of PRIMARY KEY constraint 'PK_IDInvoice'. Cannot insert duplicate key in object 'dbo.Invoice'. The duplicate key value is (1).

I hope I explained well

Upvotes: 0

Views: 187

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

You want an invoices table where each row represents a single invoice and the id_invoice is the primary key.

You want an invoice_lines or invoice_products table that has one row per invoice line (or product). That is basically the table you have described above.

Given your description, the (id_invoice, id_product) should be unique in this table. And id_invoice should be a foreign key reference to invoices(id_invoice). I would generally give such a table an identity primary key as well.

Upvotes: 1

Related Questions