crossreference (Database Toolbox)

Get information about primary and foreign keys

Syntax

f = crossreference(dbmeta, 'pcata', 'psch', 'ptab', 'fcata', 'fsch', 
'ftab')

Description

f = crossreference(dbmeta, 'pcata', 'psch', 'ptab', 'fcata', 'fsch', 'ftab') returns information about the relationship between foreign keys and primary keys. Specifically, the information is for the database whose database metadata object is dbmeta, where dbmeta was created using dmd. The primary key information is for the table ptab, in the primary schema psch, of the primary catalog pcata. The foreign key information is for the foreign table ftab, in the foreign schema fsch, of the foreign catalog fcata.

Examples

Type

f = crossreference(dbmeta,'orcl','SCOTT','DEPT',...
    'orcl','SCOTT','EMP')

MATLAB returns

f = 
Columns 1 through 7
    'orcl'   'SCOTT'   'DEPT'   'DEPTNO'    'orcl'    'SCOTT'    'EMP'
Columns 8 through 13
    'DEPTNO'    '1'    'null'    '1'    'FK_DEPTNO'    'PK_DEPT'

In this example:

dbmeta is the database metadata object.
orcl is the catalog pcata and the catalog fcata.
SCOTT is the schema psch and the schema fsch.
DEPT is the table ptab that contains the referenced primary key.
EMP is the table ftab that contains the foreign key.

The results show the primary and foreign key information.

Column
Description
Value

1
Catalog containing primary key, referenced by foreign imported key
orcl

2
Schema containing primary key, referenced by foreign imported key
SCOTT

3
Table containing primary key, referenced by foreign imported key
DEPT

4
Column name of primary key, referenced by foreign imported key
DEPTNO

5
Catalog that has foreign key
orcl

6
Schema that has foreign key
SCOTT

7
Table that has foreign key
EMP

8
Foreign key column name, that is the column name that references the primary key in another table
DEPTNO

9
Sequence number within foreign key
1

10
Update rule, that is, what happens to the foreign key when the primary key is updated
null

11
Delete rule, that is, what happens to the foreign key when the primary key is deleted
1

12
Foreign imported key name
FK_DEPTNO

13
Primary key name in referenced table
PK_DEPT

Column	Description	Value
1	Catalog containing primary key, referenced by foreign imported key	`orcl`
2	Schema containing primary key, referenced by foreign imported key	`SCOTT`
3	Table containing primary key, referenced by foreign imported key	`DEPT`
4	Column name of primary key, referenced by foreign imported key	`DEPTNO`
5	Catalog that has foreign key	`orcl`
6	Schema that has foreign key	`SCOTT`
7	Table that has foreign key	`EMP`
8	Foreign key column name, that is the column name that references the primary key in another table	`DEPTNO`
9	Sequence number within foreign key	`1`
10	Update rule, that is, what happens to the foreign key when the primary key is updated	`null`
11	Delete rule, that is, what happens to the foreign key when the primary key is deleted	`1`
12	Foreign imported key name	`FK_DEPTNO`
13	Primary key name in referenced table	`PK_DEPT`

In the schema SCOTT, there is only one foreign key. The table DEPT contains a primary key DEPTNO that is referenced by the field DEPTNO in the table EMP. DEPTNO in the EMP table is a foreign key.

For a description of the codes for update and delete rules, see
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ DatabaseMetaData.html for the getCrossReference property.

See Also
dmd, exportedkeys, get, importedkeys, primarykeys

confds database