Thread: Question about Foreign key!

Question about Foreign key!

From

Fariba Noorbakhsh

Date:

27 July 2001, 13:26:10

Hi everybody,

Is there anyway to define foreign Key ,.. (constraints) after creating
table?
As far I know, you can't do it with ALTER table!!!

Thanks in advance,
Fariba

Re: Question about Foreign key!

From

Patrick Welche

Date:

27 July 2001, 14:52:48

On Fri, Jul 27, 2001 at 04:33:24PM +0200, Fariba Noorbakhsh wrote:
> Hi everybody,
>
> Is there anyway to define foreign Key ,.. (constraints) after creating
> table?
> As far I know, you can't do it with ALTER table!!!

template1=# \h alter table
Command:     ALTER TABLE
...
ALTER TABLE table
    ADD table constraint definition
...


eg:
                        Table "tblPerson"
         Attribute         |           Type           | Modifier
---------------------------+--------------------------+----------
 ID                        | integer                  |
...
                Table "tblAddress"
 Attribute  |           Type           | Modifier
------------+--------------------------+----------
 ID         | integer                  |
 PersonID   | integer                  |
...

create unique index tblperson_pkey on "tblPerson"("ID");
alter table "tblAddress"
  add constraint addfk
  foreign key("PersonID") references "tblPerson"("ID") match full;


Hope this helps,

Patrick

Re: delete columns from table!

From

Fariba Noorbakhsh

Date:

06 August 2001, 15:02:29


Fariba Noorbakhsh wrote:

> Hi Patrick,
>
> Do you know how I can drop(delete) a column from a table?! Just a column. I don't
> think you can do it with Alter table
>
> Thanks in advance,
> Fariba

Re: delete columns from table!

From

Patrick Welche

Date:

13 August 2001, 14:30:44

On Mon, Aug 06, 2001 at 05:42:09PM +0200, Fariba Noorbakhsh wrote:
> Hi Patrick,
>
> Do you know how I can drop(delete) a column from a table?! Just a column. I don't
> think you can do it with Alter table
>
> Thanks in advance,
> Fariba

According to the TODO list:

COMMANDS
...
* ALTER
...
    o Add ALTER TABLE DROP COLUMN feature [drop]

so the way forward may be to
pg_dump -t your_table -D your_database > table.txt
and have fun editing out all reference to said column from table.txt. Then
drop the table and reload from table.txt. YMMV..

Cheers,

Patrick

Re: delete columns from table!

From

"Tim Barnard"

Date:

13 August 2001, 15:17:22

Another way SELECT INTO a new table, omitting the column you want to delete.

Tim

----- Original Message -----
From: "Patrick Welche" <prlw1@newn.cam.ac.uk>
To: "Fariba Noorbakhsh" <fNoorbakhsh@tecways.com>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, August 13, 2001 7:36 AM
Subject: Re: [GENERAL] delete columns from table!


> On Mon, Aug 06, 2001 at 05:42:09PM +0200, Fariba Noorbakhsh wrote:
> > Hi Patrick,
> >
> > Do you know how I can drop(delete) a column from a table?! Just a
column. I don't
> > think you can do it with Alter table
> >
> > Thanks in advance,
> > Fariba
>
> According to the TODO list:
>
> COMMANDS
> ...
> * ALTER
> ...
>     o Add ALTER TABLE DROP COLUMN feature [drop]
>
>
> so the way forward may be to
> pg_dump -t your_table -D your_database > table.txt
> and have fun editing out all reference to said column from table.txt. Then
> drop the table and reload from table.txt. YMMV..
>
> Cheers,
>
> Patrick
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Re: delete columns from table!

From

Fariba Noorbakhsh

Date:

14 August 2001, 06:44:35

Thanks, it helps!

Tim Barnard wrote:

> Another way SELECT INTO a new table, omitting the column you want to delete.
>
> Tim
>
> ----- Original Message -----
> From: "Patrick Welche" <prlw1@newn.cam.ac.uk>
> To: "Fariba Noorbakhsh" <fNoorbakhsh@tecways.com>
> Cc: <pgsql-general@postgresql.org>
> Sent: Monday, August 13, 2001 7:36 AM
> Subject: Re: [GENERAL] delete columns from table!
>
> > On Mon, Aug 06, 2001 at 05:42:09PM +0200, Fariba Noorbakhsh wrote:
> > > Hi Patrick,
> > >
> > > Do you know how I can drop(delete) a column from a table?! Just a
> column. I don't
> > > think you can do it with Alter table
> > >
> > > Thanks in advance,
> > > Fariba
> >
> > According to the TODO list:
> >
> > COMMANDS
> > ...
> > * ALTER
> > ...
> >     o Add ALTER TABLE DROP COLUMN feature [drop]
> >
> >
> > so the way forward may be to
> > pg_dump -t your_table -D your_database > table.txt
> > and have fun editing out all reference to said column from table.txt. Then
> > drop the table and reload from table.txt. YMMV..
> >
> > Cheers,
> >
> > Patrick
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html