Re: Syntax of: alter table ... add constraint ...

Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> writes:
> On 8 Nov 2010, at 16:18, Alexander Farber wrote:
>> alter table pref_users add constraint pref_users_medals_check check
>> (medals >= 0);
>>
>> has worked!

> To clarify a bit on this; if you add a constraint, you specify its name and what type of constraint it is, before specifying the actual constraint expression.
> Hence the need to add 'check' (the constraint type) between 'pref_users_medals_check' (the name) and '(medals >= 0)' (the expression).

One other thing that's maybe worth remembering about the syntax of
constraint clauses: the word CONSTRAINT is really used to introduce a
constraint name. If you want to create a constraint with no preselected
name, you leave off both the name and the word CONSTRAINT --- but you
still need the word(s) specifying the constraint type, such as CHECK or
FOREIGN KEY.

So either of these syntaxes are legal:

alter table pref_users add constraint pref_users_medals_check check (medals >= 0);
alter table pref_users add check (medals >= 0);

In the latter case the constraint will be created with some
system-selected name. (In fact, it looks like pref_users_medals_check
is exactly the name you'd get by default, if there were no such
constraint name already in use.)

regards, tom lane