Thread: Modifying check constraints

Modifying check constraints

From
Paulo Jan
Date:
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.

Re: Modifying check constraints

From
"Thalis A. Kalfigopoulos"
Date:
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


Re: Modifying check constraints

From
Jason Earl
Date:
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)

Re: Modifying check constraints

From
Stephan Szabo
Date:
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.


Re: Modifying check constraints

From
Vivek Khera
Date:
>>>>> "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/

Re: Modifying check constraints

From
Stephan Szabo
Date:
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.



Re: Modifying check constraints

From
Vivek Khera
Date:
>>>>> "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/

Re: Modifying check constraints

From
"Andrew G. Hammond"
Date:
-----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-----