Ad:

MS SQL Discussion Thread
Unsolved
Views: 17129

Oct 30th, 2007

'There is already an object named 'ProductID' in the database.'

Expand Post »

Hey guys!

I seem to be posting here a lot the past few days but as I am new to SQL Server I am finding to quite a pain to get used to!

I am having a problem referencing foreign keys. I did post another thread about this but this is a slightly different problem.

everytime I try to reference a foreign key SQL Server says -
'There is already an object named 'ProductID' in the database.'

yes there is another Product ID in the Product table, why won't SQL Server recognise that it is a foreign key and needs to be in this table too? This is happening quite a lot when I am creating the tables and trying to reference the foreign key (not just ProductID). Here is the code for two of my tables:-

sql Syntax (Toggle Plain Text)
CREATE TABLE HireLine (
	HireID		INT	IDENTITY NOT NULL	,
	ProductID	INT			NOT NULL,
	Quantity	INT			NULL,
PRIMARY KEY (HireID),
CONSTRAINT ProductID FOREIGN KEY (ProductID)
	REFERENCES Product (ProductID)
		ON DELETE CASCADE ON UPDATE CASCADE,
);
CREATE TABLE HireLine (
	HireID		INT	IDENTITY NOT NULL	,
	ProductID	INT			NOT NULL,
	Quantity	INT			NULL,
PRIMARY KEY (HireID),
CONSTRAINT ProductID FOREIGN KEY (ProductID)
	REFERENCES Product (ProductID)
		ON DELETE CASCADE ON UPDATE CASCADE,
);

sql Syntax (Toggle Plain Text)
CREATE TABLE CorderLine (
	CorderID		INT,
	ProductID		INT,
	Quantity		INT		NOT NULL,
PRIMARY KEY (CorderID, ProductID),
CONSTRAINT CorderID FOREIGN KEY (CorderID)
	REFERENCES Corder (CorderID)
		ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT ProductID FOREIGN KEY (ProductID)
	REFERENCES Product (ProductID)
		ON DELETE CASCADE ON UPDATE CASCADE,
);
CREATE TABLE CorderLine (
	CorderID		INT,
	ProductID		INT,
	Quantity		INT		NOT NULL,
PRIMARY KEY (CorderID, ProductID),
CONSTRAINT CorderID FOREIGN KEY (CorderID)
	REFERENCES Corder (CorderID)
		ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT ProductID FOREIGN KEY (ProductID)
	REFERENCES Product (ProductID)
		ON DELETE CASCADE ON UPDATE CASCADE,
);

has anyone any ideas of where I am going wrong?

Last edited by peter_budo; Dec 13th, 2009 at 9:05 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

Similar Threads

accessing private data members in C++
Updated : Simple ASP.Net Login Page in ASP.NET
Loading All Database Name in ASP.NET
Need help displaying single database values in ASP.NET
database connection in ASP.NET

GLT

Reputation Points: 18

Solved Threads: 0

Junior Poster in Training

Offline

98 posts
since Aug 2007

Permalink

Nov 1st, 2007

Re: 'There is already an object named 'ProductID' in the database.'

This problem is because you are trying to create two objects by same name. You are creating a column and a constraint by the same name with in a table . this why u are getting this error . Try to use different name for the coumn and the constraint.

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

Permalink

Nov 1st, 2007

Re: 'There is already an object named 'ProductID' in the database.'

Click to Expand / Collapse Quote originally posted by debasisdas ...

This problem is because you are trying to create two objects by same name. You are creating a column and a constraint by the same name with in a table . this why u are getting this error . Try to use different name for the coumn and the constraint.

Thanks for your reply!

I am trying to make the ProductID column the foreign key. I thought that using a constraint like this was how to make it a foreign key. How would i code this? would I say:-

sql Syntax (Toggle Plain Text)
CREATE TABLE HireLine (
HireID INT IDENTITY NOT NULL ,
ProductID INT NOT NULL,
Quantity INT NULL,
PRIMARY KEY (HireID),
CONSTRAINT FK_ProductID FOREIGN KEY (ProductID)
REFERENCES Product (ProductID)
ON DELETE CASCADE ON UPDATE CASCADE,
);
CREATE TABLE HireLine (
HireID INT IDENTITY NOT NULL ,
ProductID INT NOT NULL,
Quantity INT NULL,
PRIMARY KEY (HireID),
CONSTRAINT FK_ProductID FOREIGN KEY (ProductID)
REFERENCES Product (ProductID)
ON DELETE CASCADE ON UPDATE CASCADE,
);

Thanks
GLT

Last edited by peter_budo; Dec 13th, 2009 at 9:06 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

GLT

Reputation Points: 18

Solved Threads: 0

Junior Poster in Training

Offline

98 posts
since Aug 2007

Permalink

Nov 1st, 2007

Re: 'There is already an object named 'ProductID' in the database.'

Yep, that's it

hollystyles

Reputation Points: 262

Solved Threads: 68

Veteran Poster

Offline

1,181 posts
since Feb 2005

Permalink

Dec 12th, 2009

Re: 'There is already an object named 'ProductID' in the database.'

im new to database

i think that in table Corder_line
is having a productID as primary key and the same time a foreign key

iAmLast

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Dec 2009

Permalink

Dec 16th, 2009

Re: 'There is already an object named 'ProductID' in the database.'

So what is your problem ?

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

Permalink

Dec 21st, 2009

Re: 'There is already an object named 'ProductID' in the database.'

Ms. SQL Server treats constraint as an object. You can check it in sys.objects table.

achied

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

7 posts
since Jun 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Query by Position

Next Thread in MS SQL Forum Timeline: SQL failure exposes plain text passwords

DaniWeb Message

Cancel Changes

User Name
Password