| Author |
Topic |
|
harringtonce
Starting Member
5 Posts |
 Posted - 2011-11-29 : 09:41:17
|
| I have a dataview that I have "edit" and "delete" links for data. The foreign key does what it is suppose to - prohibits data from being deleted if it's being used in another table.However, is there a way to check the data for a foreign key before I display the page's content? This way, I could "turn off" the delete link, but leave the edit link available for them to use?Thanks |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-11-29 : 10:32:30
|
| You could modify the query that sends the data to the DataView to add an addtional column that indicates whether there are any dependent columns - it would return a 1 or 0. You can hide that column in the view, but examine its value to determine whether or not to enable the delete button.Another option would be to query the database each time the user changes the active row to decide whether there are dependent rows - but that may be more expensive because it would require a round trip to the database each time the user changes the active row. |
 |
|
|
harringtonce
Starting Member
5 Posts |
Posted - 2011-11-29 : 10:48:48
|
| when I modify the sp that queries the data, do I have to manually include every table that uses that foreign key or does Microsoft SQL attach properties to a piece of data when it is being used?Sorry - just not a DBA here - .NET is pretty good, but SQL is not my wheel house :-) |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-11-29 : 11:26:42
|
| A foreign key references one table only. You can use this query to find out which tables are involved with each foreign key[CODE]select parent.name, fk.name, reference.namefrom sys.foreign_keys fkinner join sys.objects parent on fk.parent_object_id = parent.object_idinner join sys.objects reference on reference.object_id = fk.referenced_object_id[/CODE]HTH=======================================Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith |
|
|
|
harringtonce
Starting Member
5 Posts |
Posted - 2011-11-29 : 11:37:54
|
| thanks guys - I'll look into this |
|
|
|
harringtonce
Starting Member
5 Posts |
Posted - 2011-11-29 : 19:50:32
|
| that's not doing what I need it to :-)I've got tbl_assets.AssetID that I'm using in other tables within the database. Let's say that tbl_assets.AssetID = 1000. Is there a way I can query sys.foreign_keys for that particular value. If it shows up, I know that I can turn off the delete key and not allow them to use it? |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-11-30 : 11:14:40
|
| The sys.foreign_keys table only shows that a foreign key relationship exists; it doesn't know about specific values. Have you considered using "cascading deletes" as part of your foreign key definition? This would allow the deletion of the parent value to automatically delete the dependent values from the child table.=======================================Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith |
|
|
|
harringtonce
Starting Member
5 Posts |
Posted - 2011-11-30 : 11:21:06
|
| The asset is an image that is being used on the website somewhere. If I use "cascading deletes", it would then delete that image on the page where it is being used.This is all part of a custom CMS that I build. In the past, I've just "manually" looked for the value in tables where it could show up - but that creates a performance issue on bigger sites. If the value shows up, I would "dim" the delete button and not allow them to delete the image. They could edit it, if they wanted.I was just hoping foreign keys would be the way to go (on the .NET side), but it appears that it won't be.Thanks for you help! |
|
|
|
|
Foreign Keys & ASP.net