lynnwalker
asked on
Two tables with foreign keys pointing at each other
I have two tables, members and images. Members has a primary key UserID and Images has a primary key of ImageID. Members may have many images. A foreign key, UserID in Images refers to the UserID primary key in Members. Members can have 1 default image, so a field in the Members table, DefaultImageID stores this value. I would like this field to be a foreign key referring to the ImageID primary key in Images. When I tried to create this relationship along with the first relationship I get an exception and can't save the second foreign key constraint.
Is this legal? Is there a way to have two tables with foreign keys pointing to each other, and if so, how do I do this? Alternately, is there a better design approach?
Thank you for your time.
Is this legal? Is there a way to have two tables with foreign keys pointing to each other, and if so, how do I do this? Alternately, is there a better design approach?
Thank you for your time.
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
dqmq,
>>Though I would argue that MemberImageID (PK) is unneeded--you can just declare the PK across the other two ID columns
Quite true. I've implemented that kind of scenario both ways, and it's really a matter or preference more than anything else.
:)
Patrick
>>Though I would argue that MemberImageID (PK) is unneeded--you can just declare the PK across the other two ID columns
Quite true. I've implemented that kind of scenario both ways, and it's really a matter or preference more than anything else.
:)
Patrick
>>I've implemented that kind of scenario both ways, and it's really a matter or preference more than anything else.
The risk of introducing a surrogate key (MemberImageID) to the physical design is loosing track of the natural key (MemberID, ImageID). MSAccess is inadequate in that respect, however, your Unique Pairs specification does serve the purpose. As long as it's intent is not forgotten, then I concede that the superflous key is a matter of preference.
The more significant design elements are:
The risk of introducing a surrogate key (MemberImageID) to the physical design is loosing track of the natural key (MemberID, ImageID). MSAccess is inadequate in that respect, however, your Unique Pairs specification does serve the purpose. As long as it's intent is not forgotten, then I concede that the superflous key is a matter of preference.
The more significant design elements are:
preventing two users from sharing one image (if that's indeed a requirement)
limiting a user to a single default image
limiting a user to a single default image
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Members
--------------------------
UserID (PK)
MemberName
Images
--------------------------
ImageID (PK)
ImageDescr
MemberImages
--------------------------
MemberImageID (PK)
UserID (FK)
ImageID (FK) <---- also add a unique index over UserID, ImageID to ensure unique pairs
IsDefault