Thread: Alter table to add foreign key
The online manual's syntax looks very simple compared to what I can achieve with manually adding by phpPgAdmin.
--
Jason Tan Boon Teck
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.
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)