PG Foreign key 外键
创建外键
单列外键
CREATE TABLE products ( product_no integer PRIMARY KEY, name text, ); #demo1 CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer REFERENCES products (product_no) ON DELETE CASCADE/CASCADE, ); #demo2 因为如果缺少列的列表,则被引用表的主键将被用作被引用列 CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer REFERENCES products ON DELETE CASCADE/CASCADE, ); #插入数据顺序 insert into products(product_no,name) values(1,'add1'),(2,'add2'),(3,'add3'); insert into orders(order_id,product_no) values(1,1),(2,2),(3,3); #删除数据顺序 delete from orders delete from products
组合外键
CREATE TABLE public.t1 ( a integer , b integer, c integer, constraint pk_t1 PRIMARY KEY(a, b) ); CREATE TABLE sc.t2 ( a integer , b integer, c integer, constraint pk_t2 PRIMARY KEY(c), constraint fk_t2_a_b FOREIGN KEY (a, b) REFERENCES t1 (a, b) );
CASCADE 特性
CREATE TABLE products ( product_no integer , name text, constraint pk_products PRIMARY KEY(product_no) ); CREATE TABLE orders ( order_id integer, product_no integer , constraint pk_orders PRIMARY KEY(order_id), constraint fk_orders_product_no FOREIGN KEY (product_no) REFERENCES products (product_no) ON DELETE CASCADE ); insert into products(product_no,name) values(1,'add1'),(2,'add2'),(3,'add3'); insert into orders(order_id,product_no) values(1,1),(2,2),(3,3); #CASCADE 这种类型的外键,删除被引用键的同时也会删除引用的所有行 # 以下删除操作将会删出products和orders的相关行 delete from products where product_no in (1,2,3) select * from products; select * from orders;
RESTRICT 特性
CREATE TABLE products ( product_no integer , name text, constraint pk_products PRIMARY KEY(product_no) ); CREATE TABLE orders ( order_id integer, product_no integer , constraint pk_orders PRIMARY KEY(order_id), constraint fk_orders_product_no FOREIGN KEY (product_no) REFERENCES products (product_no) ON DELETE RESTRICT ); insert into products(product_no,name) values(1,'add1'),(2,'add2'),(3,'add3'); insert into orders(order_id,product_no) values(1,1),(2,2),(3,3); #RESTRICT 这种类型的外键,不允许删除被应用的行 #以下删除操作将会失败 delete from products where product_no in (1,2,3) select * from products; select * from orders;
PSQL查询外键
创建多种外键用于测试
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TABLE public.t1 ( t1a integer , t1b integer, t1c integer, constraint pk_t1 PRIMARY KEY(t1a, t1b) ); CREATE TABLE dbo.t2 ( t2a integer , t2b integer, t2c integer, constraint pk_t2 PRIMARY KEY(t2c), constraint fk_t2_a_b FOREIGN KEY (t2a, t2b) REFERENCES t1 (t1a, t1b) ); CREATE TABLE public.t3 ( t3a integer , t3b integer, t3c integer, constraint pk_t3 PRIMARY KEY(t3a, t3b) ); CREATE TABLE dbo.t4 ( t4a integer , t4b integer, t4c integer, constraint pk_t4 PRIMARY KEY(t4c), constraint fk_t4_t4a_t4b FOREIGN KEY (t4a, t4b) REFERENCES t3 (t3b,t3a ) ); CREATE TABLE public.t5 ( t5a integer , t5b integer unique, t5c integer, constraint pk_t5 PRIMARY KEY(t5a) ); CREATE TABLE dbo.t6 ( t6a integer , t6b integer, t6c integer, constraint pk_t6 PRIMARY KEY(t6c), constraint fk_t6_t6b FOREIGN KEY (t6b) REFERENCES t5 (t5b ) );
查询外键相关信息
script1
--查询外键相关信息包含drop add 脚本 select * ,'alter table '||foreign_table||' drop constraint '||fk_name as DropScript ,'alter table '||foreign_table||' add constraint '||fk_name||' foreign key( ' ||fk_columns||')'||' references '||primary_table||'('||unique_columns||') on delete RESTRICT' as AddScript from( select foreign_table,fk_name, string_agg(t1.fk_column, ', ' order by fk_unique_column_position) as fk_columns, primary_table,unique_constraint_name, string_agg(t1.unique_column, ', ' order by fk_unique_column_position) as unique_columns from( select cu1.table_schema||'.'||cu1.table_name as foreign_table, r.constraint_schema,r.constraint_name as fk_name, cu1.column_name as fk_column,cu1.ordinal_position as fk_unique_column_position, cu2.table_schema||'.'||cu2.table_name as primary_table, r.unique_constraint_schema,r.unique_constraint_name, cu2.column_name as unique_column from information_schema.table_constraints tco join information_schema.referential_constraints r on tco.constraint_schema = r.constraint_schema and tco.constraint_name = r.constraint_name join information_schema.key_column_usage cu1 on r.constraint_schema=cu1.constraint_schema and r.constraint_name=cu1.constraint_name join information_schema.key_column_usage cu2 on r.unique_constraint_schema=cu2.constraint_schema and r.unique_constraint_name=cu2.constraint_name and cu2.ordinal_position =cu1.position_in_unique_constraint where tco.constraint_type = 'FOREIGN KEY' ) as t1 group by foreign_table,fk_name,primary_table,unique_constraint_name ) as result
结果
![](https://img2020.cnblogs.com/blog/569950/202008/569950-20200808151054982-1698887927.png)
script2
--查询外键相关信息 不包含 select kcu.table_schema || '.' ||kcu.table_name as foreign_table, '>-' as rel, rel_tco.table_schema || '.' || rel_tco.table_name as primary_table, string_agg(kcu.column_name, ', ') as fk_columns, kcu.constraint_name from information_schema.table_constraints tco join information_schema.key_column_usage kcu on tco.constraint_schema = kcu.constraint_schema and tco.constraint_name = kcu.constraint_name join information_schema.referential_constraints rco on tco.constraint_schema = rco.constraint_schema and tco.constraint_name = rco.constraint_name join information_schema.table_constraints rel_tco on rco.unique_constraint_schema = rel_tco.constraint_schema and rco.unique_constraint_name = rel_tco.constraint_name where tco.constraint_type = 'FOREIGN KEY' group by kcu.table_schema, kcu.table_name, rel_tco.table_name, rel_tco.table_schema, kcu.constraint_name order by kcu.table_schema, kcu.table_name;
结果