Suppose you have to change the primary key of a PostgreSQL database. You can achieve this by executing the following statements:

ALTER TABLE "order"
DROP CONSTRAINT order_pkey CASCADE,
ADD PRIMARY KEY(column_i_want_to_use_as_a_pkey_now); 

If the primary key is used as a foreign key in other tables you can add the CASCADE keyword. This will automatically delete any foreign keys which depend on the primary key:

ALTER TABLE "order"
DROP CONSTRAINT order_pkey CASCADE,
ADD PRIMARY KEY(column_i_want_to_use_as_a_pkey_now); 

Of course you still have to re-create the deleted foreign keys after this.

PostgreSQL also creates and maintains an index for each primary key by default. As of PostgreSQL 9.6.x dropping the and recreating the primary key will also recreate the corresponding index.

To see which foreign keys point to a specific table in question you can utilize the following statement by the way:

SELECT
    tc.table_schema, 
    tc.constraint_name, 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_schema AS foreign_table_schema,
    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
        AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
        ON ccu.constraint_name = tc.constraint_name
        AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND ccu.table_name ='order';