SQL & PL/SQL

CREATE TABLE "DBO_CRMTEST1"."T_ACL_GROUP_MASTER"
+(+
+"USERGROUPCODE" VARCHAR2(5 CHAR) NOT NULL DISABLE,+
+"FUNCTIONID" VARCHAR2(15 CHAR) NOT NULL DISABLE,+
+"ACLADD" VARCHAR2(1 CHAR),+
+"ACLDELETE" VARCHAR2(1 CHAR),+
+"ACLMODIFY" VARCHAR2(1 CHAR),+
+"ACLVIEW" VARCHAR2(1 CHAR),+
+"CREATEDDATETIME" TIMESTAMP (3),+
+"CREATEDUSERID" VARCHAR2(10 CHAR),+
+"UPDATEDBY" VARCHAR2(10 CHAR),+
+"UPDATEDDATETIME" TIMESTAMP (3),+
CONSTRAINT "PK_T_ACL_GROUP_MASTER" PRIMARY KEY ("USERGROUPCODE", "FUNCTIONID") DISABLE,
CONSTRAINT "*FK_T_ACL_GROUP_MASTER_T_FUNCTI"* FOREIGN KEY ("FUNCTIONID") REFERENCES "DBO_CRMTEST1"."T_FUNCTION_MASTER" ("FUNCTIONID") DISABLE,
CONSTRAINT "FK_T_ACL_GROUP_MASTER_T_USER_M" FOREIGN KEY ("CREATEDUSERID") REFERENCES "DBO_CRMTEST1"."T_USER_MASTER" ("USERID") ENABLE NOVALIDATE
+)+
SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
+(+
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
+)+
TABLESPACE "USERS" ;

CREATE TABLE "DBO_CRMTEST1"."T_FUNCTION_MASTER"
+(+
+"FUNCTIONID" VARCHAR2(15 CHAR) NOT NULL DISABLE,+
+"MODULEID" VARCHAR2(15 CHAR) NOT NULL DISABLE,+
+"FUNCTIONNAME" VARCHAR2(50 CHAR) NOT NULL DISABLE,+
+"FUNCTIONIMAGE" VARCHAR2(50 CHAR),+
+"FUNCTIONDIR" VARCHAR2(100 CHAR),+
+"FUNCTIONFILE" VARCHAR2(100 CHAR),+
+"FUNCTIONSTATUS" VARCHAR2(1 CHAR),+
+"FUNCTIONIND" VARCHAR2(1 CHAR),+
+"FUNCTIONPRIORITY" VARCHAR2(15 CHAR),+
+"FUNCTIONSEQ" NUMBER(18,0),+
+"FSBANKACCESS" VARCHAR2(1 CHAR),+
+"FGROUPID" VARCHAR2(15 CHAR),+
CONSTRAINT "PK_T_FUNCTION_MASTER" PRIMARY KEY ("FUNCTIONID") DISABLE,
CONSTRAINT "FK_T_FUNCTION_MASTER_T_FGROUP_" FOREIGN KEY ("FGROUPID") REFERENCES "DBO_CRMTEST1"."T_FGROUP_MASTER" ("FGROUPID") DISABLE,
CONSTRAINT "FK_T_FUNCTION_MASTER_T_MODULE_" FOREIGN KEY ("MODULEID") REFERENCES "DBO_CRMTEST1"."T_MODULE_MASTER" ("MODULEID") DISABLE
+)+
SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
+(+
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
+)+
TABLESPACE "USERS" ;


I am trying to enable a constraint

ALTER TABLE T_ACL_GROUP_MASTER MODIFY CONSTRAINT FK_T_ACL_GROUP_MASTER_T_FUNCTI ENABLE

ALTER table T_ACL_GROUP_MASTER enable NOVALIDATE constraint FK_T_ACL_GROUP_MASTER_T_FUNCTI;

and i get this error which i do not know why.

Error starting at line 7 in command:
ALTER TABLE T_ACL_GROUP_MASTER MODIFY CONSTRAINT FK_T_ACL_GROUP_MASTER_T_FUNCTI ENABLE
Error report:
SQL Error: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement
gives a column-list for which there is no matching unique or primary
key constraint in the referenced table.
*Action: Find the correct column names using the ALL_CONS_COLUMNS
catalog view

Earlier i disable all my constraints and FK via procedure in a schema. the same procedure was used to enable back with the syntax.

Is there some understanding i am missing with enabling FK here ? Why am i not allow to enable the FK ?


create or replace
PROCEDURE JC_ENABLE_FK
IS
BEGIN
+for i IN (select table_name, constraint_name --disable first the foreign key+
from user_constraints
where constraint_type ='R'
and status = 'DISABLED'
and table_name like '%T_%')
loop
EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' enable NOVALIDATE constraint ' ||i.constraint_name;
end loop i;
END;

create or replace
PROCEDURE JC_ENABLE_CON
IS
BEGIN
for i IN (select table_name, constraint_name -- then disable all constraints
from user_constraints
where status = 'DISABLED'
and table_name like '%T_%')
loop
EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' enable NOVALIDATE constraint ' ||i.constraint_name;
+ -- execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT "'||cur.constraint_name||'" ENABLE ';+
+ -- execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT "'||cur.constraint_name||'" ENABLE NOVALIDATE ';+
end loop i;
END;

Edited by: DominicNg85 on Jul 29, 2011 5:13 AM