Re: how to view the actual constraint code

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Thu, 25 Aug 2005 00:05:44 GMT
Message-Id: <pan.2005.08.25.00.05.43.963064@sbcglobal.net>

On Wed, 24 Aug 2005 12:46:25 -0700, Mark D Powell wrote:

> FK constraints can be recreated by joining data in dba_constraints and
> dba_cons_columns
>
> Or you can look at the dbms_metadata package. You can generate
> constraints as part of table definitions.
>
> HTH -- Mark D Powell --

DBMS_METADATA.GET_DDL will produce something like this:

CREATE TABLE "SCOTT"."EMP"

   (	"EMPNO" NUMBER(4,0), 
	"ENAME" VARCHAR2(10), 
	"JOB" VARCHAR2(9), 
	"MGR" NUMBER(4,0), 
	"HIREDATE" DATE, 
	"SAL" NUMBER(7,2), 
	"COMM" NUMBER(7,2), 
	"DEPTNO" NUMBER(2,0), 
	 CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

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

	 CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
	  REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE

   ) 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 "USERS" ; In my personal opinion, it is very ugly. In addition to that, in 9.2.0.6 on Linux, if the table contains any LOB column, it will not work. It is a "feature".

-- 
http://www.mgogala.com

Received on Wed Aug 24 2005 - 19:05:44 CDT