how to modify column data type and add references to it in postgresql?

Question

how to modify an existing column and add a references to it ?

let's say I have this table create script and executed it to the server

create table person (
id bigserial primary key,
firstname varchar(255),
flastname varchar(255),
employeeid int
);

now I have a person table, but then later on I realized that I need to reference the employeeid from another table and I don't want to drop this existing person table as it has data now. how to add the REFERENCES employee(employeeid) to the employeeid column of the person table ?.

if only I didn't forget to add that references keyword,my create table should have been like this below

create table person (
id bigserial primary key,
firstname varchar(255),
flastname varchar(255),
employeeid int references employee(employeeid)
);

so how to modify the existing employeeid to have the references keyword to it since it already has data?

Laurenz Albe · Accepted Answer · 2020-09-08 05:44:09Z

4

Use ALTER TABLE:

ALTER TABLE person
   ADD FOREIGN KEY (employeeid) REFERENCES employee(employeeid);

answered Sep 8, 2020 at 5:44

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to modify column data type and add references to it in postgresql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related