crossreference - Retrieve information about primary and foreign keys

Syntax

Description

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

Examples

Run crossreference to get primary and foreign key information. The database metadata object is dbmeta, the primary and foreign catalog is orcl, the primary and foreign schema is SCOTT, the table that contains the referenced primary key is DEPT, and the table that contains the foreign key is EMP.

f = crossreference(dbmeta,'orcl','SCOTT','DEPT',...
	'orcl','SCOTT','EMP')
f = Columns 1 through 7
    'orcl'   'SCOTT'   'DEPT'   'DEPTNO'    'orcl' ...
    'SCOTT'    'EMP'
Columns 8 through 13
    'DEPTNO'    '1'    'null'    '1'    'FK_DEPTNO'...
    'PK_DEPT' 

The results show the following primary and foreign key information.

Column	Description	Value
1	Catalog that contains primary key, referenced by foreign imported key	orcl
2	Schema that contains primary key, referenced by foreign imported key	SCOTT
3	Table that contains 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 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 updates	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

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

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

See Also

dmd | exportedkeys | get | importedkeys | primarykeys

© 1984-2012- The MathWorks, Inc.    -   Site Help   -   Patents   -   Trademarks   -   Privacy Policy   -   Preventing Piracy   -   RSS