473,320 Members | 2,029 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Need help writing sql to check for records that invalidate a foreign key

Hi I am trying to write some sql to because we have a missing foregin key constraint. The following is what I have been asked to do:

1. COMPULSORY_WORK.reference to LISTED_BUILDING.reference

2. The constraint name should be COMPULSORY_WORK_FK

3. Provide data check SQL for records which will invalidate the foreign key

I have created the constraint with the code:

Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE COMPULSORY_WORK ADD (
  2.   CONSTRAINT COMPULSORY_WORK_FK FOREIGN KEY (REFERENCE)
  3.     REFERENCES LISTED_BUILDING (REFERENCE));
but before this I am meant to "Provide data check SQL for records which invalidate the foreign key." This is the part I don't know how to do as I am a beginner at SQL.

Below is an example of a completely different requirement and I am assuming I should be editing the code in the select statement, I have no idea what to type though..

Expand|Select|Wrap|Line Numbers
  1. SET SERVEROUT ON
  2. DECLARE
  3.   CURSOR GET_PARAMETER IS
  4.     SELECT UPPER(PARAMETER_NAME) PARAMETER_NAME
  5.     FROM STANDARD_PARAMETER
  6.     GROUP BY UPPER(PARAMETER_NAME) HAVING COUNT(*) > 1; 
  7.  
  8. BEGIN
  9.   FOR REC IN GET_PARAMETER LOOP
  10.     DBMS_OUTPUT.PUT_LINE('UNABLE TO CREATE AN UPPER KEY CONSTRAINT ON THE STANDARD_PARAMETER TABLE BECAUSE THE PARAMETER '||  REC.PARAMETER_NAME||' IS DUPLICATED');
  11.  
  12.   END LOOP;
  13.  
  14. END;
Nov 21 '08 #1
3 2412
Pilgrim333
127 100+
Hi,

Do you need to write your own procedure to validate before you implement the foreign key? Or is a query that retrieves all the invalid data enough?

Pilgrim.
Nov 21 '08 #2
Pilgrim333
127 100+
If you are needed to check the contraint before, then the code below should be what you need

Expand|Select|Wrap|Line Numbers
  1. SET SERVEROUT ON 
  2. DECLARE 
  3.   CURSOR GET_INVALID_DATA IS 
  4.     SELECT DISTINCT A.FK
  5.     FROM   TABLE_WITH_FK A
  6.     WHERE  NOT EXISTS
  7.                  (SELECT 1
  8.                   FROM   TABLE_WITH_FK_AS_ID B
  9.                   WHERE  B.ID = A.FK
  10.                   AND    ROWNUM = 1
  11.                  )
  12.    ;  
  13.  
  14. BEGIN 
  15.   FOR REC IN GET_INVALID_DATA LOOP 
  16.     DBMS_OUTPUT.PUT_LINE('UNABLE TO CREATE A FOREIGN KEY CONSTRAINT ON THE TABLE_WITH_FK TABLE BECAUSE THE FOREIGN KEY '||  REC.FK ||' DOES NOT EXIST IN TABLE_WITH_FK_AS_ID'); 
  17.   END LOOP; 
  18.  
  19. END; 
  20.  
It should be something like this, please try and tell your results.

Pilgrim.
Nov 21 '08 #3
Hi thanks, this is exactly what I was after
Nov 24 '08 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: collincm | last post by:
Hi, I am trying to optimize a table for inserts. Half of the timeron cost is in the FK lookup! These tables for example CREATE TABLE FOO2( FOO2_ID INTEGER NOT NULL CONSTRAINT FOO2_PK...
9
by: Robert Schneider | last post by:
Hi to all, I don't understand that: I try to delete a record via JDBC. But I always get the error SQL7008 with the error code 3. It seems that this has something to do with journaling, since the...
19
by: James Fortune | last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't understand why people who know how to write code to completely replace a front end do not write something that will automate...
10
by: Tom | last post by:
I am looking for some ideas for how to design the layout of the form for data entry and to display the data for the following situation: There are many sales associates. A sales associate can work...
0
by: Miguel Dias Moura | last post by:
Hello, I am working on an Asp.Net 2.0 / SQL 2005 web site. I am using profile to save the users info on the database. For example, I have the following structure: Public Structure Name...
11
by: Alan Mailer | last post by:
A project I'm working on is going to use VB6 as a front end. The back end is going to be pre-existing MS Access 2002 database tables which already have records in them *but do not have any...
43
by: SLH | last post by:
hi people. im trying to validate input received via a text area on an ASP page before writing it to a database. i cant use client side javascript due to policy, so it all has to happen on the...
5
by: Will | last post by:
- I know enough ASP and Access to be dangerous :) - I need to put up a data base on our web server with 3 related tables. - They will be accessed by a limited number of people. - Each user will...
2
kcdoell
by: kcdoell | last post by:
Hello: I am trying to create a union query but do not have a lot of experience. Basically I have the below tables: The Tables: Table Name = tblPrior CreditRegIDFK; Number; Foreign Key...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.