September 5, 2008 at 4:56 am
Hi ,
Im making website using c# and sql2000. So my tables has so many foreignkeys. My problem is user trying to delete some record in my tables. But that record has foreign key reference. So i dont want to give permission to delete that kind of errors. But in my code behind i want to catch that record has some foreign key references.
So How i track FOREIGN key reference . i want to know are there any common ID or common way to identify foreign key references.
Thanks
September 5, 2008 at 7:25 am
If you have defined the foreign key in the database and have not included any Cascade options then the database will not allow a delete that will break the relationship it will return an error. The message will be:
The DELETE statement conflicted with the REFERENCE constraint "Foreign Key Name". The conflict occurred in database "Database Name", table "Child Table Name", column 'Foreign Key Column Name(s)'.
I know .NET has an error number for this, but I don't know what it is off the top of my head. You can catch the error and handle it in the front end.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2008 at 8:44 am
sampath
is the FK problem solved? like Jack said it should be 'Cascade' option while setting FK relationship. When setting relationship, if you check 'Cascade' you will not get issues when you update/delete. You can also catch the data being deleted in code-behind.
September 8, 2008 at 11:27 pm
hi,
First i want to thanks both of Grasshopper,Jack Corbett to replied me.
Still my problem is same. Earlier i used cascade for delete my foreignkey references. But now i remove cascade. Mean now we cant delete any foreign keys.
So my TOPIC Are there have any messeges or any number to find foreign keys.
Jack showed foreign key deleting time how to show it. But i want to commonly identify it. I want to thanks jack. He told he dont know how to catch that in code behind and .net has some way. YES. .net can catch what is the error and is it database error or not and why its occured. But i want to find ARE THERE ANY DATABASE FUNCTION OR ANY OPTION TO IDENTIFY FOREIGN KEY REFERENCES.
Thanks
Sampath
(SriLanka)
September 9, 2008 at 6:15 am
Well, apparently we all misunderstood your question. I think I understand now. You want to find out what all the Foreign Key constraints are in your database and I assume the columns that belong to them. Here is a query that returns the key information about foreign keys:
[font="Courier New"]SELECT
RC.Constraint_Name AS FK_Constraint,
RC.Constraint_Catalog AS FK_Database,
RC.Constraint_Schema AS FK_Schema,
CCU.Table_Name AS FK_Table,
CCU.Column_Name AS FK_Column,
RC.Unique_Constraint_Name AS PK_Constraint,
RC.Unique_Constraint_Catalog AS PK_Database,
RC.Unique_Constraint_Schema AS PK_Schema,
CCU2.Table_Name AS PK_Table,
CCU2.Column_Name AS PK_Column
FROM
information_schema.referential_constraints RC JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON
RC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2 ON
RC.UNIQUE_CONSTRAINT_NAME = CCU2.CONSTRAINT_NAME
ORDER BY
RC.Constraint_NAME
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 10, 2008 at 12:26 am
Thank you very much Jack,
This message useful for my case.I needed this
:smooooth:
Regard
Sampath
(SriLanka)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply