SQL Primary Key validation in a stored procedure

Question

I have scoured the internet with no luck. I have attached links to the practice set I am working on, but I am creating a stored procedure that needs to update a row after checking if a foreign key from the one table matches with a corresponding primary key in another table. I'll attach my code so far (which isn't much), but I am honestly lost. I know how to create foreign key constraints like:

ALTER TABLE DRIVE
ADD CONSTRAINT TRUCK_NUM_FK FOREIGN KEY (TRUCK_NUM) REFERENCES TRUCK;

But I don't know how to do these things and more from a stored procedure. Thank you!

Practice Description: http://tinypic.com/r/2djxq4w/8 http://tinypic.com/r/sq61i1/8

CREATE OR REPLACE PROCEDURE TRUCK_RETURN (TR_NUM IN NUMBER,TR_MILE IN NUMBER) AS
BEGIN
IF
UPDATE

Use DRI ("ADD CONSTRAINT") and *not* a stored procedure to ensure basic relationships .. — user2864740, May 06 '14 at 15:46
It is not clear why do you need to check a foreign key in a procedure. Once you create a foreign key constraint, it is checked automatically on each operation on corresponding tables. — Guneli, May 06 '14 at 15:52
Yea, I don't think it makes much sense either. I would rather just add a constraint outside the procedure...but this practice problem seems to be asking me to do otherwise. Are their ways to put IF statements in my stored procedure to check these conditions? — Sw33tH2O, May 06 '14 at 15:58
How would you go about working this problem? Because it is requesting to display a custom message if the foreign key entered doesn't correspond with a primary key in another table, etc. Not really sure about this one. — Sw33tH2O, May 06 '14 at 21:15

Wesley Wood · Accepted Answer · 2014-05-07T02:38:38.607

This may not be exact but hopefully it will get you close:

CREATE OR REPLACE PROCEDURE TRUCK_RETURN (TR_NUM IN NUMBER,TR_MILE IN NUMBER) AS
BEGIN
IF TR_NUM IS NULL THEN
    Dbms_Output.PUT_LINE('Error: No truck number supplied.');
ELSE
    DECLARE TR NUMBER(10);
    SELECT TRUCK_NUM 
    INTO TR
    FROM TRUCK 
    WHERE TR_NUM = TRUCK_NUM;

    IF TR IS NOT NULL AND TR = TR_NUM THEN
       SELECT Truck_Mileage
           INTO TR
       FROM Truck
       WHERE Truck_Num = TR_NUM;

            IF TR_MILE >= TR THEN
                SELECT COUNT(*)
                INTO TR
                FROM DRIVE
                WHERE Truck_Num = TR_NUM
                      AND Drive_Status = ‘OUT’;

                IF TR = 1 THEN
                   UPDATE Drive
                   SET Drive_Status = ‘Received’
                   WHERE Truck_Num = TR_NUM
                   AND Drive_Status = ‘OUT’;
                ELSE 
                   Dbms_Output.PUT_LINE('Error: Truck has too many or not record of being out.’);
                END IF;
            ELSE
               Dbms_Output.PUT_LINE('Error: Truck mileage is less than previously recorded.’);
            END IF;
      ELSE 
         Dbms_Output.PUT_LINE('Error: Truck Number is incorrect.’);
      END IF;
END IF;
END TRUCK_RETURN;

SQL Primary Key validation in a stored procedure

1 Answers1