通过仅知道架构和表名删除postgresql中主键约束条件

j9per5c4 于 2022-12-12 发布在 PostgreSQL

关注(0)|答案(3)|浏览(166)

As far I know the only way of dropping primary key in postgresql is:

ALTER TABLE schema.tableName DROP CONSTRAINT constraint_name;

the constraint name by default is tableName_pkey . However sometimes if table is already renamed I can’t get the original table name to construct right constraint name.
For example, for a table created as A then renamed to B the constraint remains A_pkey but I only have the table name B .
Do you know right way to drop the pkey constraint by knowing only the schema name and table name ?
I am writing program for doing this so I need to use only SQL queries. Solutions like "open pgAdmin and see the constraint name" will not work.

postgresql

来源：https://stackoverflow.com/questions/47962406/drop-primary-key-constraint-in-postgresql-by-knowing-schema-and-table-name-only

3条答案

按热度按时间

huus2vyu1#

You can use information from the catalog tables like so:
Create a table with id as the primary key

create table test1 (id int primary key, name text);

Create the SQL to drop the key

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 execute it.

赞(0）回复(0）举报 2022-12-12

zhte4eai2#

\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.
Otherwise, the other answer, selecting from the system catalogs, is the way to go.

赞(0）回复(0）举报 2022-12-12

hkmswyz63#

对于使用PGAdmin的用户：导航到数据库〉模式〉{您的模式}〉表〉{您的表名}右键单击〉属性。转到约束选项卡并随意添加/删除。
我使用PGAdmin 4和PostgreSQL 14完成了这一操作。

赞(0）回复(0）举报 2022-12-12