Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-12-04
10
Medium Priority
?
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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
 

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 1200 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

715 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