2008R2 Foreign Keys Don't have cardinality

Question

2008R2 Foreign Keys Don't have cardinality

flebber.crue

SSC Eights!

Points: 805
More actions
April 3, 2012 at 6:51 am

#254252

In SSMS designer when you create a relationship from one table to the next it doesn't have a setting to choose 1-to-1 or 1-to-M .
All that appears to happen is that you right click on the table that you want to have the foreign key in and select design. I use the Guid column from the other table in the left column and that's it. It doesn't ask as Access would what's the relationship, why? Does it guess it?
I assume that the table I inserted the foreign key into should be the 'many' and the primary table the 'one'. But I cannot see any confirmation of this. Is there a way to test relationship cardinality in SSMS? I tried right clicking on the SSMS designer on the relationship link but you can't edit and create this.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

to make it 1:1, you simply add a unique constraint on the child table's column that is referencing the Parent;

so it's not a "type" of foreign key you are looking for, but an additional constraint...a Refernece AND can be only one...that jsut requires two constraints.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

SQL can figure it out by checking which table has the unique constraint on the column.

If both sides have a unique constraint, then it's a 1:1 foreign key, otherwise it's 1:m where the 1 is the table with the unique constraint on the foreign key column

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

flebber.crue SSC Eights! Points: 805 More actions · Answer 3

So if I haven't a constraint it should be m on the table with the created foreign key.

I guess I wIll just have to get used of it. Not initially as comforting as seeing the relationship though.