FK not checked ?

rd4004 · #1 (**permalink**) 11-26-09, 04:40

Hello !

I'm working on DB2 v8 on z/OS.

Here is my pbl.
I have th following tables :
- table TAB1
. col1 CHAR(6) NOT NULL
. col2 CHAR(1) NOT NULL
PK : col1, col2

- table TAB2
. colY CHAR(6)
. colZ CHAR(1)
FK : ColY, ColZ to table TAB1

NOTES : the column TAB1.col2 can support 3 values Blank, A and P. NULL is not allowed because of the primary Key.

When I insert a record into table F like this :
INSERT INTO TAB2
(colY,
colZ)
VALUES
('BLABLA',
NULL);
The FK doesn't check an error whereas I don't have any record in table TAB1 with a NULL in the column col2!!

Any idea ???
Thanks !!

tonkuma · #2 (**permalink**) 11-26-09, 05:37

It has no contradiction.

DB2 Universal Database for z/OS Version 8 SQL Reference
Chapter 1. DB2 concepts
Constraints
Referential constraints
P9

Quote:

The following rules provide referential integrity:

insert rule
A nonnull insert value of the foreign key must match some value of the
parent key of the parent table. The value of a composite foreign key is null
if any component of the value is null.

rd4004 · #3 (**permalink**) 11-26-09, 06:45

Thanks for your answer !

but is that means that if I do the following :
INSERT INTO TAB2
(colY,
colZ)
VALUES
(NULL,
'A');
the result will be the same ??

tonkuma · #4 (**permalink**) 11-26-09, 07:00

I think... Yes!

You should read second statement in my quote.
> The value of a composite foreign key is null
> if any component of the value is null.

Then, read again first statement.
> A nonnull insert value of the foreign key must match some value of the
> parent key of the parent table.

I couldn't find any other rule of referential integrity for insert.

rd4004 · #5 (**permalink**) 11-26-09, 08:09

I've tried and I've got a -545 ! In this case the FK is checked !!

tonkuma · #6 (**permalink**) 11-26-09, 10:39

-545 violates check constraint.
You can know which constraint violates by looking full message.

DB2 Version 9.1 for z/OS Codes GC18-9843-04.

Quote:

-545
THE REQUESTED OPERATION IS
NOT ALLOWED BECAUSE A ROW
DOES NOT SATISFY THE CHECK
CONSTRAINT check-constraint

tonkuma · #7 (**permalink**) 11-26-09, 11:08

Quote:

but is that means that if I do the following :
INSERT INTO TAB2
(colY,
colZ)
VALUES
(NULL,
'A');

It worked fine on DB2 9.7 for Windows.
So, there might be some more restrictions or functionalities on DB2 for z/OS.

Make tables

Code:

------------------------------ Commands Entered ------------------------------
CREATE table TAB1
(col1 CHAR(6) NOT NULL
,col2 CHAR(1) NOT NULL
,PRIMARY KEY(col1, col2)
,CONSTRAINT tab1_check_col2
 CHECK (col2 IN (' ', 'A', 'P') )
);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
CREATE table TAB2 
(colY CHAR(6) 
,colZ CHAR(1)
,CONSTRAINT tab2_fk_tab1
 FOREIGN KEY (ColY, ColZ)
 REFERENCES tab1
);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

INSERT data and results:

Code:

------------------------------ Commands Entered ------------------------------
INSERT INTO TAB2 
(colY, colZ)
VALUES
('BLABLA', NULL);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
INSERT INTO TAB2 
(colY, colZ)
VALUES
(NULL, 'A');
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM tab1;
------------------------------------------------------------------------------

COL1   COL2
------ ----

  0 record(s) selected.


------------------------------ Commands Entered ------------------------------
SELECT * FROM tab2;
------------------------------------------------------------------------------

COLY   COLZ
------ ----
BLABLA -   
-      A   

  2 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech