Relationship question

Hello experts!

I have a table of users:

USERS
id
name
surname

Users are allowed to sell things to each other.

I need a table to record sales data between users.

SALES
id
buyerFK foreign key to USERS.id
sellerFK foreign key to USERS.id
price

problem is that my database which is SQL Server express does not allow me to have two foreign keys pointing to the same table (USERS).

How can I solve this problem please?

Thank you

panJames
panJamesAsked:
Who is Participating?
 
JacobfwConnect With a Mentor Commented:
Not familiar with WebMatrix, but it could be attempting to create both relationships with the same name.  Is there a place to force the name of the foreign key on each one?

Does it provide an error?
0
 
ThomasianConnect With a Mentor Commented:
>>SQL Server express does not allow me to have two foreign keys pointing to the same table (USERS)
How are you creating the references? Are you getting errors?
CREATE TABLE TEMPUSERS (
	id int PRIMARY KEY
	,name varchar(100)
	,surname varchar(100)
)

CREATE TABLE TEMPSALES (
	id int PRIMARY KEY
	,buyerFK int REFERENCES TEMPUSERS(id)
	,sellerFK int REFERENCES TEMPUSERS(id)
	,price money
)

GO

DROP TABLE TEMPSALES
DROP TABLE TEMPUSERS

Open in new window

0
 
JacobfwCommented:
Are you attempting to use the ON Delete or On Update?
These have been know to cause problems in SQL Server Express for this type of relationship.

As indicated by Thomasian, would need to see the error to understand further.
0
 
panJamesAuthor Commented:
I am using WebMatrix, it allows me to create tables and relationships in its own tool.

Are you guys saying that it is possible to have such relations?

Maybe it is some king of limitation of webmatrix?

panJames
0
 
panJamesAuthor Commented:
Stupid me: all I had to do was to change the name.

THX!

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.