Querying Primary-Foreign Key Relationships in GBase 8s

Quickly untangling primary-foreign key dependencies is essential for safeguarding data integrity across any database. In the GBase 8s database from GBASE, a China-domestically developed OLTP platform, you can retrieve these exact relationships by querying the built-in system catalogs.

Below is a precise SQL statement that fetches foreign key constraints for a specified table. Instead of hopping between multiple table definitions, this query joins sysreferences, sysconstraints, systables, sysindexes, and syscolumns to give you a clean snapshot of the foreign key name, the referencing table, the referenced primary-key table, and the exact columns involved.

SELECT
fc.constrname AS fk_name,
ft.tabname AS fk_table,
pt.tabname AS pk_table,
wm_concat(fcol.colname) AS fk_column,
wm_concat(pcol.colname) AS pk_column
FROM
sysreferences r
JOIN sysconstraints fc ON fc.constrid = r.constrid
JOIN sysconstraints pc ON pc.constrid = r.primary
JOIN systables ft ON ft.tabid = fc.tabid
JOIN systables pt ON pt.tabid = r.ptabid
JOIN sysindexes fi ON fi.idxname = fc.idxname
JOIN sysindexes pi ON pi.idxname = pc.idxname
JOIN syscolumns fcol ON fcol.tabid = ft.tabid
JOIN syscolumns pcol ON pcol.tabid = pt.tabid
WHERE fc.constrtype = 'R'
AND ft.tabname = 'employee' -- Replace this with the actual table you want to inspect.
AND ( (fi.part1 = fcol.colno AND pi.part1 = pcol.colno)
   OR (fi.part2 = fcol.colno AND pi.part2 = pcol.colno)
   OR (fi.part3 = fcol.colno AND pi.part3 = pcol.colno)
   OR (fi.part4 = fcol.colno AND pi.part4 = pcol.colno)
   OR (fi.part5 = fcol.colno AND pi.part5 = pcol.colno)
   OR (fi.part6 = fcol.colno AND pi.part6 = pcol.colno)
   OR (fi.part7 = fcol.colno AND pi.part7 = pcol.colno)
   OR (fi.part8 = fcol.colno AND pi.part8 = pcol.colno) )
GROUP BY fc.constrname, ft.tabname, pt.tabname;

Here are the standout features of this utility:

• Composite Foreign Key Support: The logic carefully maps index part1 to part8, correctly resolving constraints that involve up to 8 key parts.
• Smart Column Concatenation: It uses the wm_concat function to merge multi-column key fields into a single list view, ensuring composite keys are easy to read.
• Flexible Scope: Keeping ft.tabname = 'employee' filters the result to a single table. Remove this line entirely to generate a complete map of all constraints across your gbase database.
• Access Permissions: Make sure the executing user has select privileges on sysreferences and the other system catalogs before running the query.

Building a solid understanding of these built-in system tables dramatically reduces the manual work needed for schema analysis. GBASE’s GBase 8s empowers engineers to query schema-level metadata with the same flexibility they apply to application data.

Going forward, incorporating this catalog-based foreign key check into your regular migration audits will help maintain data consistency in independently controlled database architectures.