Saturday 8 February 2014

How to Rebuild Primary key constraints or Indexes in Postgresql 8.4 +


1)  Verified the two tables relations and check the data on those table. 
    If you required take table dump backup of these two tables.

test=# create table table_primary(id int primary key,name text);
NOTICE:  CREATE TABLE / PRIMARY KEY 

test=# create table table_foreign(id int references table_primary(id),location varchar);
CREATE TABLE

test=# \d table_primary
 Table "public.table_primary"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 name   | text    | 
Indexes:
    "table_primary_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "table_foreign" CONSTRAINT "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)

test=# \d table_foreign 
       Table "public.table_foreign"
  Column  |       Type        | Modifiers 
----------+-------------------+-----------
 id       | integer           | 
 location | character varying | 
Foreign-key constraints:
    "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)

test=# insert into table_primary values (1,'RC');
INSERT 0 1
test=# insert into table_primary values (2,'RK');
INSERT 0 1
test=# insert into table_primary values (3,'Balu');
INSERT 0 1
test=# insert into table_primary values (4,'mahi');
INSERT 0 1
test=# insert into table_foreign values (1,'HYD');
INSERT 0 1
test=# insert into table_foreign values (2,'PUNE');
INSERT 0 1
test=# insert into table_foreign values (3,'PUNE');
INSERT 0 1
test=# insert into table_foreign values (4,'PUNE');
INSERT 0 1
test=# select * from table_primary ;
 id | name 
----+------
  1 | RC
  2 | RK
  3 | Balu
  4 | mahi
(4 rows)

test=# select * from table_foreign ;
 id | location 
----+----------
  1 | HYD
  2 | PUNE
  3 | PUNE
  4 | PUNE

2) Add new unique index before deleting existing primary key constraint,once it is done queries will start using new index.

test=# create unique index CONCURRENTLY unique_table_primary_id  on table_primary(id);
CREATE INDEX

test=# \d public.table_primary
 Table "public.table_primary"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 name   | text    | 
Indexes:
    "table_primary_pkey" PRIMARY KEY, btree (id)
    "unique_table_primary_id" UNIQUE, btree (id)
Referenced by:
    TABLE "table_foreign" CONSTRAINT "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)


3)  Drop  existing primary key constraint,it will also dependent foreign key constraints also.
And then add the new primary key constraint and foreign key constraints those tables.

test=# begin ;
BEGIN
test=# alter table table_primary  drop constraint table_primary_pkey  CASCADE;  alter table table_foreign add  FOREIGN KEY (id) REFERENCES table_primary(id);
NOTICE:  drop cascades to constraint table_foreign_id_fkey on table table_foreign
ALTER TABLE
ALTER TABLE
test=# commit ;
COMMIT

5) verify and  make sure that you have the table definitions and contents of data in table,what you have before the operation.

test=# \d table_primary 
 Table "public.table_primary"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 name   | text    | 
Indexes:
    "unique_table_primary_id" UNIQUE, btree (id)
Referenced by:
    TABLE "table_foreign" CONSTRAINT "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)

test=# \d public.table_foreign
       Table "public.table_foreign"
  Column  |       Type        | Modifiers 
----------+-------------------+-----------
 id       | integer           | 
 location | character varying | 
Foreign-key constraints:
    "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)

test=# select * from table_primary ;
 id | name 
----+------
  1 | RC
  2 | RK
  3 | Balu
  4 | mahi
(4 rows)

test=# select * from table_foreign ;
 id | location 
----+----------
  1 | HYD
  2 | PUNE
  3 | PUNE
  4 | PUNE
(4 rows)

6) Now verify the tables primary and foreign key rules are working or not?

test=# insert into table_foreign values (10,'id');
ERROR:  insert or update on table "table_foreign" violates foreign key constraint "table_foreign_id_fkey"
DETAIL:  Key (id)=(10) is not present in table "table_primary".
test=# insert into table_primary values (1,'RC');
ERROR:  duplicate key value violates unique constraint "unique_table_primary_id"
DETAIL:  Key (id)=(1) already exists.
test=# insert into table_primary values (null,'RC');
ERROR:  null value in column "id" violates not-null constraint


7)  How to Add new column to existing composite Primary key : 


i) master table is having  composite PK with two columns.

postgres=# \d+ master1
                           Table "public.master1"
    Column    |  Type   | Modifiers | Storage  | Stats target | Description
--------------+---------+-----------+----------+--------------+-------------
 id           | integer | not null  | plain    |              |
 name         | text    | not null  | extended |              |
 created_date | date    |           | plain    |              |
 loc          | text    |           | extended |              |
Indexes:
    "master1_pkey" PRIMARY KEY, btree (id, name)
Child tables: child100,
              child200
Has OIDs: no

2)  drop existing Pk and  add new index as PK.

postgres=# Begin;
 create unique index CONCURRENTLY on master1 (id,name,created_date);
alter table table_primary  drop constraint table_primary_pkey  CASCADE;
alter table master1  add CONSTRAINT PK_my_table PRIMARY KEY USING INDEX master1_id_name_created_date_idx;
commit;
NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "master1_id_name_created_date_idx" to "pk_my_table"
ALTER TABLE

4) Verify the Table 

postgres=# \d+ master1 
                           Table "public.master1"
    Column    |  Type   | Modifiers | Storage  | Stats target | Description 
--------------+---------+-----------+----------+--------------+-------------
 id           | integer | not null  | plain    |              | 
 name         | text    | not null  | extended |              | 
 created_date | date    | not null  | plain    |              | 
 loc          | text    |           | extended |              | 
Indexes:
    "pk_my_table" PRIMARY KEY, btree (id, name, created_date)
Child tables: child100,
              child200
Has OIDs: no


1 comment:

  1. Hey, thanks for the article, really useful!

    I think there is one missed action in the script for step #3:
    ALTER TABLE primary ADD PRIMARY KEY USING INDEX unique_table_primary_id;


    Another thing that we can do here is renaming the index before using it, like this:

    ALTER INDEX unique_table_primary_id RENAME TO table_primary_pkey;

    ReplyDelete