将表列移动到新表并在PostgreSQL中作为外键引用

本文介绍了将表列移动到新表并在PostgreSQL中作为外键引用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

假设我们有一个数据库表，其中包含字段 id， category， subcategory， brand， name， description等。有什么好方法为 category， subcategory和 brand 创建单独的表，并且原始表中的相应列和行成为外键引用？

Suppose we have a DB table with fields "id", "category", "subcategory", "brand", "name", "description", etc. What's a good way of creating separate tables for (eg.) "category", "subcategory" and "brand" and the corresponding columns and rows in the original table becoming foreign key references?

概述所涉及的操作：-在原始表的每一列中获取所有唯一值，这些值应成为外键； -为那些表创建-在原始表（或副本）中创建外键引用列

To outline the operations envolved: - get all unique values in each column of the original table which should become foreign keys; - create tables for those - create foreign key reference columns in original table (or a copy)

在这种情况下，将访问PostgreSQL数据库通过Ruby应用程序中的Sequel，所以可用的界面是命令行，Sequel，PGAdmin或（...）

In this case the PostgreSQL db is accessed via Sequel in a Ruby app, so available interfaces are the command line, Sequel, PGAdmin, or (...)

问题：您将如何做？ / p>

The question: how would you do this?

推荐答案

-- Some test data CREATE TABLE animals ( id SERIAL NOT NULL PRIMARY KEY , name varchar , category varchar , subcategory varchar ); INSERT INTO animals(name, category, subcategory) VALUES ( 'Chimpanzee' , 'mammals', 'apes' ) ,( 'Urang Utang' , 'mammals', 'apes' ) ,( 'Homo Sapiens' , 'mammals', 'apes' ) ,( 'Mouse' , 'mammals', 'rodents' ) ,( 'Rat' , 'mammals', 'rodents' ) ; -- [empty] table to contain the "squeezed out" domain CREATE TABLE categories ( id SERIAL NOT NULL PRIMARY KEY , category varchar , subcategory varchar , UNIQUE (category,subcategory) ); -- The original table needs a "link" to the new table ALTER TABLE animals ADD column category_id INTEGER -- NOT NULL REFERENCES categories(id) ; -- FK constraints are helped a lot by a supportive index. CREATE INDEX animals_categories_fk ON animals (category_id); -- Chained query to: -- * populate the domain table -- * initialize the FK column in the original table WITH ins AS ( INSERT INTO categories(category, subcategory) SELECT DISTINCT a.category, a.subcategory FROM animals a RETURNING * ) UPDATE animals ani SET category_id = ins.id FROM ins WHERE ins.category = ani.category AND ins.subcategory = ani.subcategory ; -- Now that we have the FK pointing to the new table, -- we can drop the redundant columns. ALTER TABLE animals DROP COLUMN category, DROP COLUMN subcategory; -- show it to the world SELECT a.* , c.category, c.subcategory FROM animals a JOIN categories c ON c.id = a.category_id ;