13

Say I have a constraint

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

that needs to be modified so that zipcode length is 6.

Do I have the drop the constraint zipchk first and then recreate it ?

ALTER TABLE distributors DROP CONSTRAINT zipchk;
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 6);

or is there a MODIFY (like) command :

ALTER TABLE distributors MODIFY CONSTRAINT zipchk CHECK (char_length(zipcode) = 6);
3

1 Answer 1

35

To sum up the comments:

Like @ypercube hinted, you can do it in a single command, which is cheaper and safer:

ALTER TABLE distributors
  DROP CONSTRAINT zipchk
, ADD  CONSTRAINT zipchk CHECK (length(zipcode) = 6);

ALTER CONSTRAINT in Postgres 9.4 or later (like you found) can only change the "deferability" of a FK constraints. So not what you are looking for. Other than that, there is no "MODIFY" command for constraints (referring to MySQL's MODIFY I assume).

Details in the manual for ALTER TABLE, like @a_horse mentioned.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.