Home » SQL & PL/SQL » SQL & PL/SQL » consistency constraint ON store Procedure (12c, window)
consistency constraint ON store Procedure [message #671287] Tue, 21 August 2018 01:39 Go to next message
Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan
Senior Member
Dear Experts.
i am creating procedure but need help.
i create table and then inserted data like:

CREATE TABLE TRIPLEG
    (
        T#                NUMBER(10)       NOT NULL,
        LEG#              NUMBER(2)        NOT NULL,
        DEPARTURE         VARCHAR(30)      NOT NULL,
        DESTINATION       VARCHAR(30)      NOT NULL,
        CONSTRAINT  TRIPLEG_PKEY PRIMARY KEY (T#, LEG#)
     );

INSERT INTO TRIPLEG VALUES( 1, 1, 'Sydney', 'Melbourne');
INSERT INTO TRIPLEG VALUES( 1, 2, 'Melbourne', 'Adelaide');
INSERT INTO TRIPLEG VALUES( 1, 3, 'Adelaide', 'Sydney');
INSERT INTO TRIPLEG VALUES( 2, 1, 'Sydney', 'Melbourne');
INSERT INTO TRIPLEG VALUES( 2, 2, 'Melbourne', 'Adelaide');
INSERT INTO TRIPLEG VALUES( 2, 3, 'Adelaide', 'Sydney');
INSERT INTO TRIPLEG VALUES( 3, 1, 'Sydney', 'Melbourne');
INSERT INTO TRIPLEG VALUES( 3, 2, 'Melbourne', 'Adelaide');
INSERT INTO TRIPLEG VALUES( 3, 3, 'Adelaide', 'Sydney');
INSERT INTO TRIPLEG VALUES( 4, 1, 'Sydney', 'Melbourne');

we have data like this:
     T#       LEG# DEPARTURE                      DESTINATION
---------- ---------- ------------------------------ ------------------------------
         1          1 Sydney                         Melbourne
         1          2 Melbourne                      Adelaide
         1          3 Adelaide                       Sydney
         2          1 Sydney                         Melbourne
         2          2 Melbourne                      Adelaide
         2          3 Adelaide                       Sydney
         3          1 Sydney                         Melbourne
         3          2 Melbourne                      Adelaide
         3          3 Adelaide                       Sydney
         4          1 Sydney                         Melbourne

after that i create a simple store procedure like
CREATE OR REPLACE PROCEDURE INSERT_TRIPLEG
(
V_T# IN TRIPLEG.T#%TYPE,
V_LEG# IN TRIPLEG.LEG#%TYPE,
V_DEPARTURE IN TRIPLEG.DEPARTURE%TYPE,
V_DESTINATION IN TRIPLEG.DESTINATION%TYPE
)
IS 
BEGIN

INSERT INTO TRIPLEG(T#, LEG#, "DEPARTURE", "DESTINATION")
VALUES (V_T#, V_LEG#, V_DEPARTURE, V_DESTINATION);


COMMIT;
END;

i want to enforces the following consistency constraint on table TRIPLEG.

For all trip legs that belong to the same trip and such that leg_number > n where n > 1

a value of departure attribute must be equal to a value of destination attribute in a trip leg
where leg_number = n-1.
It means that for all trip legs except the first one a departure location must be the same as a destination location of the previous trip leg.

To simplify the problem, assume that the rows are inserted into a relational table TRIPLEG in the increasing order of an attribute leg_number and that a row where a value of attribute leg_number = n is immediately followed by a row where a value of attribute leg_number = n + 1.


please help me to handle this problem, thanks and regards,
Re: consistency constraint ON store Procedure [message #671288 is a reply to message #671287] Tue, 21 August 2018 02:34 Go to previous messageGo to next message
John Watson
Messages: 8347
Registered: January 2010
Location: Global Village
Senior Member
You cannot do that with a declarative constraint because that would be a constraint of the "assertion" type and Oracle does not support assertions. There are ways to simulate assertions, look them up and decide if you want to do it that way. Assertions are quite a hot topic in relational engineering.

You cannot do it with a trigger, because the the trigger would not be able to query the table.

So you have to do it with more code in your procedure: do all the checks there, before inserting the row.
Re: consistency constraint ON store Procedure [message #671289 is a reply to message #671288] Tue, 21 August 2018 02:39 Go to previous messageGo to next message
Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan
Senior Member
thanks dear,
please help me little bit to start it, as no idea is in my mine to handle this problem,
Re: consistency constraint ON store Procedure [message #671290 is a reply to message #671289] Tue, 21 August 2018 02:42 Go to previous messageGo to next message
John Watson
Messages: 8347
Registered: January 2010
Location: Global Village
Senior Member
I'm not writing your code for you, particularly if you can't even be bothered to google "assertions" or tried writing any code for yourself. Earn your salary. Do some work.
Re: consistency constraint ON store Procedure [message #671291 is a reply to message #671290] Tue, 21 August 2018 02:53 Go to previous messageGo to next message
Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan
Senior Member
Thanks dear, i am trying it but could not find the solution.
Re: consistency constraint ON store Procedure [message #671292 is a reply to message #671291] Tue, 21 August 2018 03:00 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
If I understand the requirement correctly, try using analytic functions in the logical check. Beyond that, show people what you have tried and they'll be more likely to help.

"I've tried nothing and am all out of ideas" doesn't typically garner much sympathy.
Re: consistency constraint ON store Procedure [message #671293 is a reply to message #671288] Tue, 21 August 2018 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There was Toon Kopelaars and missing Lex de Haan's RuleGen for an implementation of (some) assertions in Oracle but unfortunately it seems the product is now dead as the site (http://www.rulegen.com) no more exists.
You can see Tom Kyte's opinion on it there.

If anyone has news about this product, please give us.


Re: consistency constraint ON store Procedure [message #671294 is a reply to message #671293] Tue, 21 August 2018 03:10 Go to previous messageGo to next message
Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan
Senior Member
Thanks dear,
i am reviewing it and let you know.
Re: consistency constraint ON store Procedure [message #671300 is a reply to message #671294] Tue, 21 August 2018 05:55 Go to previous messageGo to next message
Bilal Khan
Messages: 126
Registered: April 2010
Location: Pakistan
Senior Member
i am trying to make these changes but it display error message.
if V_T# = V_T1# and V_LEG# >1 
then 
INSERT INTO TRIPLEG(T#, LEG#, "DEPARTURE", "DESTINATION")
VALUES (V_T#, V_LEG#, V_DEPARTURE, V_DESTINATION);
--V_DEPARTURE = V_DESTINATION
end if;
Re: consistency constraint ON store Procedure [message #671302 is a reply to message #671300] Tue, 21 August 2018 06:38 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
Bilal Khan wrote on Tue, 21 August 2018 05:55
i am trying to make these changes but it display error message.
if V_T# = V_T1# and V_LEG# >1 
then 
INSERT INTO TRIPLEG(T#, LEG#, "DEPARTURE", "DESTINATION")
VALUES (V_T#, V_LEG#, V_DEPARTURE, V_DESTINATION);
--V_DEPARTURE = V_DESTINATION
end if;
I don't see any error message. Are we supposed to guess?
Re: consistency constraint ON store Procedure [message #671310 is a reply to message #671302] Tue, 21 August 2018 07:36 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
The following after statement trigger will validate your table entries and remove anyone that is in error.

CREATE OR REPLACE TRIGGER TRIPLEG_T1
AFTER INSERT OR UPDATE
ON TRIPLEG
BEGIN
    FOR Pnt IN (SELECT A.T#,
                       A.Leg#,
                       A.Departure,
                       A.Destination,
                       LAG(A.Destination,
                           1,
                           NULL)
                           OVER(PARTITION BY A.T# ORDER BY A.T#, A.Leg#)
                           Last_dest
                FROM Tripleg A
                ORDER BY A.T#, A.Leg#) LOOP
        IF Pnt.Last_dest IS NOT NULL AND Pnt.Departure <> Pnt.Last_dest THEN
            DELETE Tripleg A
            WHERE A.T# = Pnt.T# AND A.Leg# = Pnt.Leg#;

        END IF;
    END LOOP;
END;
Re: consistency constraint ON store Procedure [message #671313 is a reply to message #671310] Tue, 21 August 2018 07:52 Go to previous messageGo to next message
John Watson
Messages: 8347
Registered: January 2010
Location: Global Village
Senior Member
Bill, will you not need to lock the table first? Otherwise (I may be wrong) I don't see how it can be guaranteed to work in a multiuser environment.
Re: consistency constraint ON store Procedure [message #671315 is a reply to message #671313] Tue, 21 August 2018 08:06 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
I don't think it would be a problem because Oracle won't let 2 people enter a row for the same primary key and the only way it could fail is if user one entered a lower number leg# for the same trip and doesn't commit and then user 2 enters a higher number leg# for the same trip. While certainly possible the chance of that happening is extremely low. but an exclusive lock on the table in the after statement trigger would certainly fix the possibility.

[Updated on: Tue, 21 August 2018 08:07]

Report message to a moderator

Re: consistency constraint ON store Procedure [message #671316 is a reply to message #671287] Tue, 21 August 2018 08:16 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2986
Registered: January 2010
Location: Connecticut, USA
Senior Member
So you want inserted leg departure to reference previous leg destination, right? Issue is first leg where there is no previous destination. We can use virtual columns to bypass it:

CREATE TABLE TRIPLEG(
                     T#                 NUMBER(10)       NOT NULL,
                     LEG#               NUMBER(2)        NOT NULL,
                     DEPARTURE          VARCHAR(30)      NOT NULL,
                     DESTINATION        VARCHAR(30)      NOT NULL,
                     PARENT_LEG#        NUMBER(2)   GENERATED ALWAYS AS (GREATEST(1,LEG# - 1)) NOT NULL,
                     ADJUSTED_DEPARTURE VARCHAR(30) GENERATED ALWAYS AS (
                                                                         CASE LEG#
                                                                           WHEN 1 THEN DESTINATION
                                                                           ELSE DEPARTURE
                                                                         END
                                                                        ) NOT NULL
                    )
/
ALTER TABLE TRIPLEG
  ADD CONSTRAINT TRIPLEG_PKEY
    PRIMARY KEY(T#,LEG#)
/
ALTER TABLE TRIPLEG
  ADD CONSTRAINT TRIPLEG_UKEY
    UNIQUE(T#,LEG#,DESTINATION)
/
ALTER TABLE TRIPLEG
  ADD CONSTRAINT TRIPLEG_FKEY
    FOREIGN KEY(T#,PARENT_LEG#,ADJUSTED_DEPARTURE)
    REFERENCES TRIPLEG(T#,LEG#,DESTINATION)
/

We would have to provide column list excluding virtual columns when inserting:

SQL> INSERT INTO TRIPLEG(T#,LEG#,DEPARTURE,DESTINATION) VALUES(1,1,'Sydney','Melbourne');

1 row created.

SQL> INSERT INTO TRIPLEG(T#,LEG#,DEPARTURE,DESTINATION) VALUES(1,2,'Melbourne','Adelaide');

1 row created.

SQL> INSERT INTO TRIPLEG(T#,LEG#,DEPARTURE,DESTINATION) VALUES(1,3,'Adelaide','Sydney');

1 row created.

SQL> INSERT INTO TRIPLEG(T#,LEG#,DEPARTURE,DESTINATION) VALUES(2,1,'Sydney','Melbourne');

1 row created.

SQL> INSERT INTO TRIPLEG(T#,LEG#,DEPARTURE,DESTINATION) VALUES(2,2,'Melbourne','Adelaide');

1 row created.

SQL> INSERT INTO TRIPLEG(T#,LEG#,DEPARTURE,DESTINATION) VALUES(2,3,'Adelaide','Sydney');

1 row created.

SQL> INSERT INTO TRIPLEG(T#,LEG#,DEPARTURE,DESTINATION) VALUES(3,1,'Sydney','Melbourne');

1 row created.

SQL> INSERT INTO TRIPLEG(T#,LEG#,DEPARTURE,DESTINATION) VALUES(3,2,'Melbourne','Adelaide');

1 row created.

SQL> INSERT INTO TRIPLEG(T#,LEG#,DEPARTURE,DESTINATION) VALUES(3,3,'Adelaide','Sydney');

1 row created.

SQL> INSERT INTO TRIPLEG(T#,LEG#,DEPARTURE,DESTINATION) VALUES(4,1,'Sydney','Melbourne');

1 row created.

SQL> COMMIT
  2  /

Commit complete.

SQL> 

Now we try inserting leg 4 for trip 1 with departure != 'Sydney' which is leg 3 destination:

SQL> INSERT INTO TRIPLEG(T#,LEG#,DEPARTURE,DESTINATION) VALUES(1,4,'Brisbane','Perth')
  2  /
INSERT INTO TRIPLEG(T#,LEG#,DEPARTURE,DESTINATION) VALUES(1,4,'Brisbane','Perth')
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.TRIPLEG_FKEY) violated - parent key not found


SQL> 

And now we insert leg 4 for trip 1 with departure = 'Sydney' which is leg 3 destination:

SQL> INSERT INTO TRIPLEG(T#,LEG#,DEPARTURE,DESTINATION) VALUES(1,4,'Sydney','Perth')
  2  /

1 row created.

SQL> 

SY.
P.S. The above will not work if you need to insert a leg between existing legs. For that you will have to either delete and re-insert whole trip or make PARENT_LEG# non-virtual column and make FK deferrable.

[Updated on: Tue, 21 August 2018 08:24]

Report message to a moderator

Re: consistency constraint ON store Procedure [message #671317 is a reply to message #671316] Tue, 21 August 2018 08:20 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
Soloman,
Very elegant solution. I had not noticed that they were on 12C. Sweet!!
Re: consistency constraint ON store Procedure [message #671318 is a reply to message #671317] Tue, 21 August 2018 08:26 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2986
Registered: January 2010
Location: Connecticut, USA
Senior Member
Bill,

This solution should work in any version where virtual columns are supported.

SY.
Re: consistency constraint ON store Procedure [message #671319 is a reply to message #671318] Tue, 21 August 2018 08:28 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
Agreed, Virtual columns started in 11. I work on Oracle 8, 10, 11, and 12 at my shop. They tend to blend together after a while. LOL.

The one change I would make to your solution is to make the virtual columns invisible.

CREATE TABLE TRIPLEG
(
T# NUMBER(10) NOT NULL,
LEG# NUMBER(2) NOT NULL,
DEPARTURE VARCHAR2(30 BYTE) NOT NULL,
DESTINATION VARCHAR2(30 BYTE) NOT NULL,
PARENT_LEG# NUMBER(2) INVISIBLE GENERATED ALWAYS AS (GREATEST(1,LEG#-1)) NOT NULL,
ADJUSTED_DEPARTURE VARCHAR2(30 BYTE) INVISIBLE GENERATED ALWAYS AS (CASE LEG# WHEN 1 THEN DESTINATION ELSE DEPARTURE END) NOT NULL
)

[Updated on: Tue, 21 August 2018 08:32]

Report message to a moderator

Re: consistency constraint ON store Procedure [message #671320 is a reply to message #671319] Tue, 21 August 2018 08:30 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Bill B wrote on Tue, 21 August 2018 14:28
Oracle 8
Wow, I thought we were bad for 9i...

You should speak to https://www.amnesty.org/en/, I'm pretty sure it's a warcrime at this point

[Updated on: Tue, 21 August 2018 08:30]

Report message to a moderator

Re: consistency constraint ON store Procedure [message #671321 is a reply to message #671316] Tue, 21 August 2018 08:31 Go to previous messageGo to next message
John Watson
Messages: 8347
Registered: January 2010
Location: Global Village
Senior Member
That is well cool.
One could optimize it a bit: create an index on T#,LEG#,DESTINATION before defining the primary key and add an index on T#,PARENT_LEG#,ADJUSTED_DEPARTURE to avoid foreign key locking problems. Then make the virtual columns invisible to avoid confusion.
Re: consistency constraint ON store Procedure [message #671322 is a reply to message #671320] Tue, 21 August 2018 08:42 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
Roachcoach wrote on Tue, 21 August 2018 09:30
Bill B wrote on Tue, 21 August 2018 14:28
Oracle 8
Wow, I thought we were bad for 9i...

You should speak to https://www.amnesty.org/en/, I'm pretty sure it's a warcrime at this point
LMAO!!
I use Oracle 8I as a swear word. Every time I have to write a hack to get around Oracle 8i limitations I tell them If only the system was on Oracle 12. We are scheduled to upgrade the Retail system using the 8i database within the year and upgrade to 12.2 or above. I am so looking forward to it. lol
Re: consistency constraint ON store Procedure [message #671323 is a reply to message #671319] Tue, 21 August 2018 08:43 Go to previous messageGo to next message
John Watson
Messages: 8347
Registered: January 2010
Location: Global Village
Senior Member
Bill B wrote on Tue, 21 August 2018 14:28
Agreed, Virtual columns started in 11. I work on Oracle 8, 10, 11, and 12 at my shop. They tend to blend together after a while. LOL.

<snip>
)
Virtual columns have been around for a while, we just weren't supposed to know about them. I remember first seeing them in Label Security which I think was indeed release 8. In this example, I'm creating a constraint on one created implicitly for a function based index. No idea if it would work on older releases:
orclx> create table t1(c1 varchar2(10));

Table created.

orclx> create index fbi on t1(upper(c1));

Index created.

orclx> select column_name,virtual_column,hidden_column from user_tab_cols where table_name='T1';

COLUMN_NAME                    VIR HID
------------------------------ --- ---
C1                             NO  NO
SYS_NC00002$                   YES YES

orclx> alter table t1 add constraint uk unique(SYS_NC00002$);

Table altered.

orclx>
Re: consistency constraint ON store Procedure [message #671324 is a reply to message #671322] Tue, 21 August 2018 08:45 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Good luck, we've been "moving away" from the 9i platform for well over 5 years now Wink
Re: consistency constraint ON store Procedure [message #671326 is a reply to message #671323] Tue, 21 August 2018 08:52 Go to previous message
Solomon Yakobson
Messages: 2986
Registered: January 2010
Location: Connecticut, USA
Senior Member
IMHO, using system generated names is bad idea. First of all, unique(SYS_NC00002$) is not that readable at all. But main issue is - it is not scriptable since you never know what that system generated name will be. I wouldn't suggest it unless it is 8i which doesn't support directly declared virtual columns.

SY.
Previous Topic: Running settlement of inward vs outward quantity
Next Topic: find circular reference in data
Goto Forum:
  


Current Time: Sat Sep 26 00:22:28 CDT 2020