Re: FK constraints with system-generated names

From: Transalp <nick_williamson_at_mentor.com>
Date: 5 Apr 2007 07:23:57 -0700
Message-ID: <1175783037.942876.113050@n76g2000hsh.googlegroups.com>

On 5 Apr, 13:50, "sybrandb" <sybra..._at_gmail.com> wrote:

> connect to sqlplus
> issue set long <any high number>
> set pagesize newpage 0 heading off feedback off
> select dbms_metadata.get_ddl('TABLE',<table_name>,<schema>) from dual;

> Secondly could you run
> imp full=y (assuming the only user in the export) show=y
> log=<filename>
> or

> imp full=y indexfile=<any filename>
> This will show the statements actually executed.

1. In database #1 (the good one), dbms_metadata.get_ddl returns the following for one of the affected tables:

  CREATE TABLE "CHS"."CHSUSERPROPERTYPART"

   (	"USERPROPERTYPART_ID" VARCHAR2(255) NOT NULL ENABLE,
	"LIBRARYOBJECT_ID" VARCHAR2(255) NOT NULL ENABLE,
	"USERPROPERTY_ID" VARCHAR2(255) NOT NULL ENABLE,
	"USERPROPERTYVALUE" VARCHAR2(255),
	 CONSTRAINT "XPKCHSUSERPROPERTYPART" PRIMARY KEY

("USERPROPERTYPART_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)   TABLESPACE "CHS_INDEX" ENABLE,

	 FOREIGN KEY ("USERPROPERTY_ID")
	  REFERENCES "CHS"."CHSUSERPROPERTY" ("USERPROPERTY_ID") ENABLE
NOVALIDATE,
	 FOREIGN KEY ("LIBRARYOBJECT_ID")
	  REFERENCES "CHS"."LIBRARYOBJECT" ("LIBRARYOBJECT_ID") ENABLE

NOVALIDATE
   ) 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)   TABLESPACE "CHS" No FK constraint names there. Bizarrely though, if I choose "Show object DDL" for the exact same table in Enterprise Manager, I get this:

CREATE TABLE "CHS"."CHSUSERPROPERTYPART" ("USERPROPERTYPART_ID"     VARCHAR2(255 byte) NOT NULL, "LIBRARYOBJECT_ID" VARCHAR2(255     byte) NOT NULL, "USERPROPERTY_ID" VARCHAR2(255 byte) NOT     NULL, "USERPROPERTYVALUE" VARCHAR2(255 byte),     CONSTRAINT "CHSUSERPROPERTYPART_FK" FOREIGN KEY("USERPROPERTY_ID")     REFERENCES "CHS"."CHSUSERPROPERTY"("USERPROPERTY_ID")     NOVALIDATE,
    CONSTRAINT "CHSUSERPROPERTYPART_FK1" FOREIGN KEY("LIBRARYOBJECT_ID")
    REFERENCES "CHS"."LIBRARYOBJECT"("LIBRARYOBJECT_ID")     NOVALIDATE,
    CONSTRAINT "XPKCHSUSERPROPERTYPART" PRIMARY KEY("USERPROPERTYPART_ID")
    USING INDEX
    TABLESPACE "CHS_INDEX"
    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS     2147483645 PCTINCREASE 0 FREELISTS 0 FREELIST GROUPS 0)     PCTFREE 10 INITRANS 2 MAXTRANS 255)
    TABLESPACE "CHS" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS     255
    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS     2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)     LOGGING ...which *does* include FK names!

2) Running "imp full=y show=y log=<filename>" returns the following for the same table:

"CREATE TABLE "CHSUSERPROPERTYPART" ("USERPROPERTYPART_ID"
VARCHAR2(255) NOT"
" NULL ENABLE, "LIBRARYOBJECT_ID" VARCHAR2(255) NOT NULL ENABLE,
"USERPROPER"
"TY_ID" VARCHAR2(255) NOT NULL ENABLE, "USERPROPERTYVALUE"
VARCHAR2(255)) P"
"CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536
FREELIST"
"S 1 FREELIST GROUPS 1) TABLESPACE "CHS" LOGGING"
. . skipping table "CHSUSERPROPERTYPART"

"CREATE UNIQUE INDEX "XPKCHSUSERPROPERTYPART" ON
"CHSUSERPROPERTYPART" ("USE"
"RPROPERTYPART_ID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 655"
"36 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "CHS_INDEX" LOGGING"

"CREATE INDEX "CHSUSERPROPERTYPART_FKINDEX" ON
"CHSUSERPROPERTYPART" ("USERP"
"ROPERTY_ID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL
65536 FRE"
"ELISTS 1 FREELIST GROUPS 1) TABLESPACE "CHS_INDEX" LOGGING"

"CREATE INDEX "CHSUSERPROPERTYPART_FK1INDEX" ON
"CHSUSERPROPERTYPART" ("LIBR"
"ARYOBJECT_ID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL
65536 F"
"REELISTS 1 FREELIST GROUPS 1) TABLESPACE "CHS_INDEX" LOGGING"

"ALTER TABLE "CHSUSERPROPERTYPART" ADD CONSTRAINT
"XPKCHSUSERPROPERTYPART" "
"PRIMARY KEY ("USERPROPERTYPART_ID") USING INDEX PCTFREE 10 INITRANS
2 MAXTR"
"ANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
TABLESPACE "CH"
"S_INDEX" ENABLE "

"ALTER TABLE "CHSUSERPROPERTYPART" ADD FOREIGN KEY
("USERPROPERTY_ID") REFER"
"ENCES "CHSUSERPROPERTY" ("USERPROPERTY_ID") ENABLE NOVALIDATE"

"ALTER TABLE "CHSUSERPROPERTYPART" ADD FOREIGN KEY
("LIBRARYOBJECT_ID") REFE"
"RENCES "LIBRARYOBJECT" ("LIBRARYOBJECT_ID") ENABLE NOVALIDATE"
"ALTER TABLE "CHSUSERPROPERTYPART" ENABLE CONSTRAINT "XPKCHSUSERPROPERTYPART"
"""

...so it looks like the FK constraint names for this table are not in the DMP file. Weird...

TIA Received on Thu Apr 05 2007 - 09:23:57 CDT