mysql - 使用各种 SQL 语法创建自动关系

Question

我有许多数据库，外键(非正式)字段名称和主键字段名称对于以下代码中可见的示例相同。

CREATE TABLE Customer(
   CustomerId int PRIMARY,
   CustomerName varchar(200),
...
)
CREATE TABLE Invoice(
      InvoiceID int PRIMARY KEY,
      InvoiceType int ,
      CustomerID int)
CREATE TABLE InvoiceDet(
      InvoiceDetId int PRIMARY KEY,
      InvoiceId int ...)

我想要一个程序(或脚本)来读取我的表、列并创建外键引用。应该生成关系样本:Customer.CustomerId<=>Invoice.CustomerID和 InvoiceDet.InvoiceId<=>Invoice.InvoiceId

Answer 1

创建此类自动化脚本是一项艰巨的任务，尤其是对于您提到的所有数据库。您应该考虑聘请一些具有 Perl/Python 知识的优秀 DBA 来为您做这件事。

通过最少的检查(不交叉检查键中的列数和列的类型)，以下可能是 concept proof对于 PostgreSQL:

WITH colnames(oid,colnum,colname) AS (
    SELECT a.attrelid AS oid, a.attnum AS colnum, a.attname AS colname
      FROM pg_attribute a WHERE a.attnum > 0 AND NOT a.attisdropped),
pkcols(oid,colnum) AS (
    SELECT c.conrelid AS oid, unnest(c.conkey)
      FROM pg_constraint c
     WHERE contype = 'p'),
pks(oid,colnum,colname) AS (
    SELECT pkc.oid, pkc.colnum, cn.colname
      FROM pkcols pkc
      JOIN colnames cn ON pkc.oid = cn.oid AND pkc.colnum = cn.colnum),
pktabs AS (
    SELECT t.oid AS oid, n.nspname AS sname, t.relname AS tname,
           pks.colnum AS colnum, pks.colname AS colname
      FROM pg_class t
      JOIN pg_namespace n ON n.oid = t.relnamespace
      JOIN pks ON pks.oid = t.oid
     WHERE t.relkind = 'r'),
coltabs AS (
    SELECT t.oid AS oid, n.nspname AS sname, t.relname AS tname,
           cn.colnum AS colnum, cn.colname AS colname
      FROM pg_class t
      JOIN pg_namespace n ON n.oid = t.relnamespace
      JOIN colnames cn ON cn.oid = t.oid
     WHERE t.relkind = 'r')
SELECT 'ALTER TABLE '||quote_ident(ct.sname)||'.'||quote_ident(ct.tname)||
       ' ADD FOREIGN KEY ('||string_agg(quote_ident(pk.colname),',')||
       ') REFERENCES '||
       quote_ident(pk.sname)||'.'||quote_ident(pk.tname)||' ('||
       string_agg(quote_ident(pk.colname),',')||');'
  FROM pktabs pk
  JOIN coltabs ct ON pk.oid != ct.oid AND pk.colname = ct.colname
 GROUP BY pk.sname,pk.tname,ct.sname,ct.tname;