Thread: Syntax of: alter table ... add constraint ...
Hello,
I'm having this table filled with data:
\d pref_users;
Table "public.pref_users"
Column | Type | Modifiers
------------+-----------------------------+---------------
id | character varying(32) | not null
first_name | character varying(32) |
last_name | character varying(32) |
female | boolean |
avatar | character varying(128) |
city | character varying(32) |
lat | real |
lng | real |
last_login | timestamp without time zone | default now()
last_ip | inet |
medals | smallint |
Indexes:
"pref_users_pkey" PRIMARY KEY, btree (id)
Check constraints:
"pref_users_lat_check" CHECK ((-90)::double precision <= lat AND
lat <= 90::double precision)
"pref_users_lng_check" CHECK ((-90)::double precision <= lng AND
lng <= 90::double precision)
"pref_users_medals_check" CHECK (medals > 0)
Referenced by:
TABLE "pref_luck" CONSTRAINT "pref_luck_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_misere" CONSTRAINT "pref_misere_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_money" CONSTRAINT "pref_money_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_pass" CONSTRAINT "pref_pass_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_rate" CONSTRAINT "pref_rate_obj_fkey" FOREIGN KEY
(obj) REFERENCES pref_users(id)
TABLE "pref_rate" CONSTRAINT "pref_rate_subj_fkey" FOREIGN KEY
(subj) REFERENCES pref_users(id)
And then I realized that I actually want
medals smallint default 0 check (medals >= 0)
So I've dropped the old constraint with
alter table pref_users drop constraint "pref_users_medals_check";
but how can I add the new contraint please? I'm trying:
alter table pref_users add constraint pref_users_medals_check (medals >= 0);
ERROR: syntax error at or near "("
LINE 1: ...pref_users add constraint pref_users_medals_check (medals >=...
^
and many combinations of quotes and "check" inbetween,
but can't find the correct syntax
Regards
Alex
Alexander Farber, 08.11.2010 15:50:
> And then I realized that I actually want
>
> medals smallint default 0 check (medals>= 0)
>
> So I've dropped the old constraint with
>
> alter table pref_users drop constraint "pref_users_medals_check";
>
> but how can I add the new contraint please? I'm trying:
>
> alter table pref_users add constraint pref_users_medals_check (medals>= 0);
> ERROR: syntax error at or near "("
> LINE 1: ...pref_users add constraint pref_users_medals_check (medals>=...
> ^
> and many combinations of quotes and "check" inbetween,
> but can't find the correct syntax
That should work:
alter table pref_users add constraint pref_users_medals_check check check (medals >= 0);
Thomas
On 08/11/2010 14:50, Alexander Farber wrote:
> alter table pref_users add constraint pref_users_medals_check (medals>= 0);
> ERROR: syntax error at or near "("
> LINE 1: ...pref_users add constraint pref_users_medals_check (medals>=...
> ^
> and many combinations of quotes and "check" inbetween,
> but can't find the correct syntax
Working from memory, I think you need the word "check" before the
opening parenthesis.
Do you use PgAdmin? - You can go through the motions of creating the
constraint there, and see what SQL it generates.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
Thank you, alter table pref_users add constraint pref_users_medals_check check (medals >= 0); has worked! I do not use pgAdmin, because I see in the logs of my 2 web server, that attackers look for it all the time. But I'll install it at my development VM at home now. Regards Alex
Le 08/11/2010 16:18, Alexander Farber a écrit : > Thank you, > > alter table pref_users add constraint pref_users_medals_check check > (medals >= 0); > > has worked! > > I do not use pgAdmin, because I see in the logs of my 2 web server, > that attackers look for it all the time. But I'll install it at my > development VM at home now. > I don't really see what an attacker can look for wrt pgAdmin. Be careful that there is pgAdmin and phpPgAdmin and those are two different things (even if they are both admin tools for PostgreSQL). -- Guillaume http://www.postgresql.fr http://dalibo.com
Oh right, I meant phpPgAdmin
On 8 Nov 2010, at 16:18, Alexander Farber wrote: > Thank you, > > 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 specifyingthe actual constraint expression. Hence the need to add 'check' (the constraint type) between 'pref_users_medals_check' (the name) and '(medals >= 0)' (theexpression). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4cd82a7b10261263518415!
Alban Hertroys <dalroi@solfertje.student.utwente.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
specifyingthe actual constraint expression.
> Hence the need to add 'check' (the constraint type) between 'pref_users_medals_check' (the name) and '(medals >= 0)'
(theexpression).
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