Re: Adding a column with constraint

On Thu, Feb 24, 2011 at 8:02 PM, David Johnston <polobo@yahoo.com> wrote:
> A column constraint can only reference its own column.  Since you are
> referencing "completed" in the CHECK it implicitly converts the Column
> constraint into a Table constraint - and table constraints do not reference
> the name of a column like a column constraint does during name
> auto-generation.

Oh ok, that's a good explanation, thank you


>
> # alter table pref_match add column win integer default 0 check (completed
>>= win and win >= 0);
>
> Now I have:
>
> # \d pref_match
>                          Table "public.pref_match"
>  Column   |         Type          |                Modifiers
> -----------+-----------------------+------------------------------------
> -----------+-----------------------+-----
>  id        | character varying(32) |
>  started   | integer               | default 0
>  completed | integer               | default 0
>  quit      | integer               | default 0
>  yw        | character(7)          | default to_char(now(), 'IYYY-IW'::text)
>  win       | integer               | default 0
> Check constraints:
>    "pref_match_check" CHECK (completed >= win AND win >= 0)
>    "pref_match_completed_check" CHECK (completed >= 0)
>    "pref_match_quit_check" CHECK (quit >= 0)
>    "pref_match_started_check" CHECK (started >= 0) Foreign-key constraints:
>    "pref_match_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
>