Violation of PRIMARY KEY constraint....problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Violation of PRIMARY KEY constraint....problem

Author

Topic

krishnet
Starting Member

29 Posts

Posted - 2007-06-06 : 09:00:28

Hi,

Table: company

companyid (p.k) identity
companyname notnull

Table: item

itemid (p.k.) identity
companyid(f.k.)
itemname notnull

Table:vendor

vendorid(p.k)identity
vendorname notnull

Table:vendordetails

vendorid notnull ----------------------------------- p.k. or f.k. ????
companyid(f.k.)
itemid (f.k.)

Ok,this is the perfect table which i use.

Can anyone pls tell me what can i keep vendorid in vendordetails table as p.k. or f.k ??

I want that 1 vendor has a relationship of 1toMany with the company and items.

NOTE :

1or more vendor/vendors can have contract with morethan 1 company to sell more than 1items of that company..

For the items and vendor i want to use the list box in the vendor form.
I want to select multiple items and company from the listbox under 1 vendor name.Which gives me the error.

Violation of PRIMARY KEY constraint.Cannot insert duplicate key in object.
The statement has been terminated.

Line 55: SqlDataSource1.Insert();

The vendor can sell the items of the particular company as well as other companies also...

I don't get the idea whether i should post this topic under ASP.NET 2.0 with C# or SQL SERVER 2005.

Thanxs hope to get a reply..

Pls if possible tell me what changes i can do for that table ??

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-06 : 09:05:53

Since Vendor table has one-to-many relationship with company and Item, obviously VendorID should be made Foreign key in VendorDetails table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

krishnet
Starting Member

29 Posts

Posted - 2007-06-06 : 09:06:38

protected void cmdsubmit_Click(object sender, EventArgs e)
{
SqlDataSource1.InsertParameters["vendorname"].DefaultValue = tbvendorname.Text;
foreach (ListItem item in listboxcompany.Items)
{
if (item.Selected)
{
SqlDataSource1.InsertParameters["companyname"].DefaultValue = item.Text;
SqlDataSource1.Insert();
}
}
foreach (ListItem item in listboxitemsname.Items)
{
if (item.Selected)
{
SqlDataSource1.InsertParameters["itemname"].DefaultValue = item.Text;
SqlDataSource1.Insert();
}
}
}

This is the way i have used listbox for mulitple selection.Where i m getting the above error...

thanxs..

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-06-06 : 09:11:06

first of all the u combine the three columns of vendordetails tables and make them as a primary key for the table.
So the all the three columns combination must be unique.Hope you got the answar.

krishnet
Starting Member

29 Posts

Posted - 2007-06-06 : 09:53:57

Sorry...bpgupta
I don't get what u said...can u explain me again..

I don't want to add any extra key to that...Table:vendordetails as a primary key...

Do u want to say that all three field should be primary key ??
Table:vendordetails

vendorid notnull ----------------------------------- p.k. or f.k. ????
companyid(f.k.)
itemid (f.k.)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-06 : 12:54:02

I believe what bpgupta is saying is that:
1. VendorID should be a FK to Vendor
2. That VendorID, CompanyID and ItemID should have a Unique Constraint or Index (PK) on VendorDetails.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources