Re: alter table

From: Pavel SRB <srb(at)heckler-koch(dot)cz>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: alter table
Date: 2008-04-08 04:32:37
Message-ID: 47FAF565.9020308@heckler-koch.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Sean Davis wrote:
> On Mon, Apr 7, 2008 at 5:18 PM, Pavel SRB <srb(at)heckler-koch(dot)cz> wrote:
>
>> Sean Davis wrote:
>>
>>
>>> On Mon, Apr 7, 2008 at 4:53 PM, Pavel SRB <srb(at)heckler-koch(dot)cz> wrote:
>>>
>>>
>>>
>>>> hi all
>>>>
>>>> please i have one question. I made table called tab_article with
>>>>
>>>> CREATE TABLE "tab_article" (
>>>> "id" serial NOT NULL PRIMARY KEY,
>>>> "flag" boolean NOT NULL,
>>>> "title" text NOT NULL,
>>>> "journal" integer NOT NULL,
>>>> "doi" varchar(255) NOT NULL,
>>>> "year" integer NOT NULL,
>>>> "volume" integer NOT NULL,
>>>> "issue" integer NOT NULL,
>>>> "pages_start" integer NOT NULL,
>>>> "pages_end" integer NOT NULL,
>>>> "printed" boolean NOT NULL,
>>>> "public" boolean NOT NULL,
>>>> "entry" boolean NOT NULL,
>>>> "filename" integer NOT NULL,
>>>> "language" integer NOT NULL,
>>>> "link" integer NOT NULL,
>>>> "info" varchar(255) NOT NULL
>>>> );
>>>>
>>>> and later another table called tab_journal
>>>>
>>>> CREATE TABLE "tab_journal" (
>>>> "id" serial NOT NULL PRIMARY KEY,
>>>> "flag" boolean NOT NULL,
>>>> "name" text NOT NULL,
>>>> "publisher" integer NOT NULL,
>>>> "issn_print" integer NOT NULL,
>>>> "issn_online" integer NOT NULL,
>>>> "link" integer NOT NULL,
>>>> "date_range" varchar(255) NOT NULL
>>>> );
>>>>
>>>> and i needed to change column journal in tab_article to be set like
>>>> "journal_id" integer NOT NULL REFERENCES "article_journal" ("id")
>>>>
>>>> and i am not able to find the right alter table command.
>>>> thank you very much
>>>>
>>>>
>>>>
>>> You will want to add the NOT NULL as one statement. Then, add the
>>> foreign key constraint as a second statement. There are examples of
>>> how to do each here:
>>>
>>> http://www.postgresql.org/docs/8.3/static/sql-altertable.html
>>>
>>> Hope that helps.
>>>
>>> Sean
>>>
>>>
>>>
>> thank you for fast replay. I have read this one tutorial before i posted
>> this here, but with no luck.
>>
>> ALTER TABLE l_article ADD CONSTRAINT journal_id FOREIGN KEY (journal_id)
>> REFERENCES l_journal (id);
>>
>> this one stops my psql terminal and did not change anything. Am i missing
>> the not null statement?
>>
>
> When you say it stops your psql terminal, you mean that the prompt
> does not return immediately? I assume that your tables already have
> data in them. If that is the case, then postgresql is probably
> checking to make sure that your foreign key constraint is actually
> satisfied. If you have a good deal of data, then this may take some
> time.
>
>
>> ALTER TABLE "l_article" ADD CONSTRAINT "journal_id" integer NOT NULL
>> REFERENCES FOREIGN KEY ("journal_id") REFERENCES "l_journal" (id);
>>
>> thank you
>> pavel
>>
>>
>>
>> --
>> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-novice
>>
>>
>
>
i have only six rows in the l_article table. So i believe that fault is
on my code, when after 5 minutes it still does nothing(yes it does not
return the prompt).

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message A B 2008-04-08 09:54:09 boolean as 0 and 1
Previous Message Justin Bailey 2008-04-08 00:27:15 problems with dollar quoting