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.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Help with Foreign Key relationships

Author  Topic 

GLE3
Starting Member

3 Posts

Posted - 2015-02-18 : 14:07:18
Hoping to get a resolution to my Foreign Key issue. I am trying to set up a database diagram full of Foreign Key relationships between tables with composite Primary Keys. All tables have at least one column where a Foreign Key relationship makes sense but there is also many columns within the composite keys that have no relational relevance (Not my design). It seems I can't build a Foreign Key using just parts of a composite key so I'm looking for a work-around. I'm hoping this work-around can be done under the constraint(s):

1) No superfluous columns need to be built in child or parent.

2) Without the use of a view omitting the columns that do not relate (It appears MS SQL does not support views in database diagrams)

3) Without re-keying any Primary Keys of the tables.

4) Without the use of unique indexes to isolate any one column as that would defeat the purpose of my composite Primary Keys.


I'm assuming this would be a tall task if not impossible, but I figured I better check before I start over and try another database diagram software. Thanks in advance for any help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-18 : 14:20:01
Why are you dismissing a unique index? They do not defeat the purpose of PKs.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

GLE3
Starting Member

3 Posts

Posted - 2015-02-19 : 14:05:58
Sorry, I should probably take a step back. I am a bit of a novice at SQL as my company just went Agile and everyone is trying to educate themselves on cross programming platforms. Most of my conclusions I draw above are from other internet pages.

Judging by this post : http://www.databaseproblem.com/1491_18892135/ It suggests the table in the post will not be governed by uniqueness of the PK but rather the Unique index. Is that not true?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-19 : 14:16:55
It isn't suggesting that. It is asking a good question that perhaps just the ID column should be the PK instead of the ID column plus the other two columns. If ID is not unique in that table, then the 3 columns should be the PK.

But don't let that question confuse PKs and unique indexes. You can have both. You SHOULD have both if there is more than one way to make the rows unique. But you should not just add all columns in the table to the PK when there's a unique identity column. Just use the identity column as the PK in that scenario.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

GLE3
Starting Member

3 Posts

Posted - 2015-02-19 : 14:49:07
Ok, thanks for the clarification. To re-word and correct #4 I would say:

4) Without the use of unique index to isolate the one column all tables share (that I mention above) as the data in the column is not unique.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-20 : 10:29:55
quote:
Originally posted by GLE3

Ok, thanks for the clarification. To re-word and correct #4 I would say:

4) Without the use of unique index to isolate the one column all tables share (that I mention above) as the data in the column is not unique.



Can you show an example of a constraint violation? That would make it easier to write
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-20 : 14:05:16
quote:
Originally posted by GLE3

Ok, thanks for the clarification. To re-word and correct #4 I would say:

4) Without the use of unique index to isolate the one column all tables share (that I mention above) as the data in the column is not unique.



But they are likely unique in the parent table. That's the key. As gbritton mentions, we'll need to see some examples to help you.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -