通过只知道模式和表名来删除 postgresql 中的主键约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47962406/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
drop primary key constraint in postgresql by knowing schema and table name only
提问by ArmanHunanyan
As I know the only way of dropping primary key in postgresql is
据我所知，在 postgresql 中删除主键的唯一方法是
ALTER TABLE schema.tableName DROP CONSTRAINT constraint_name;
ALTER TABLE schema.tableName DROP CONSTRAINT 约束名；
the constraint name by default is tableName_pkey however sometimes if table is already renamed I can't get original table name to construct right constraint name.
默认情况下，约束名称是 tableName_pkey 但是有时如果表已经重命名，我无法获得原始表名称来构造正确的约束名称。
For example is table created as A then renamed to B the constraint remains A_pkey but I only have B.
例如，表创建为 A 然后重命名为 B 约束仍然是 A_pkey 但我只有 B。
Do you know right way to drop pkey constraint by knowing only schema name and table name ?
你知道通过只知道模式名和表名来删除 pkey 约束的正确方法吗？
I am writing programm for doing this so I need to use only SQL queries. Solutions like "open pgAdmin and see the constraint name" will not work.
我正在为此编写程序，因此我只需要使用 SQL 查询。诸如“打开 pgAdmin 并查看约束名称”之类的解决方案将不起作用。
回答by zedfoxus
You can use information from the catalog tables like so:
您可以像这样使用目录表中的信息：
Create a table with id as the primary key
创建一个以id为主键的表
create table test1 (id int primary key, name text);
Create the SQL to drop the key
创建 SQL 以删除密钥
select concat('alter table public.test1 drop constraint ', constraint_name) as my_query
from information_schema.table_constraints
where table_schema = 'public'
and table_name = 'test1'
and constraint_type = 'PRIMARY KEY';
The result will be:
结果将是：
alter table public.test1 drop constraint test1_pkey
You can create a stored function to extract this query and then executeit.
您可以创建一个存储函数来提取此查询，然后再提取execute它。
回答by Ron Ballard
login to the database using psql, the command line tool.
使用命令行工具 psql 登录数据库。
Then type:
然后输入：
\d <table_name>
for example:
例如：
\d claim
Table "public.claim"
Column | Type | Collation | Nullable | Default
--------------------------------+-----------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('claim_id_seq'::regclass)
policy_id | integer | | |
person_id | integer | | |
incident_id | integer | | |
first_notification_of_loss | timestamp without time zone | | |
police_reference | character varying(40) | | |
photos_to_follow | boolean | | |
sketch_to_follow | boolean | | |
description_of_weather | character varying(2000) | | |
description_of_property_damage | character varying(2000) | | |
created_at | timestamp without time zone | | not null | now()
updated_at | timestamp without time zone | | not null |
Indexes:
"primary_key_claim" PRIMARY KEY, btree (id)
Foreign-key constraints:
"foreign_key_claim_incident" FOREIGN KEY (incident_id) REFERENCES incident(id)
"foreign_key_claim_person" FOREIGN KEY (person_id) REFERENCES person(id)
"foreign_key_claim_policy" FOREIGN KEY (policy_id) REFERENCES policy(id)
Referenced by:
TABLE "claimant" CONSTRAINT "foreign_key_claimant_claim" FOREIGN KEY (claim_id) REFERENCES claim(id)
TABLE "damage" CONSTRAINT "foreign_key_damage_claim" FOREIGN KEY (claim_id) REFERENCES claim(id)
TABLE "witness" CONSTRAINT "foreign_key_witness_claim" FOREIGN KEY (claim_id) REFERENCES claim(id)
This shows you the primary key name (as well as other stuff).
这会向您显示主键名称（以及其他内容）。
If you want to do this programmatically and you are using Java or another language that uses the JDBC interface, you can use the class DatabaseMetaData, method getPrimaryKeys.
如果您想以编程方式执行此操作，并且您正在使用 Java 或其他使用 JDBC 接口的语言，则可以使用 DatabaseMetaData 类和 getPrimaryKeys 方法。
Otherwise, the other answer, selecting from the system catalogs, is the way to go.
否则，从系统目录中选择的另一个答案就是要走的路。
