current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 0 down vote favorite

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);
share|improve this question
2  
There is no modify constraint (which is clearly documented in the manual). You need to drop and re-create it. – a_horse_with_no_name Dec 2 '15 at 17:03
1  
But I think it can be done in a single statement. – ypercubeᵀᴹ Dec 2 '15 at 17:13
    
There's an option ALTER CONSTRAINT in 9.4 but only works for foreign key constraints. postgresql.org/docs/current/static/sql-altertable.html – user4150760 Dec 2 '15 at 17:21
up vote 3 down vote accepted

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 (like) command" for constraints (referring to MySQL's MODIFY I assume).

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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