Oracle PL/SQL Tutorial/Table/References

Reference column from another column

   <source lang="sql">

SQL> SQL> CREATE TABLE compact_discs (

 2     ID      INTEGER NOT NULL,
 3     title   VARCHAR2(60),
 4     artist  VARCHAR2(60),
 5     label   VARCHAR2(60),
 6     PRIMARY KEY (ID)
 7  );

Table created. SQL> SQL> INSERT INTO compact_discs VALUES (100, "W", "B", "A"); 1 row created. SQL> SQL> CREATE TABLE cd_keywords (

 2     cd_id        INTEGER NOT NULL REFERENCES compact_discs (id),
 3     keyword      VARCHAR2(60) NOT NULL,
 4     PRIMARY KEY  (cd_id, keyword)
 5  );

Table created. SQL> SQL> INSERT INTO cd_keywords VALUES (100, "L"); 1 row created. SQL> INSERT INTO cd_keywords VALUES (100, "M"); 1 row created. SQL> SQL> drop table cd_keywords; Table dropped. SQL> drop table compact_discs; Table dropped. SQL></source>

Refernece a table

   <source lang="sql">

SQL> set echo on SQL> SQL> create table p ( x int primary key ); Table created. SQL> SQL> create table c ( y references p ); Table created. SQL> SQL> SQL> insert into p values ( 1 ); 1 row created. SQL> SQL> SQL> insert into p values ( 2 ); 1 row created. SQL> SQL> SQL> column columns format a30 word_wrapped SQL> column tablename format a15 word_wrapped SQL> column constraint_name format a15 word_wrapped SQL> SQL> select table_name, constraint_name,

 2         cname1 || nvl2(cname2,","||cname2,null) ||
 3         nvl2(cname3,","||cname3,null) || nvl2(cname4,","||cname4,null) ||
 4         nvl2(cname5,","||cname5,null) || nvl2(cname6,","||cname6,null) ||
 5         nvl2(cname7,","||cname7,null) || nvl2(cname8,","||cname8,null)
 6              columns
 7    from ( select b.table_name,
 8                  b.constraint_name,
 9                  max(decode( position, 1, column_name, null )) cname1,
10                  max(decode( position, 2, column_name, null )) cname2,
11                  max(decode( position, 3, column_name, null )) cname3,
12                  max(decode( position, 4, column_name, null )) cname4,
13                  max(decode( position, 5, column_name, null )) cname5,
14                  max(decode( position, 6, column_name, null )) cname6,
15                  max(decode( position, 7, column_name, null )) cname7,
16                  max(decode( position, 8, column_name, null )) cname8,
17                  count(*) col_cnt
18             from (select substr(table_name,1,30) table_name,
19                          substr(constraint_name,1,30) constraint_name,
20                          substr(column_name,1,30) column_name,
21                          position
22                     from user_cons_columns ) a,
23                  user_constraints b
24            where a.constraint_name = b.constraint_name
25              and b.constraint_type = "R"
26            group by b.table_name, b.constraint_name
27         ) cons
28   where col_cnt > ALL
29           ( select count(*)
30               from user_ind_columns i
31              where i.table_name = cons.table_name
32                and i.column_name in (cname1, cname2, cname3, cname4,
33                                      cname5, cname6, cname7, cname8 )
34                and i.column_position <= cons.col_cnt
35              group by i.index_name
36           )
37  /

TABLE_NAME CONSTRAINT_NAME COLUMNS

---

--------------- ------------------------------

C SYS_C009529 Y SQL> SQL> SQL> drop table c; Table dropped. SQL> SQL> drop table p; Table dropped. SQL> SQL> SQL> SQL> SQL></source>