Re: Modifying check constraints - Mailing list pgsql-general

From Jason Earl
Subject Re: Modifying check constraints
Date
Msg-id 87u1vxi33a.fsf@npa01zz001.simplot.com
Whole thread Raw
In response to Modifying check constraints  (Paulo Jan <admin@digital.ddnet.es>)
List pgsql-general
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)

pgsql-general by date:

Previous
From: "Thalis A. Kalfigopoulos"
Date:
Subject: Re: Modifying check constraints
Next
From: Stephan Szabo
Date:
Subject: Re: Modifying check constraints