CODEWITHSUNDEEP
postgresqlpostgresql-9.3postgresql-9.4postgresql-9.2
sasori
sasori

Reputation: 5445

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

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?

Upvotes: 2

Views: 1495

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246238

Use ALTER TABLE:

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

Upvotes: 4

Related Questions