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 |
Bilal Khan
Messages: 128 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 |
John Watson
Messages: 8951 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 #671292 is a reply to message #671291] |
Tue, 21 August 2018 03:00 |
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 #671300 is a reply to message #671294] |
Tue, 21 August 2018 05:55 |
Bilal Khan
Messages: 128 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 #671310 is a reply to message #671302] |
Tue, 21 August 2018 07:36 |
Bill B
Messages: 1971 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 #671315 is a reply to message #671313] |
Tue, 21 August 2018 08:06 |
Bill B
Messages: 1971 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 |
Solomon Yakobson
Messages: 3285 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 #671319 is a reply to message #671318] |
Tue, 21 August 2018 08:28 |
Bill B
Messages: 1971 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 #671321 is a reply to message #671316] |
Tue, 21 August 2018 08:31 |
John Watson
Messages: 8951 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 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Roachcoach wrote on Tue, 21 August 2018 09:30Bill B wrote on Tue, 21 August 2018 14:28Oracle 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 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Bill B wrote on Tue, 21 August 2018 14:28Agreed, 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 #671326 is a reply to message #671323] |
Tue, 21 August 2018 08:52 |
Solomon Yakobson
Messages: 3285 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.
|
|
|
Goto Forum:
Current Time: Fri Sep 27 18:07:38 CDT 2024
|