Thread: Adding foreign key constraint post table creation

All,

A couple of novice questions:


I would like to modify an existing TABLE by addinga new column (FOREIGN
KEY): 
      type_id int not null,      foreign key (type_id) references cvterm (cvterm_id),


Will this work ( running PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled
by GCC 2.96):

ALTER TABLE contig ADD COLUMN type_id int;

ALTER TABLE contig ADD CONSTRAINT cvtermfk FOREIGN KEY (type_id)
references cvterm (cvterm_id);
********

I would like to load data into the table below from a file lacking the
timestamp fields, where the file structure is:


COPY table FROM STDIN;
1    feature_type    types of features    \N
2    3'-exon    \N    1 
.
.
.
\.

This fails as the timestamp fields are 'not null'.  Othere than
generating INSERT stmts for the data how else could I enter the data?

create table cvterm (cvterm_id serial not null,primary key (cvterm_id),termname varchar(255) not null,termdefinition
text,termtype_idint,foreign key (termtype_id) references cvterm (cvterm_id),timeentered timestamp not null default
current_timestamp,timelastmodtimestamp not null default current_timestamp,unique(termname, termtype_id)
 
);


regards,

Charles

On Monday 09 Dec 2002 4:11 pm, Charles Hauser wrote:
> All,
>
> A couple of novice questions:
>
>
> I would like to modify an existing TABLE by addinga new column (FOREIGN
> KEY):
>
>        type_id int not null,
>        foreign key (type_id) references cvterm (cvterm_id),
>
>
> Will this work ( running PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled
> by GCC 2.96):

You'll need to check your manuals for 7.1.3 - look in the SQL command
reference under ALTER TABLE. It should work with current versions but I don't
have v7.1.3 to hand.

> I would like to load data into the table below from a file lacking the
> timestamp fields, where the file structure is:
>
> COPY table FROM STDIN;
> 1    feature_type    types of features    \N
> 2    3'-exon    \N    1
>
> This fails as the timestamp fields are 'not null'.  Othere than
> generating INSERT stmts for the data how else could I enter the data?

You could process the file with perl/awk etc. and add the required timestamps.
I don't think COPY substitutes default values for you.

--  Richard Huxton