Change Primary Key

How can I alter a primary key constraint using SQL syntax?

Yes. The only way would be to drop the constraint with an Alter table then recreate it.

ALTER TABLE <Table_Name>
DROP CONSTRAINT <constraint_name>

ALTER TABLE <Table_Name>
ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)

SQL Server Change Primary Key Data Type

You can't change primary key column,unless you drop it..Any operations to change its data type will lead to below error..

The object 'XXXX' is dependent on column 'XXXX'.

Only option is to

1.Drop primary key

2.change data type

3.recreate primary key

ALTER TABLE t1  
DROP CONSTRAINT PK__t1__3213E83F88CF144D;   
GO  

alter table t1 
alter column id varchar(10) not null

alter table t1 add primary key (id)

From 2012,there is a clause called (DROP_EXISTING = ON) which makes things simple ,by dropping the clustered index at final stage and also keeping old index available for all operations..But in your case,this clause won't work..

So i recommend

1.create new table with desired schema and indexes,with different name

2.insert data from old table to new table

3.finally at the time of switch ,insert data that got accumulated

4.Rename the table to old table name

This way you might have less downtime

Change primary key in PostgreSQL table

I've spent some time and finally came up with a working solution.

I will publish it here for future reference.

Solution

First of all, you have three tables (foo_table, bar_table, baz_table) which are pointing to your users table by means of foreign keys (called user_id in all cases). You will need to replace the IDs stored in those columns from id to another_id. Here's how you can do it:

-- We are dropping the foreign key constraint on dependant table (in other case it will prevent us from updating the values)
ALTER TABLE foo_table DROP CONSTRAINT fk_e52ffdeea76ed395;

-- Then, we're swapping values in foreign key column from id to another_id
UPDATE foo_table T SET user_id = (SELECT another_id FROM users WHERE id = T.user_id);

-- And finally we're creating new foreign key constraint pointing to the another_id instead of id
ALTER TABLE foo_table ADD CONSTRAINT fk_e52ffdeea76ed395 FOREIGN KEY (user_id) REFERENCES users (another_id) ON DELETE CASCADE;

You will need to repeat the above queries for each dependent table.

After that, all dependent tables will point to your new another_id column.

In the end we will just need to replace the primary key:

-- 1. Dropping the original primary key
ALTER TABLE users DROP CONSTRAINT users_pkey

-- 2. Renaming existing index for another_id (optional)
ALTER INDEX uniq_1483a5e93414710b RENAME TO users_pkey

-- 3. Creating new primary key using existing index for another_id
ALTER TABLE users ADD PRIMARY KEY USING INDEX users_pkey

-- 4. Creating index for old id column (optional)
CREATE UNIQUE INDEX users_id ON users (id)

-- 5. You can drop the original sequence generator if you won't need it
DROP SEQUENCE users_id_seq

You can even drop the original id column if you want to.

I hope it will help someone.

change primary key values

Do a Truncate table and try again.

You need to reset the Identity column.

change primary key value

You need to set ON UPDATE CASCADE for those foreign keys:

ALTER TABLE bar
ADD CONSTRAINT FK_foo_bar
FOREIGN KEY (fooid) REFERENCES foo(id)
ON UPDATE CASCADE

Then you simply update the FKs and referring fields will also be updated as part of the transaction:

UPDATE foo SET id = id + 1000

Note that to alter constraints they need to be dropped.

How to alter primary key in table for snowflake?

You can add primary key constraint to a column but cannot alter an existing one.

https://docs.snowflake.com/en/sql-reference/sql/create-table-constraint.html#out-of-line-unique-primary-foreign-key

---

Related Topics