Re: how can i change my table/column constraint

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>