Referential Integrity - Which Column violates this?

Hi All,

I am inserting into a table that hold several foreign keys from several tables.
I'm performing this via a client (VB) and I only how to capture the error, but unable to determine which column/field is the one that violates referential integrity.

Any one can shed some light here? Many thanks!

CyherusDo you have any ddl that's behind the VB application? Can you match it with the values you want to insert?|||nope, I am basically connecting to the SQL server via odbc.. and using INSERT INTO queries to add records as I read from a text file..
Since the SQL Server is able to throw me an error description that says which table and column name violates the RI, I thought I would be able to manage this with codes. That is create the primary record in the primary table (affected table) and resume to insert the record in the foreign table again..

Cyherus|||It should be done with sp, not with FE-based action queries.|||The text of the error message (in the errors collection) will tell you which foreign key caused the problem. The code is the same for them all, so it only tells you that a foreign key was the problem.

-PatP|||If you do it in sp you can customize the way errors are returned to the client. And of course Errors (rdoErrors) collection should be looped to retrieve ALL the errors that came from the server.|||Alright.. as per your advise.. I am now performing these actions on the BE via sp.
now, I am using both return value, out parameters and capture @.@.ERROR.
Issue is that the system it prompting me before I can capture the errors returned.. sp example:

ALTER PROCEDURE dbo.sp_insert_bl
(
@.AAA varchar(12),
@.BBB int,
)
AS
DECLARE @.err int

SET NOCOUNT ON
BEGIN TRAN
INSERT INTO dbo.Table ([AAA], [BBB])
VALUES (@.AAA, @.BBB)
SELECT @.err = @.@.ERROR
IF @.err <> 0
BEGIN
ROLLBACK TRAN
RETURN @.err
END
ELSE
BEGIN
SELECT @.blid = SCOPE_IDENTITY()
COMMIT TRAN
RETURN 0
END

oh yes, using ado to execute exec the sp
cmd.parameter.........
cmd.execute
cmd.parameter("return value")

do you suggest using RAISERROR?|||Of course, so that you can interrogate Errors collection. How else were you planning to see the errors?

Referential Integrity

Hi guys,

Is there a way of finding the foreign keys for a table and therefore determine weather any referential integrity rules would be broken if a record was deleted?

For example. You have an author you want to delete, but that author has books. You call a procedure to delete the author. The stored procedure checks the foriegn keys, checks the tables and determines what is in them and if any tables have records that would be "orphaned" you return an error reporting what tables have the "would be orphaned" data in them.

It need to be fairly generic int he manner that it checks the foreign keys and the sub table data.

This is on behalf of a guy at work so "requirements" may change. ;)

Anyone got some ideas??

Cheers,
ShaunI see three options:

1) Try / Error approach: just delete, catch eventual error and handle it
2) In a SQL Server using ADo: Use OpenSchema to retrieve foreign key constraints
3) query SQL Server's schema tables

If these hints are not sufficient, I'll eloborate on your preferred option.|||There's always the "deleted item" option. Instead of removing it just mark it as deleted and then filter out deleted items in your search results. I used to think that was a lazy way of doing it, but now I kind of think it's helpful when dealing with product databases. Unless of course you expect to be removing many items over a short period of time.|||select object_name(fkeyid) from sysforeignkeys where rkeyid = object_id(tablename)

This will give you the table names which have a foreign key constraint on the table.|||what about the referential integrity option

the only reason i can think of why you'd want to select first before deleting the author, is if you had failed to properly implement referential integrity and were facing the prospect of perhaps leaving orphaned books around

with referential integrity declared, here's what you do

1. delete author

if this is successful, there were no books that would've been "orphaned"

if the delete is unsuccessful, there were, so then issue your error message|||thanks for all the suggestions guys. much appreciated. look like the same sort of stuff I was suggesting to the guy.

the reason he wants to do this is because he is expecting stupid users who won't understand what they are deleting by deleting a author (following the example).

I think he is intending to go with a deleted flag (soft delete) at this point.

Referencing composite Primary KEYS

Hi,
i want to make a reference from a table on itself.
The table has a composite Primary Key. But I just want to refernce the TEstCaseID.
So whats wrong? Can anyone help me?

CREATE TABLE dbo.TestCase (
Project_projectID VARCHAR(20) NOT NULL references Project,
testCaseID VARCHAR(50) NOT NULL,
PRIMARY KEY(Project_projectID, testCaseID),
FatherID VARCHAR(50) references TestCase(testCaseID)

)

THanx CreanFor logical reasons, you can only reference unique values. Otherwise, how would SQL Server (or any database engine) know which of many records you were referencing?|||:D Already found out. But thanx a lot