CODEWITHSUNDEEP
sqlpostgresqlforeign-keysprimary-key
manugupt1
manugupt1

Reputation: 2437

Changing type of primary key when you have foreign key constraint

I have a table with elements :

SGA
----
Pk | Integer (PRIMARY KEY)

and it has 3 - 4 relations
SB1
----
FK1 | Integer (references PK)


SB1
----
FK1 | Integer (references PK)


SB2
----
FK2 | Integer (references PK)


SB3
----
FK3 | Integer (references PK)

I want to change the type of PK to text but it gives a constrain error (which is obvious). Is there a SQL command, so that I can reflect the changes on the other tables as well.

The database has no values in it as of now and is the database is constructed.

Upvotes: 3

Views: 2751

Answers (1)

Jasen
Jasen

Reputation: 12402

You have to update each table explicitly like this, there is no shortcut SQL:

ALTER TABLE child DROP CONSTRAINT constraint_name ;
ALTER TABLE child ALTER COLUMN fk_col TYPE new_type;
ALTER TABLE parent ALTER COLUMN pk_col TYPE new_type;
ALTER TABLE child ADD CONSTRAINT constraint_name 
    FOREIGN KEY fk_col REFERENCES parent(pk_col);

eg:

create temp table foo( i integer primary key);
create temp table bar ( foo_i integer references foo(i) );
insert into foo values (1),(2),(3);
insert into bar values (1),(2),(2);

ALTER TABLE bar DROP CONSTRAINT bar_foo_i_fkey;
ALTER TABLE bar ALTER COLUMN foo_i TYPE text USING 'NUM:'||foo_i;
ALTER TABLE foo ALTER COLUMN i     TYPE text USING 'NUM:'||i;
ALTER TABLE bar ADD CONSTRAINT bar_foo_i_fkey
    FOREIGN KEY (foo_i) REFERENCES foo(i);

USING is optional, only needed if you want to do some sort of translation when changing types, or if postgres doesn't know how to translate them.

Upvotes: 3

Related Questions