Re: Primary Key definition from data dictionary

From: Kirmo Uusitalo <kirmo.uusitalo_at_iki.fi.nospam>
Date: 1997/07/11
Message-ID: <33c61437.66625682@news.kolumbus.fi>#1/1

On 11 Jul 1997 08:26:57 GMT, "ACE" <mario.simandl_at_ace.co.at> wrote:

>How can i get the definition of a foreign key from the ALL_CONSTRAINTS,
>ALL_CONS_COLUMNS?.
>The View ALL_CONS_COLUMNS Shows me only the columns of the foreign key
>table and not the
>references column. Who can help?

The foreign keys always refer to columns of referenced constraint. It is in the r_constraint_name column of all_constraints.

BTW, I have created a set of sql-scripts that can be used to create the create statements from an existing schema. This SQL*Create product is not freeware though. Email me if you're interested.

SQL>connect scott/tiger
SQL> @create

SQL*Create is a set of SQL tools to automate creation of create statements for an existing database. The tools in SQL*Create are:

create.sql   - the main starter application for SQL*Create
cre_tab.sql  - the creator of create table statements
cre_ind.sql  - the creator of create index statements
cre_syn.sql  - the creator of create synonym statements
cre_view.sql - the creator of create view statements
cre_fk.sql   - the creator of create constraint statements
cre_trig.sql - the creator of create trigger statements
cre_ins.sql  - the creator of insert statements
cre_seq.sql  - the creator of create sequence statements

(C) Kirmo Uusitalo, Key Technologies 1997 - kuu_at_iki.fi

All tables in database (1) or just current user's (0) ?:0 Synonyms (0/1) ?:1
Sequences (0/1) ?:1
Start sequences at current value (1) or minvalue (0) ?:1 Number of rows for insert statement (0: none, -1: all) ?:0 Spooling - wait a moment...
...
rem Table SCOTT.EMP has 19 rows.

create table SCOTT.EMP

  (EMPNO                NUMBER (4) NOT NULL
  ,ENAME                VARCHAR2 (10)
  ,JOB                  VARCHAR2 (9)
  ,MGR                  NUMBER (4)
  ,HIREDATE             DATE
  ,SAL                  NUMBER (7,2)
  ,COMM                 NUMBER (7,2)
  ,DEPTNO               NUMBER (2)

) tablespace USER_DATA
storage (initial 10240 next 10240 pctincrease 50);

create index EMP_JOB_MGR on SCOTT.EMP
(JOB ,MGR) tablespace USER_DATA
storage ( initial 10240 next 10240 pctincrease 50); rem
create index IND2_EMP on SCOTT.EMP
(SAL) tablespace USER_DATA
storage ( initial 10240 next 10240 pctincrease 50); rem
create unique index PK_EMP on SCOTT.EMP
(EMPNO) tablespace USER_DATA
storage ( initial 10240 next 10240 pctincrease 50); rem

alter table SCOTT.EMP add constraint PK_EMP primary key (EMPNO) ;
alter table SCOTT.EMP add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO) ;
alter table SCOTT.EMP add constraint CK_COMM check (comm>=0) ; alter table SCOTT.EMP add constraint CK_SAL check (sal > 0) ;

create or replace trigger scott.total_salary

   after delete or update of deptno,sal on scott.emp declare
  tot_sal number;
begin
-- nothing here
  select sum(sal) into tot_sal from emp;   update dept_budget set total_sal=tot_sal; end;
/

>/
>DROP TABLE KOPF;
>DROP TABLE ZEILE;
>/
>CREATE TABLE KOPF(
>K_NR NUMBER( 10, 0 ),
>K_KUNDE NUMBER( 10, 0 ),
>CONSTRAINT KOPF_PK PRIMARY KEY ( K_NR ) )
>/
>CREATE TABLE ZEILE(
>Z_NR NUMBER( 10, 0 ),
>Z_POS NUMBER( 10, 0 ),
>Z_ARTIKEL NUMBER( 10, 0 ),
>CONSTRAINT ZEILE_PK PRIMARY KEY( Z_NR, Z_POS ),
>CONSTRAINT ZEILE_FK FOREIGN KEY( Z_NR ) REFERENCES KOPF( K_NR ) )
>/
>SELECT * FROM ALL_CONSTRAINTS
>WHERE OWNER = 'BOKU' AND TABLE_NAME IN( 'KOPF', 'ZEILE' )
>/
>RESULT:
>CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER
>R_CONSTRAINT_NAME
>KOPF_PK P KOPF
>ZEILE_FK R ZEILE BOKU KOPF_PK NO ACTION
>ZEILE_PK P ZEILE
>END RESULT:
>/
>SELECT * FROM ALL_CONS_COLUMNS
>WHERE OWNER = 'BOKU' AND TABLE_NAME IN( 'KOPF', 'ZEILE' )
>/
>RESULT:
>CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
>KOPF_PK KOPF K_NR 1
>ZEILE_FK ZEILE Z_NR 1
>ZEILE_PK ZEILE Z_NR 1
>ZEILE_PK ZEILE Z_POS 2
>END RESULT:

Kirmo.Uusitalo_at_iki.fi                   Key Technologies Oy 
System Analyst                          Uotinmaenkuja 9
tel. +358-9-323451,+358-500-439125      00970  HELSINKI
fax         324031                      FINLAND

Received on Fri Jul 11 1997 - 00:00:00 CDT