From 1ac08fbb1a876da4563178f645e42386284b32f4 Mon Sep 17 00:00:00 2001 From: Alan Knowles Date: Tue, 7 Oct 2014 20:01:25 +0800 Subject: [PATCH] pgsql/schema_create_table_statement.sql --- pgsql/schema_create_table_statement.sql | 23 +++++++++++++++++++++-- 1 file changed, 21 insertions(+), 2 deletions(-) diff --git a/pgsql/schema_create_table_statement.sql b/pgsql/schema_create_table_statement.sql index eaa7dceb..f54c65fb 100644 --- a/pgsql/schema_create_table_statement.sql +++ b/pgsql/schema_create_table_statement.sql @@ -93,8 +93,27 @@ BEGIN END LOOP; -- constraints... - - + FOR column_record IN + + SELECT + tc.constraint_name, tc.table_name, kcu.column_name, + ccu.table_name AS foreign_table_name, + ccu.column_name AS foreign_column_name + FROM + information_schema.table_constraints AS tc + JOIN information_schema.key_column_usage AS kcu + ON tc.constraint_name = kcu.constraint_name + JOIN information_schema.constraint_column_usage AS ccu + ON ccu.constraint_name = tc.constraint_name + WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name=p_table_name; + + LOOP + + v_table_ddl:= v_table_ddl||chr(10)|| + 'ALTER TABLE ' ||v_schema||'.'||p_table_name||' ADD CONSTRAINT ' || column_record.constraint_name || + ' FOREIGN KEY (' || column_record.column_name || ') REFERENCES ' || foreign_table_name || '(' || foreign_column_name || ') MATCH SIMPLE'; + + END LOOP; -- 2.39.2