Hi everyone,
I created one table having this structure:
CREATE TABLE "CORRESPONDANCE_FOLDER" (
"PARENT_FLD" VARCHAR2(500) NOT NULL ENABLE,
"CHILD_FLD" VARCHAR2(500) NOT NULL ENABLE,
CONSTRAINT "UC_CORR" UNIQUE ("PARENT_FLD", "CHILD_FLD")
USING INDEX ENABLE,
CONSTRAINT "PARENT_NEQUAL_TO_CHILD_CHK" CHECK (PARENT_FLD <> CHILD_FLD) ENABLE )
- My need:
Is to create another CHECK CONSTRAINT, which makes to check that CHILD_FLD does not have to appear as much as PARENT_FLD.
For example:
| PARENT_FLD | CHILD_FLD
|
|---|
| AA000 | AA001 |
| AA000 | AA002 |
| AA001 | AA011 |
| AA012 | AA003 |
So here, the 'AA001' is already a sub folder of 'AA011; so following the RULE "It can't be a primary folder of any others folders".
My suggestion is to create a function like that :
CREATE FUNCTIONCheck_isChild (id_folder VARCHAR2)
RETURN INT
AS
DECLARE v_nb_child NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_nb_child
FROM CORRESPONDANCE_FOLDER
WHERE CHILD_FLD = id_folder;
RETURN v_nb_child;
END;
GO
ALTER TABLE CORRESPONDANCE_FOLDER
ADD CONSTRAINT "IS_NOT_CHILD_CHK" CHECK (Check_isChild(?????) == 0 ); -- I dont know what should i put in function parameter
GO
Thank you for help.