how can i change my table/column constraint
Hi,
if i have created this tables:
create table lab (
kd_lab varchar(4) check (kd_lab ~'[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?[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?
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
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
Show quoted text
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
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
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>