How to catch constraint violation in PL/SQL?

Question

CREATE TABLE LOCATION (
  LOCID VARCHAR2(5)
, MINQTY    NUMBER
, MAXQTY    NUMBER
, PRIMARY KEY   (LOCID)
, CONSTRAINT CHECK_LOCID_LENGTH CHECK (LENGTH(LOCID) = 5)
, CONSTRAINT CHECK_MINQTY_RANGE CHECK (MINQTY BETWEEN 0 AND 999)
, CONSTRAINT CHECK_MAXQTY_RANGE CHECK (MAXQTY BETWEEN 0 AND 999)
, CONSTRAINT CHECK_MAXQTY_GREATER_MIXQTY CHECK (MAXQTY >= MINQTY)
);


CREATE OR REPLACE PROCEDURE ADD_LOCATION_TO_DB(ploccode VARCHAR2, pminqty NUMBER, pmaxqty NUMBER) AS
BEGIN
INSERT INTO location(locid, minqty, maxqty) VALUES (ploccode, pminqty, pmaxqty);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20081, 'Duplicate Location ID');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20086,sqlerrm);
END;

I created the above table with constraints. Now I want to test these constraints in PL/SQL procedure by catching them in exceptions. But I'm confused how to do it.

Mechanician

sql-server doesn't use PL/SQL. Do you need a solution for both it and oracle? – Cab

I want PL/SQL code for a procedure that inserts a row in location table and want to test the constraints in that pl/sql procedure. But i'm not sure about the code of how to check constraint. – Mechanician

Yes, but you've tagged this question with two different products, and one of those products doesn't even have PL/SQL. Do you really need a solution for both products? – Cab

I removed the sql-server tag because the question is clearly about Oracle and PL/SQL. – Colorant

There is no predefined exception for a check constraint violation (see here: docs.oracle.com/cd/E11882_01/appdev.112/e25519/…) you need to check the error code in the when others handler – Isothermal

Answer 1

The error which occurs when a check constraint is violated is ORA-02290. Although there is no "standard" definition for this it's easy enough to declare your own exception so you can catch the -2290 when it's thrown. Let's say we have a table created as follows:

CREATE TABLE SOME_TABLE (COL1 CHAR(1) CHECK(COL1 IN ('Y', 'N')));

and that we then run the following block:

DECLARE
  -- First, declare and initialize an appropriate exception

  CHECK_CONSTRAINT_VIOLATED EXCEPTION;
  PRAGMA EXCEPTION_INIT(CHECK_CONSTRAINT_VIOLATED, -2290);
BEGIN
  INSERT INTO SOME_TABLE(COL1) VALUES ('X');  -- will violate the check constraint
  RETURN;
EXCEPTION
  WHEN CHECK_CONSTRAINT_VIOLATED THEN  -- catch the ORA-02290 exception
    DBMS_OUTPUT.PUT_LINE('INSERT failed due to check constraint violation');
  WHEN OTHERS THEN                     -- catch all other exceptions
    DBMS_OUTPUT.PUT_LINE('Something else went wrong - ' || SQLCODE ||
                         ' : ' || SQLERRM);
END;

If you create the table as shown earlier and then run the block above you'll find that the line 'INSERT failed due to check constraint violation' will show up on DBMS_OUTPUT.

Share and enjoy.

Lauralauraceous

Is there a way to identify which constraint was violated if the table has multiple check constraints? – Linnea

I believe that the name of the constraint will be in SQLERRM, along with other text. You could either print SQLERRM as shown above, or parse the text to find the constraint name. – Daph