Postgres - Allow only those codes from another table

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.
pvsbandiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
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
pvsbandiAuthor 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 EngineerCommented:
>> 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

Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

pvsbandiAuthor Commented:
isn't there a constraint which can look from a query to limit the values from another table/view?
SharathData EngineerCommented:
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?
pvsbandiAuthor 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 EngineerCommented:
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

pvsbandiAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.