-- ------------------------------------------------------------------------
-- insert some test data: PK only
-- ------------------------------------------------------------------------
BEGIN
INSERT INTO arc_reftbl1 VALUES (1) ;
INSERT INTO arc_reftbl2 VALUES (2) ;
INSERT INTO arc_reftbl3 VALUES (3) ;
END;
--
-- test the constraints
--
INSERT INTO arc_ VALUES (1,1,NULL,NULL) ; -- ok
INSERT INTO arc_ VALUES (1,1,NULL,NULL) ; -- fails if executed again: PK!
INSERT INTO arc_ VALUES (2,1,1,1) ; -- must fail due to check constraint
INSERT INTO arc_ VALUES (2,1,1,NULL) ; -- must fail due to check constraint
INSERT INTO arc_ VALUES (2,NULL,NULL,NULL) ; -- must fail due to check constraint
INSERT INTO arc_ VALUES (3,9,NULL,NULL) ; -- fails: "parent key not found" (FK!)
INSERT INTO arc_ VALUES (3,NULL,9,NULL) ; -- fails: "parent key not found" (FK!)
INSERT INTO arc_ VALUES (3,NULL,NULL,9) ; -- fails: "parent key not found" (FK!)
--
-- all okay
--
BEGIN
INSERT INTO arc_ VALUES (2, 1, NULL, NULL) ;
INSERT INTO arc_ VALUES (3, NULL, 2, NULL) ;
INSERT INTO arc_ VALUES (4, NULL, NULL, 3) ;
END;
-- ------------------------------------------------------------------------
-- retrieve a list of constraints
-- ------------------------------------------------------------------------
SELECT *
FROM user_constraints
WHERE table_name LIKE 'ARC_%';