Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Apples
Posting Yak Master
146 Posts |
 Posted - 2011-04-19 : 20:40:31
|
| I have two objects, a Press and a Stitching:Press------------------PressIDNameStitching------------------StitchingIDNameCostSo on my site, when a user creates a press, they have the option to create a Stitching for that press. A press doesn't have to have a stitching.I'm using Entity Framework 4, and if I get a Press object from the database, I'd like to be able to say:Press p = getPressFromDB(pressId);if (s.Stitching != null){ float cost = p.Stitching.Cost;}How can I create a one-to-one relationship between these two tables? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-19 : 20:48:40
|
| I don't know EF4, but on the database side, the easiest way is to add a StitchingID column to Press:ALTER TABLE Press ADD StitchingID int null REFERENCES Stitching(StitchingID)If that's not an option, an additional table can be used:CREATE TABLE PressStitching (PressID int NOT NULL PRIMARY KEY, StitchingID int NOT NULL,CONSTRAINT FK_PressStitching_Press FOREIGN KEY(PressID) REFERENCES Press(PressID),CONSTRAINT FK_PressStitching_Stitching FOREIGN KEY(StitchingID) REFERENCES Stitching(StitchingID))The primary key on PressID guarantees that a Press only has one Stitching, and the foreign keys guarantee referential integrity. |
 |
|
|
Apples
Posting Yak Master
146 Posts |
Posted - 2011-04-19 : 20:50:47
|
quote:
Originally posted by robvolk
I don't know EF4, but on the database side, the easiest way is to add a StitchingID column to Press:ALTER TABLE Press ADD StitchingID int null REFERENCES Stitching(StitchingID)If that's not an option, an additional table can be used:CREATE TABLE PressStitching (PressID int NOT NULL PRIMARY KEY, StitchingID int NOT NULL,CONSTRAINT FK_PressStitching_Press FOREIGN KEY(PressID) REFERENCES Press(PressID),CONSTRAINT FK_PressStitching_Stitching FOREIGN KEY(StitchingID) REFERENCES Stitching(StitchingID))The primary key on PressID guarantees that a Press only has one Stitching, and the foreign keys guarantee referential integrity.
That would create a many-to-one relationship though.I'd like to also be able to call:Stitching s = getStitchingFromDB(stitchingId);Press p = s.Press;In your first example, I would have a collection of Presses for my Stitching object. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-19 : 21:02:42
|
| CREATE TABLE PressStitching (PressID int NOT NULL PRIMARY KEY, StitchingID int NOT NULL,CONSTRAINT FK_PressStitching_Press FOREIGN KEY(PressID) REFERENCES Press(PressID),CONSTRAINT FK_PressStitching_Stitching FOREIGN KEY(StitchingID) REFERENCES Stitching(StitchingID),CONSTRAINT UNQ_Stitching UNIQUE(StitchingID))The additional UNIQUE constraint prevents a Stitching from associating with multiple Presses. |
|
|
|
|
|
|
|
|
Creating a one-to-one relationship