Postgres - Allow only those codes from another table

pvsbandi
pvsbandi used Ask the Experts™
on
Hi,

   We are on Postgresql 9.7
I have two tables.
Table-1 has about 100 codes. As an example,
ID        Code
1           'M'
2           'C'
3           'R'

My table-2 has a code  field, which needs to only allow a subset of the codes from Table-1.
Example: Table-2 can only have values 'M' and 'R'.
Is it possible to create a constraint or something?
  Please advice.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SharathData Engineer

Commented:
You need a foreign key constraint on second table that refers the first table.
CREATE TABLE table2 (
    ...
    ...
    code varchar(10) REFERENCES table1 (code),
);

Open in new window

check documentation - https://www.postgresql.org/docs/9.4/ddl-constraints.html

Author

Commented:
I'm sorry, I forgot to add that Table-2 has to allow only a subset of the codes from Table-1.
For example:
Table-1 has:
M,N,O, P,Q,R,T

Table-2 shall only allow codes: M,O,P,T

The actual values are more than this example. So, wondering we could use a query Table-1 as a constraint on Table-2
Alternately, wondering if we can create a VIEW and add the constraint on it.
SharathData Engineer

Commented:
>> Table-2 shall only allow codes: M,O,P,T
How many values you have? You can create an ENUM type and use that.

From documentation - https://www.postgresql.org/docs/9.1/datatype-enum.html
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
INSERT INTO person VALUES ('Moe', 'happy1');
/*
ERROR:  invalid input value for enum mood: "happy1"
LINE 1: INSERT INTO person VALUES ('Moe', 'happy1');
*/

Open in new window

Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Author

Commented:
isn't there a constraint which can look from a query to limit the values from another table/view?
SharathData Engineer

Commented:
Table1 has many values and you want to restrict to few values in Table2.
Where do you define that few list? In another table? or a file?

Author

Commented:
Actually, the first table has a "Type" column, for different sets of "Code" values.
The restriction is something like:

SELECT code FROM Table_1 WHERE Type = 'Midrange'

It gives all the set of values needed
SharathData Engineer

Commented:
You need a trigger to achieve that. Here is a sample you can refer.

drop table if exists Table1;
create table Table1 (ID int, Code varchar(10), Type varchar(20));
insert into Table1 values (1, 'M', 'Midrange');
insert into Table1 values (2, 'C', 'midrange');
insert into Table1 values (3, 'R', 'other');

drop table if exists Table2;
create table Table2 (ID int, Code varchar(10));

CREATE FUNCTION Table2_code_check() RETURNS trigger AS $Table1_code_check$
    BEGIN
        -- Check that empname and salary are given
        IF NEW.code NOT IN (SELECT code FROM Table1 WHERE Type = 'Midrange') THEN
            RAISE EXCEPTION 'code doesn''t exist in Table1';
        END IF;
        RETURN NEW;
    END;
$Table1_code_check$ LANGUAGE plpgsql;

CREATE TRIGGER Table2_audit
AFTER INSERT OR UPDATE OR DELETE ON Table2
    FOR EACH ROW EXECUTE PROCEDURE Table2_code_check();

INSERT INTO Table2 VALUES (1,'M');

select * from Table2;

INSERT INTO Table2 VALUES (1,'Z');
-- P0001: code doesn't exist in Table1

Open in new window

Author

Commented:
Sharat,

  Table-2 is an independent table and has a lot of other fields.
Only for the "code" column, this constraint is needed. Inserts into this table happen from an application, so cannot use triggers.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial