如何获取Progress DB中表间关系及指定表的外键关联信息

Hey there! Let's break down how to figure out table relationships and verify foreign keys in Progress DB, especially for your OrderDetail table example with fields Date, Product_Id, Order_Id, and Quantity.

Progress OpenEdge maintains system catalog tables that store metadata about constraints, tables, and columns. You can query these to pull all cross-table relationships:

SELECT 
  ft.table_name AS foreign_table,
  fk.column_name AS foreign_column,
  pt.table_name AS primary_table,
  pk.column_name AS primary_column
FROM 
  sysconstraint sc
  JOIN syscolumn fk ON sc.constraint_id = fk.constraint_id AND sc.constraint_type = 'F'
  JOIN syscolumn pk ON sc.ref_constraint_id = pk.constraint_id
  JOIN systable ft ON fk.table_id = ft.table_id
  JOIN systable pt ON pk.table_id = pt.table_id
ORDER BY 
  foreign_table, foreign_column;

This query joins the system tables to map every foreign key column to its corresponding primary key table and column.

To confirm if Product_Id and Order_Id are foreign keys in OrderDetail, tweak the query above to filter for your target table:

SELECT 
  fk.column_name AS foreign_column,
  pt.table_name AS referenced_primary_table,
  pk.column_name AS referenced_primary_column
FROM 
  sysconstraint sc
  JOIN syscolumn fk ON sc.constraint_id = fk.constraint_id AND sc.constraint_type = 'F'
  JOIN syscolumn pk ON sc.ref_constraint_id = pk.constraint_id
  JOIN systable ft ON fk.table_id = ft.table_id
  JOIN systable pt ON pk.table_id = pt.table_id
WHERE 
  ft.table_name = 'OrderDetail'
ORDER BY 
  foreign_column;

If Product_Id and Order_Id show up in the results, they’re confirmed as foreign keys. You’ll also see which primary tables they link to—typically Product_Id points to a Product table’s primary key (like Id), and Order_Id links to an Order table’s primary key.

If you prefer a GUI or command-line approach instead of SQL:

• OpenEdge Explorer: Navigate to your database, find the OrderDetail table, and check the "Constraints" section—foreign keys will be listed with their referenced tables.
• Command Line with dbtool: Run this command to list all constraints for OrderDetail:

  dbtool -db your_database_name -listconstraints OrderDetail
  

  Look for entries marked as "FOREIGN KEY" to confirm Product_Id and Order_Id’s status.

内容的提问来源于stack exchange，提问作者cagin