Solved

Check to see if a Table has reference key with other tables

Posted on 2007-12-04
10
172 Views
Last Modified: 2010-03-19
Hi,
I have 3 tables currently in sql server 2005 which has references
 TABLE A --- MAIN TABLE
  Student_ID
TABLE B  Child Table Student_ID Column as FK
TABLE c  Child Table Student_ID Column as FK

Now lets say I want to delete  a Row of Student_ID in Table A.... How can I check if that Student_ID is being referenced Or Used in Table B or TABLE C...........is there a easy way to check it .
0
Comment
Question by:dotnet0824
  • 5
  • 4
10 Comments
 
LVL 10

Expert Comment

by:lahousden
ID: 20402584
You can do it in one hit:

delete a
from Table_A as a
left outer join Table_B as b on b.Student_ID = a.Student_ID
left outer join Table_C as c on c.Student_ID = a.Student_ID
where b.Student_ID is null
and c.Student_ID is null
0
 
LVL 8

Expert Comment

by:srafi78
ID: 20402711
Something like this would help
Declare @Student_ID as int

Set @Student_ID = [Student ID you want to delete]

If Exists (Select * from Table_B where Student_ID = @Student_ID)

BEGIN

 PRINT ('STUDENT ID EXISTS IN Table_B')

 IF Exists (Select * from Table_C where Student_ID = @Student_ID)

  PRINT ('STUDENT ID EXISTS IN Table_B & Table_C')

END

ELSE

BEGIN

 IF Exists (Select * from Table_C where Student_ID = @Student_ID)

  PRINT ('STUDENT ID EXISTS IN Table_B')

 ELSE

  Delete from Table_A 

   Where Student_ID = @Student_ID

END

Open in new window

0
 

Author Comment

by:dotnet0824
ID: 20405333
Hi  lahousden:
can u break it up in such a way that I need to know whether it student_ID exists in B and C tables in my stored proc...... I want to have a return value to my calling function which knows that record cant be deleted due to existance of student_id in tables B or C
0
 

Author Comment

by:dotnet0824
ID: 20405344
Hi Srafi,
I dont need return values or print statements in each and every check in tables B AND C.
I just need kind of 1 return value which says "Exists " ELSE DELETE successfully
0
 
LVL 10

Expert Comment

by:lahousden
ID: 20405857
If you definitely know that the student was in Table A prior to the Delete statement then you can check @@ROWCOUNT immediately after the Delete to see whether any rows were deleted.  If not, then you know it was due to blocking rows in Table B or Table C, e.g.

declare @li_error int
declare @li_rowcount int

delete a
from Table_A as a
left outer join Table_B as b on b.Student_ID = a.Student_ID
left outer join Table_C as c on c.Student_ID = a.Student_ID
where b.Student_ID is null
and c.Student_ID is null

select @li_error = @@error, @li_rowcount = @@rowcount

and then you can do something with the value in @li_rowcount (e.g. RETURN it if you are in a stored procedure, etc.)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:dotnet0824
ID: 20407057
ok thanks got u though.. finally I couldnt understand the the join which u have created. could it be briefly explained
0
 
LVL 10

Accepted Solution

by:
lahousden earned 300 total points
ID: 20407827
The most common type of join is an INNER join - and this is the default if you omit the qualifier INNER/OUTER.  Consider table Clients and table WebSites.  A Client may or may not have a web-site.  Consider this select statement:

select c.ClientID, c.Name, w.WebSiteID, w.URL
from Clients as c
inner join WebSites as w on w.WebSiteID = c.WebSiteID

this will return the Name and ID of all clients with a web site, as well as the ID and URL of the web site.
This only returns clients if they have a web site.  What if you wanted all clients, whether they had a web-site or not, and the extra details in addition if they actually did have a web site?  That's where the OUTER join comes in:

select c.ClientID, c.Name, w.WebSiteID, w.URL
from Clients as c
left outer join WebSites as w on w.WebSiteID = c.WebSiteID

this will return ALL rows from the Clients table, regardless of whether there is a maching row in table WebSites or not.  Because an OUTER join is an asymmetric operation you need to specify whether the join is LEFT or RIGHT - this qualifier specifies which side of the JOIN keyword should be the "driving" table for the join - i.e. the table which we want all of the rows returned for - in this case it is the Clients table so we specify a LEFT outer join.
But what is returned in columns w.WebSiteID and w.URL for the Clients where there is no matching row in the WebSites table?  In an outer join, whenever the RDBMS engine can't find a match for the join condition in the non-driving table it invents a "phantom" row where ALL the columns are NULL (including the column it was supposed to have matched on!), so these extra columns will be returned in the query's result set as NULL.
So, when you reference columns in the non-driving table in the WHERE clause you can use the NULL-ness of columns that are always not-NULL (such as Primary Key columns, or the column you are joining on) to detect whether there was a row meeting the join condition or not.  For instance, these two SQL statement should return the same result set:

select ClientID
from Clients
where WebSiteID not in (select WebSiteID
                                        from WebSites)

select c.ClientID
from Clients as c
left outer join WebSites as w on w.WebSiteID = c.WebSiteID
where w.WebSiteID is not null
and c.WebSiteID is not null

I guess I didn't meet your request that this be "briefly explained" - but I hope you can see what is going on in my delete statement now...
0
 

Author Comment

by:dotnet0824
ID: 20407856
Excellent I felt like increasing the points for you. Thanks again.
0
 

Author Comment

by:dotnet0824
ID: 20415796
Hi lahousden:
Sorry.. Its my mistake I need to pass Student_ID  as parameter its value may be 1 lets say
if  the value exists in either table B or Table C then I shouldnt delete or Else I can delete . Can this be given sorry about that
0
 
LVL 10

Expert Comment

by:lahousden
ID: 20416070
Sounds like you need to do an SP to keep it organised, e.g.:

CREATE PROCEDURE Delete_Student_If_Childless
 @pi_Student_ID int
as
 begin

  declare @li_answer int
  declare @li_error int
  declare @li_rowcount int
  declare @li_childless int

  set nocount on

  begin transaction

  set @li_answer = 0 -- Initialise this to indicate success if it doesn't get changed
  set @li_error = 0

  set @li_there = 0

  select @li_there = 1, @li_childless = case
                                                             when b.Student_ID is not null
                                                             then 0
                                                             when c.Student_ID is not null
                                                             then 0
                                                             else 1
                                                            end
  from Table_A as a
  left outer join Table_B as b on b.Student_ID = a.Student_ID
  left outer join Table_C as c on c.Student_ID = a.Student_ID
  where a.Student_ID = @pi_Student_ID

  if 0 = @li_there
   begin
    set @li_answer = 1
   end
  else if 0 = @li_childless
   begin
    set @li_answer = 2
   end

  if 0 = @li_answer
   begin
    delete Table_A
    where Student_ID = @pi_Student_ID

    set @li_error = @@error

    if 0 <> @li_error
     begin
      set @li_answer = @li_error -- Pass back horrible error
     end
   end

  if = @li_error
   begin
    commit transaction
   end
  else
   begin
    rollback transaction
   end

  return @li_answer

 end

If you call this and the returned status is 0, then the Student was there, was childless and was deleted.  If the returned status is 1 then the student was already missing from Table A.  If the returned status was 2 then the student was there but couldn't be deleted because of child rows in Table B or Table C.  If the returned status is anything else then something horrible went wrong during the delete and the SQL Error code is the value returned.  If you don't like these return values you can change them around or simplify the status returned in some way that suits your purposes better.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now