Generating FK constraints by matching column names

Question

I have a database structure, which two tables A & B.

A contains few columns including a column BiD as a foreign key reference to the primary key of table B.

Issue is however columns are defined but FK constraints are not present. So I am looking for a way to generate these constraints through some automated method as the real database I am dealing with has over hundred tables.

My database is in Oracle 11g and I also have its ERD model available in Enterprise Architect. Solution in any of these would work. Please suggest if there is any such possibility.

Justin Cave Justin Cave · Accepted Answer · 2016-08-25T07:06:34

all_tab_columns (or the dba_ or user_ version) will have column names and the table they're in. If you simply want to find all columns named BiD and create a foreign key constraint to B, you could do something like

BEGIN
  FOR child IN (SELECT owner, table_name, column_name
                  FROM dba_tab_columns
                 WHERE column_name = 'BID'
                   and owner IN (<<schemas you care about>>))
  LOOP
    EXECUTE IMMEDIATE 
      'ALTER TABLE ' || child.owner || '.' || child.table_name ||
      ' ADD CONSTRAINT fk_' || child.table_name || '_a FOREIGN KEY( bid ) ' ||
      ' REFERENCES b(bid) ';
  END LOOP;
END;

In a real system, you would probably want to have a local variable where you build up the SQL statement so you can log it before executing in case there is an error. If you have case-sensitive identifiers or if the constraint name you would generate using this pattern would be more than 30 characters, you'd need to add logic to handle those cases.

Generating FK constraints by matching column names

1 Answers