Re: Question on SQL, constraints, triggers, and Journaling

My original statement regarding journaling requirements was too broad. I
stand corrected. Thanks Rob et al.




From:
rob@xxxxxxxxx
To:
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date:
10/24/2008 08:59 AM
Subject:
Re: Question on SQL, constraints, triggers, and Journaling
Sent by:
midrange-l-bounces@xxxxxxxxxxxx



Any attempt to delete a row from a parent table that has a referential
constraint with a child file will result in SQL7008-3 if these files are
not journalled to the same journal.

CRTLIB MKIRK
STRSQL

CREATE TABLE MKIRK/PARENT

(PARENTNO INT AS IDENTITY, DUH CHAR ( 1) NOT NULL
WITH DEFAULT, CONSTRAINT PARENT_PRIKEY PRIMARY KEY (PARENTNO))

INSERT INTO MKIRK/PARENT (DUH) VALUES('A')
CREATE TABLE MKIRK/CHILD (PARENTNO INT , CHILDNO INT , DUH CHAR (

1) NOT NULL WITH DEFAULT, CONSTRAINT CHILD_PRIKEY PRIMARY KEY
(PARENTNO, CHILDNO), CONSTRAINT PARENT_CHILD FOREIGN KEY (PARENTNO)
REFERENCES MKIRK/PARENT (PARENTNO) ON DELETE NO ACTION ON UPDATE NO
ACTION)
Table CHILD in MKIRK created but was not journaled.

iNSERT INTO MKIRK/CHILD VALUES(1, 1, 'Y')

1 rows inserted in CHILD in MKIRK.

iNSERT INTO MKIRK/CHILD VALUES(2, 1, 'Y')

Operation not allowed by referential constraint PARENT_CHILD in MKIR

INSERT INTO MKIRK/PARENT (DUH) VALUES('u')

1 rows inserted in PARENT in MKIRK.

delete from mkirk/parent where duh='u'

PARENT in MKIRK not valid for operation. (SQL7008-3)

DSPMSGD SQL7008 MSGF(QSQLMSG)

Rob Berendt