Thread: Modifying check constraints
Hi all:
Let's suppose I create a table with a CHECK constraint, like:
CREATE TABLE blabla (id int, tipo varchar(8),
CHECK tipo IN ('tipo1', 'tipo2', 'tipo3'))
Can I change the CHECK constraint afterwards? For example, can I
add another value that "tipo" can have, or I'd have to recreate the
table?
Paulo Jan.
DDnet.
On Wed, 14 Nov 2001, Paulo Jan wrote:
> Hi all:
>
> Let's suppose I create a table with a CHECK constraint, like:
>
> CREATE TABLE blabla (id int, tipo varchar(8),
> CHECK tipo IN ('tipo1', 'tipo2', 'tipo3'))
>
> Can I change the CHECK constraint afterwards? For example, can I
> add another value that "tipo" can have, or I'd have to recreate the
> table?
Add another constraint with
ALTER TABLE lala ADD CONSTRAINT lalaCheck2 CHECK tipo in ('t1','t2','t3','t4');
The new check will be in effect along with the old one. Make sure the check constraints don't allow disjoint sets of
valuescause then you won't be able to add anything to the table.
cheers,
thalis
You have to reload the table if you want to change check constraints. See: http://www.postgresql.org/idocs/index.php?sql-altertable.html However, an easy way to get around this is to not use CHECK constraints in these situations, and instead use a FOREIGN KEY constraint like this: -- BEGIN EXAMPLE CREATE TABLE tipos ( tipo varchar(8) PRIMARY KEY ); INSERT INTO tipos (tipo) VALUES ('tipo1'); INSERT INTO tipos (tipo) VALUES ('tipo2'); INSERT INTO tipos (tipo) VALUES ('tipo3'); CREATE TABLE blabla ( id int, tipo varchar(8) REFERENCES tipos(tipo) ); -- END EXAMPLE Yes, this does require a little more typing. However, you only build your schema once. Using this method your application will be much more flexible. Adding new tipos is as simple as: INSERT INTO tipos (tipo) VALUES ('tipo_neuvo'); I hope this is helpful, Jason Earl Paulo Jan <admin@mail.ddnet.es> writes: > Hi all: > > Let's suppose I create a table with a CHECK constraint, like: > > CREATE TABLE blabla (id int, tipo varchar(8), > CHECK tipo IN ('tipo1', 'tipo2', 'tipo3')) > > Can I change the CHECK constraint afterwards? For example, can I > add another value that "tipo" can have, or I'd have to recreate the > table? > > > > Paulo Jan. > DDnet. > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Wed, 14 Nov 2001, Paulo Jan wrote:
> Hi all:
>
> Let's suppose I create a table with a CHECK constraint, like:
>
> CREATE TABLE blabla (id int, tipo varchar(8),
> CHECK tipo IN ('tipo1', 'tipo2', 'tipo3'))
>
> Can I change the CHECK constraint afterwards? For example, can I
> add another value that "tipo" can have, or I'd have to recreate the
> table?
Right now, I think you'd need to recreate, but 7.2 should let you drop
and re-add the check constraint.
>>>>> "SS" == Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: >> Can I change the CHECK constraint afterwards? For example, can I >> add another value that "tipo" can have, or I'd have to recreate the >> table? SS> Right now, I think you'd need to recreate, but 7.2 should let you drop SS> and re-add the check constraint. I posted a note the other day to postgres general titled "constraint surgery" showing how I altered the check constraints on a table asking if it would cause problems. it is in the list archives for Nov 7 (I think). I never got any replies, so I'm getting ready to try it on my production server and hope it doesn't croak anything. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
On 15 Nov 2001, Vivek Khera wrote: > >>>>> "SS" == Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > >> Can I change the CHECK constraint afterwards? For example, can I > >> add another value that "tipo" can have, or I'd have to recreate the > >> table? > > SS> Right now, I think you'd need to recreate, but 7.2 should let you drop > SS> and re-add the check constraint. > > I posted a note the other day to postgres general titled "constraint > surgery" showing how I altered the check constraints on a table asking > if it would cause problems. it is in the list archives for Nov 7 (I > think). > > I never got any replies, so I'm getting ready to try it on my > production server and hope it doesn't croak anything. I'd guess so, except that it doesn't look like it necessarily takes effect in sessions that are currently running that have already done the check (I need to restart psql). If you can have downtime, you might just want to look at making the new table and using insert into to copy the data and rename the tables around. This is kinda unhappy with foreign keys and more complicated constructs, but in general is less likely to cause you grief.
>>>>> "SS" == Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: SS> If you can have downtime, you might just want to look at making the SS> new table and using insert into to copy the data and rename the tables SS> around. This is kinda unhappy with foreign keys and more complicated SS> constructs, but in general is less likely to cause you grief. Unfortunately, I have plenty of foreign keys and *TONS* of data in that table, and downtime is undesirable, but possible. I'm gonna try the constraint surgery, as it seems to work. Thanks. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 2001 November 15 10:05 am, Vivek Khera wrote: > I posted a note the other day to postgres general titled "constraint > surgery" showing how I altered the check constraints on a table asking > if it would cause problems. it is in the list archives for Nov 7 (I > think). http://archives2.us.postgresql.org/pgsql-general/2001-11/msg00298.php Interesting. - -- Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/ 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iEYEARECAAYFAjv0E+gACgkQCT73CrRXhLGUIwCfYMW/2B7CvShe9HqjXAkgass3 QkIAnil8dVTfWKig9hlZb+MpDWpYWjxB =et9F -----END PGP SIGNATURE-----