What is the best way to create a conditional foreign key Constraint?

Question

In some cases, it is interesting to create a foreign key reference conditional, that is, that does not allow to reference all the records of another table, but only those that assume a condition.

For example: If you have a table people which stores both customers and suppliers (accepts "C" or "F" in the "person type" column). Imagine a sales chart could only reference people who are customers but not suppliers. What is the best way to create this restriction?

A possible way (I don’t know if the best one) would be to create an additional column and a composite reference, as in the example below:

CREATE TABLE posts(
  id INTEGER PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  accepts_comments BOOLEAN NOT NULL,
  UNIQUE (id, accepts_comments)
);

CREATE TABLE comments(
  id INTEGER PRIMARY KEY,
  post_id INTEGER NOT NULL,
  text VARCHAR(255) NOT NULL,
  accepts_comments BOOLEAN NOT NULL DEFAULT (true) CHECK (accepts_comments = true),
  FOREIGN KEY (post_id, accepts_comments) REFERENCES posts (id, accepts_comments)
);

INSERT INTO posts (id, title, accepts_comments) VALUES (1, 'aceita', true);
INSERT INTO posts (id, title, accepts_comments) VALUES (2, 'não aceita', false);


-- a parte interessante

-- ok
INSERT INTO comments (id, post_id, text)
VALUES (1, 1, 'aceita');

-- erro de chave estrangeira violada
INSERT INTO comments (id, post_id, text)
VALUES (2, 2, 'não aceita');

-- erro da constraint check
INSERT INTO comments (id, post_id, text, accepts_comments)
VALUES (3, 2, 'não aceita valor diferente', false);

The goal is to prevent comments on posts marked to not allow them.

Is this the right way to do it, or is there a more elegant way?

Answer 1

This here:

UNIQUE (id, accepts_comments)

No need. Primary key verification already exists (which already ensures that the record is unique).

This here:

accepts_comments BOOLEAN NOT NULL DEFAULT (true) CHECK (accepts_comments = true)

It doesn’t make much sense either, since the table will only accept accepts_comments with the value true, and not with anyone else, which should be the case to make sense that this column exists.

I understand the intention was to seize the foreign key:

FOREIGN KEY (post_id, accepts_comments) REFERENCES posts (id, accepts_comments)

But it’s not the right way to do it. The information accepts_comments only needs to exist in posts. You are creating a data redundancy just to use the feature of preventing entry by the foreign key which, in my view, is a gambit.

The correct way is:

1. Prevent direct insertion into the table

DENY INSERT ON dbo.comments TO PUBLIC

2. Create a Stored Procedure to carry out this insertion, by checking whether the post accept comments

Create PROCEDURE dbo.InsertComment
 @PostID INT,
 @Text VARCHAR(255)
AS
BEGIN

 INSERT INTO comments (post_id, text)
 SELECT p.id, @Text
 FROM posts p
 WHERE p.id = PostID
 AND accepts_comments = true

END
GO

3. Give GRANT only for the Stored Procedure

GRANT EXECUTE ON dbo.InsertComment TO PUBLIC

I’m guessing your schema will stay that way:

CREATE TABLE posts(
  id INTEGER PRIMARY KEY IDENTITY,
  title VARCHAR(255) NOT NULL,
  accepts_comments BOOLEAN NOT NULL
);

CREATE TABLE comments(
  id INTEGER PRIMARY KEY IDENTITY,
  post_id INTEGER NOT NULL,
  text VARCHAR(255) NOT NULL,
  accepts_comments BOOLEAN NOT NULL DEFAULT (true),
  FOREIGN KEY (post_id) REFERENCES posts (id)
);

Answer 2

Another way in this case would be a validation TRIGGER checking whether in the required condition the column meets the rule in question. I would still review the model because it may be a modeling problem.