Re: alter table

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
>