How do I implement a sql command which outputs NULL for salary if it is a voluntary worker? Here are the tables I created first:
create Table worker (pid integer references Person,salary float);create Table person(pid integer primary key,name varchar(30),adress varchar(30));Since I’m not sure how to distinguish a normal worker from a voluntary one, I decided to make another table. Unfortunately, I don’t know how to insert NULL values for salary for all voluntary workers. That is what I tried out:
create table voluntaryworker(pid integer references Person,salary = null);insert into Person (pid, name, adress) values (1345, anna, 'festreet');insert into voluntaryworker (pid, salary) values (1345, null);pid = person ID
Advertisement
Answer
Like others commented, you certainly don’t need another table to implement this. All you need is some way to remember whether a worker is voluntary.
To make sure salary sticks to your rule, you can add a CHECK constraint:
CREATE TABLE worker ( pid integer PRIMARY KEY REFERENCES person, voluntary boolean NOT NULL DEFAULT false, salary numeric, CONSTRAINT voluntary_has_no_salary CHECK (NOT voluntary OR salary IS NULL));Meaning: voluntary workers cannot have a nonnull salary.
Alternatively, you might drop the table worker, too, and just add the columns worker_salary and worker_voluntary to table person. (You may need an additional flag worker, or integrate this information in the other two columns …)
If you are still interested in generated columns (not needed here), see this example with correct syntax and instructions:
Related: