Thread

1. how can i change my table/column constraint

   Muhammad Rusydi <rusydi@cbn.net.id> — 2001-05-05T06:49:31Z

   Hi,
   if i have created this tables:
   create table lab (
   kd_lab varchar(4) check (kd_lab ~'[0][1-4][0-9][0-9]'),
   name varchar(10),
   primary key (kd_lab));
   
   create table tb2 (
   kd_lab varchar(4) references lab (kd_lab),
   tb2 varchar(10),
   primary key (kd_lab));
   
   the questions is if i want to change the check constraint to
   [0][1-5][0-9][0-9]
   how can i do this?
   if i create another table named lab2 with same fields and then i move
   all tuples from lab to lab2, then drop table lab and rename lab2 to
   lab, is the references constraint in table tb2 still valid?
   would you show me how to do this?
   
   TIA
   Didi
   
   
   

2. Re: how can i change my table/column constraint

   Anand Raman <araman@india-today.com> — 2001-05-05T07:18:59Z

   basically copy the table to some other table..
   Drop the old table..
   recreate it with the constraints changed
   Repopulate the table..
   
   Hope this helps
   Anand
   
   On Sat, May 05, 2001 at 01:49:31PM +0700, Muhammad Rusydi wrote:
   >Hi,
   >if i have created this tables:
   >create table lab (
   >kd_lab varchar(4) check (kd_lab ~'[0][1-4][0-9][0-9]'),
   >name varchar(10),
   >primary key (kd_lab));
   >
   >create table tb2 (
   >kd_lab varchar(4) references lab (kd_lab),
   >tb2 varchar(10),
   >primary key (kd_lab));
   >
   >the questions is if i want to change the check constraint to
   >[0][1-5][0-9][0-9]
   >how can i do this?
   >if i create another table named lab2 with same fields and then i move
   >all tuples from lab to lab2, then drop table lab and rename lab2 to
   >lab, is the references constraint in table tb2 still valid?
   >would you show me how to do this?
   >
   >TIA
   >Didi
   >
   >
   >---------------------------(end of broadcast)---------------------------
   >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
   
   

3. Re: how can i change my table/column constraint

   Muhammad Rusydi <rusydi@cbn.net.id> — 2001-05-05T08:18:37Z

   Hi Anand,
   
   > basically copy the table to some other table..
   > Drop the old table..
   > recreate it with the constraints changed
   > Repopulate the table..
   would you show me with my two tables?
   is it like this?
   > >if i create another table named lab2 with same fields and then i move
   > >all tuples from lab to lab2, then drop table lab and rename lab2 to
   > >lab, is the references constraint in table tb2 still valid?
   > >would you show me how to do this?
   i create lab2 with the same constraint before i delete lab?
   isn't it?
   after lab2 created i copy all tuples from lab to lab2...
   is it right?
   what do you repopulate?
   sorry...i'm still not getting on to it
   TIA
   Didi
   
   
   

4. Re: how can i change my table/column constraint

   Eric G. Miller <egm2@jps.net> — 2001-05-05T11:17:21Z

   On Sat, May 05, 2001 at 01:49:31PM +0700, Muhammad Rusydi wrote:
   > Hi,
   > if i have created this tables:
   > create table lab (
   > kd_lab varchar(4) check (kd_lab ~'[0][1-4][0-9][0-9]'),
   > name varchar(10),
   > primary key (kd_lab));
   > 
   > create table tb2 (
   > kd_lab varchar(4) references lab (kd_lab),
   > tb2 varchar(10),
   > primary key (kd_lab));
   > 
   > the questions is if i want to change the check constraint to
   > [0][1-5][0-9][0-9]
   > how can i do this?
   > if i create another table named lab2 with same fields and then i move
   > all tuples from lab to lab2, then drop table lab and rename lab2 to
   > lab, is the references constraint in table tb2 still valid?
   > would you show me how to do this?
   
   On Sat, May 05, 2001 at 03:18:37PM +0700, Muhammad Rusydi wrote:
   > Hi Anand,
   > 
   > > basically copy the table to some other table..
   > > Drop the old table..
   > > recreate it with the constraints changed
   > > Repopulate the table..
   > would you show me with my two tables?
   > is it like this?
   > > >if i create another table named lab2 with same fields and then i move
   > > >all tuples from lab to lab2, then drop table lab and rename lab2 to
   > > >lab, is the references constraint in table tb2 still valid?
   > > >would you show me how to do this?
   > i create lab2 with the same constraint before i delete lab?
   > isn't it?
   > after lab2 created i copy all tuples from lab to lab2...
   > is it right?
   > what do you repopulate?
   > sorry...i'm still not getting on to it
   > TIA
   > Didi
   
   -- Create new table
   
   CREATE TABLE lab_new (
   kd_lab VARCHAR(4) CHECK (kd_lab ~'[0][1-5][0-9][0-9]'),
   name VARCHAR(10),
   PRIMARY KEY (kd_lab));
   
   -- Populate with existing data
   
   INSERT INTO lab_new (kd_lab, name, key)
   SELECT kd_lab, name, key FROM lab;
   
   -- Stop if there was an error!
   
   -- Create second new table without FK constraint
   
   CREATE TABLE tb2_new (
   kd_lab VARCHAR(4),
   tb2 VARCHAR(10),
   PRIMARY KEY (kd_lab));
   
   -- Populate with existing data
   
   INSERT INTO tb2_new (kd_lab, tb2, key)
   SELECT kd_lab, tb2, key FROM tb2;
   
   -- Stop if there was an error!
   
   -- Don't drop unless you're sure the data got copied okay
   
   DROP TABLE tb2;
   DROP TABLE lab;
   
   -- Rename tables
   
   ALTER TABLE lab_new RENAME TO lab;
   ALTER TABLE tb2_new RENAME TO tb2;
   
   -- Re add foreign key constraint (optionally named)
   
   ALTER TABLE tb2 ADD [CONSTRAINT foo] 
     FOREIGN KEY (kd_lab) REFERENCES lab (kd_lab);
   
   -- 
   Eric G. Miller <egm2@jps.net>