Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

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
0
panJames
Asked:
panJames
  • 2
  • 2
2 Solutions
 
ThomasianCommented:
>>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
 
JacobfwCommented:
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
 
panJamesAuthor Commented:
Stupid me: all I had to do was to change the name.

THX!

0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now