1

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?

Joshua von Damm
  • 35
  • 7
  • 1
    You have to use a trigger either a system one(FK) or your own custom one. – Adrian Klaver Jun 28 '22 at 19:10
  • Ok that was my suspicion, helpful to have it confirmed – Joshua von Damm Jun 28 '22 at 20:15
  • 1
    Does it make sense to add a third table with `TestID` as PK. – Mike Organek Jun 28 '22 at 22:21
  • Table 'a' is a list of all the testcases for a codebase, and there are other fields (which I didn't list) that are unique based on the TestVer. So It's not a bad idea as then table 'b' could reference that third table with a FK,, but then the third table would have to consist of just TestID, which isn't the best format. – Joshua von Damm Jun 30 '22 at 01:48

1 Answers1

1

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 ...
Bohemian
  • 412,405
  • 93
  • 575
  • 722