Set rule in Postgres for entry in Column

Question

I have a table called Staff I am currently creating. I have columns:

staffNo
fName
lName
position
sex
DOB
salary
branchNo

Currently, the entry I have in the postgres command line is:

CREATE TABLE Staff(
         staffNo     TEXT CONSTRAINT firstkey PRIMARY KEY,
         fname       TEXT NOT NULL,
         lname       TEXT NOT NULL,
         position    TEXT NOT NULL,
         sex         CHAR(1) NOT NULL,
         DOB         DATE NOT NULL,
         salary      INT NOT NULL,
         FOREIGN KEY (branchNo) REFERENCES Branch(branchNo));

I would like to set it so that for position, only either Manager, Assistant, or Supervisor can be inserted into that column. For sex, I would like the same to be for M or F. Can anyone provide an example of how this can be done?

Houari · Accepted Answer · 2014-11-09 23:12:52Z

4

You can achieve this using the check constraint:

CREATE TABLE Staff(
     staffNo     TEXT CONSTRAINT firstkey PRIMARY KEY,
     fname       TEXT NOT NULL,
     lname       TEXT NOT NULL,
     position    TEXT NOT NULL,
     sex         CHAR(1) NOT NULL,
     DOB         DATE NOT NULL,
     salary      INT NOT NULL,
     FOREIGN KEY (branchNo) REFERENCES Branch(branchNo),
     CHECK((position = 'Manager' or position = 'Assistant' or position = 'Supervisor') 
     AND (sex = 'F' OR sex = 'M')));

But it is more flexible if you reference those constants from other table (Perhaps a position_table)

answered Nov 9, 2014 at 23:12

Houari

5,6513 gold badges33 silver badges57 bronze badges

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

1 Comment

ryekayo Over a year ago

Thanks so much, I am actually following these examples in a book to prepare for a job interview :)

Collectives™ on Stack Overflow

Set rule in Postgres for entry in Column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related