[SQL] Adding foreign key constraint post table creation

[Charles Hauser]

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_id int,
        foreign key (termtype_id) references cvterm (cvterm_id),
        timeentered timestamp not null default current_timestamp,
        timelastmod timestamp not null default current_timestamp,
        unique(termname, termtype_id)
);


regards,

Charles



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster