Adding foreign key constraint post table creation

From: Charles Hauser <chauser(at)duke(dot)edu>
To: PSQL_list <pgsql-sql(at)postgresql(dot)org>
Subject: Adding foreign key constraint post table creation
Date: 2002-12-09 16:11:21
Message-ID: 1039450281.32471.44.camel@pandorina.biology.duke.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jean-Luc Lachance 2002-12-09 16:11:32 Re: Rules/Trigges Trade-offs
Previous Message Ludwig Lim 2002-12-09 14:42:20 Re: ISNULL FUNCTION