Re: Alter table to add foreign key

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