Solved

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

Posted on 2007-12-04
10
176 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Powershell error using sql agent job 24 41
SQL Simple Query Taking a Very Long Time 11 37
SQL Server 2012 and core licensing 5 32
sql server string_split 4 30
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

751 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