CODEWITHSUNDEEP
sqlpostgresql
Trip Therapy
Trip Therapy

Reputation: 317

Postgresql SET DEFAULT value from another table SQL

I'm making a sql script so I have create tables, now I have a new table that have columns. One column has a FOREIGN KEY so I need this value to be SET DEFAULT at the value of the value of the original table. For example consider this two table

PERSON(Name,Surename,ID,Age);

EMPLOYER(Name,Surname,Sector,Age);

In Employer I need AGE to be setted on default on the AGE of Person, this only if PERSON have rows or just 1 row.

ID is Primary key for person and Surname,Sector for employer and AGE is FOREIGN KEY in Employer refferenced from Person

Example sql :

CREATE TABLE PERSON(
    name VARCHAR(30) ,
    surename VARCHAR(20),
    ID VARCHAR(50) PRIMARY KEY,
    Age INT NOT NULL,
);
CREATE TABLE EMPLOYER(
    name VARCHAR(30) ,
    Surename VARCHAR(20),
    Sector VARCHAR(20),
    Age INT NOT NULL,
    PRIMARY KEY (Surename,Sector),
    FOREIGN KEY (Age) REFERENCES Person(Age) //HERE SET DEFAULT Person(Age), how'??
);

Upvotes: 2

Views: 6300

Answers (2)

Gurmokh
Gurmokh

Reputation: 2081

Taking away the poor design choices of this exercise it is possible to assign the value of a column to that of another one using a trigger.

Rough working example below: 

create table a (
 cola int, 
 colb int) ;

create table b (
 colc int, 
 cold int);

Create or replace function fn()
returns trigger 
as $$ begin
if new.cold is null then
  new.cold = (select colb from a where cola = new.colc);
end if;
return new;
end; 
$$ language plpgsql; 

 CREATE TRIGGER
   fn
 BEFORE INSERT ON
   b
 FOR EACH ROW EXECUTE PROCEDURE
   fn();

Upvotes: 6

Chris Travers
Chris Travers

Reputation: 26464

Use a trigger rather than a default. I have done things like this (useful occasionally for aggregated full text vectors among other things).

You cannot use a default here because you have no access to the current row data. Therefore there is nothing to look up if it is depending on your values currently being saved.

Instead you want to create a BEFORE trigger which sets the value if it is not set, and looks up data. Note that this has a different limitation because DEFAULT looks at the query (was a value specified) while a trigger looks at the value (i.e. what does your current row look like). Consequently a default can be avoided by explicitly passing in a NULL. But a trigger will populate that anyway.

Upvotes: 1

Related Questions