Reference a Column From Another Table in PostgreSQL-CodePudding

I want to create the following tables (simplified to the keys for example):

CREATE TABLE a (
    TestVer VARCHAR(50) PRIMARY KEY,
    TestID INT NOT NULL
);

CREATE TABLE b (
    RunID SERIAL PRIMARY KEY,
    TestID INT NOT NULL
);

Where TestID is not unique, but I want table b's TestID to only contain values from table a's `TestID'.

I'm fairly certain I can't make it a foreign key, as the target of a foreign key has to be either a key or unique, and found that supported by this post.

It appears possible with Triggers according to this post where mine on insert would look something like:

 CREATE TRIGGER id_constraint FOR b
   BEFORE INSERT
   POSITION 0
 AS BEGIN
   IF (NOT EXISTS(
      SELECT TestID
      FROM a
      WHERE TestID = NEW.TestID)) THEN
    EXCEPTION my_exception 'There is no Test with id=' ||
      NEW.TestID;
 END

But I would rather not use a trigger. What are other ways to do this if any?

CodePudding user response：

A trigger is the only way to continuously maintain such a constraint, however you can delete all unwanted rows as part of a query that uses table b:

with clean_b as (
    delete from b
    where not exists (select from a where a.TestID = b.TestID)
)
select *
from b
where ...