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 |
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
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? |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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. |
|
|
|
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 |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
Help with Foreign Key relationships