Skip site navigation (1) Skip section navigation (2)

Re: Alter table to add foreign key

From: raghu ram <raghuchennuru(at)gmail(dot)com>
To: Jason Tan Boon Teck <tanboonteck(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Alter table to add foreign key
Date: 2011-08-12 19:35:28
Message-ID: CALnrrJTfs=asHXkiGEX0GecG5HEX5rz5TEhpykerftniurVqfw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Fri, Aug 12, 2011 at 9:41 AM, Jason Tan Boon Teck
<tanboonteck(at)gmail(dot)com>wrote:

> What is the full syntax of SQL to add foreign key to an existing table?
>
> The online manual's syntax looks very simple compared to what I can achieve
> with manually adding by phpPgAdmin.
>
>
Below example will explains to add foreign key to an existing table:

1. Create parent & child tables

postgres=# CREATE TABLE PARENT(T INT);

CREATE TABLE

postgres=# CREATE TABLE CHILD(T INT);

CREATE TABLE

2. Add primary key constraint to parent table

postgres=# ALTER TABLE PARENT ADD PRIMARY KEY(T);

NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"parent_pkey" for table "parent"

ALTER TABLE

3. Add foreign key constraint

postgres=# ALTER TABLE CHILD ADD  FOREIGN KEY(T) REFERENCES PARENT(T);

ALTER TABLE


Child Table:

postgres=# \d child

    Table "public.child"

Column |  Type   | Modifiers

--------+---------+-----------

t      | integer |

Foreign-key constraints:

   "child_t_fkey" FOREIGN KEY (t) REFERENCES parent(t)


Parent Table:

postgres=# \d parent

   Table "public.parent"

Column |  Type   | Modifiers

--------+---------+-----------

t      | integer | not null

Indexes:

   "parent_pkey" PRIMARY KEY, btree (t)

Referenced by:

   TABLE "child" CONSTRAINT "child_t_fkey" FOREIGN KEY (t) REFERENCES
parent(t)

--Raghu Ram

In response to

pgsql-novice by date

Next:From: jmscottDate: 2011-08-13 03:58:26
Subject: trivial question about unlogged tables
Previous:From: Douglas HydeDate: 2011-08-12 12:59:42
Subject: Re: new install, can't use - need password for postgres user

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group