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

[GLT]

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:-

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 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?

[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.

[GLT]

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:-

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

[hollystyles]

Yep, that's it :)

[iAmLast]

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

[debasisdas]

So what is your problem ?

[achied]

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