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

Navigation
4

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

For example: If you have a people table that stores both clients and vendors (accept "C" or "F" in the "people_type" column). Imagine a sales table could only refer people who are customers but not suppliers. What is the best way to create this restriction?

One possible way (I do not know if the best) would be to create an additional column and a compound 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 marked posts from permitting them.

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

    
asked by anonymous 21.11.2014 / 19:12

2 answers

4

This here: 

UNIQUE (id, accepts_comments)

Not necessary. Verification of the primary key already exists (which already ensures that the record is unique).

This here: 

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

It also does not make much sense, since the table will only accept accepts_comments with the value true , and not with any other, which should be the case to make sense of this column to exist.

I understand that the intention was to take advantage of the foreign key: 

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

But it is not the right way to do it. The accepts_comments information only needs to exist in posts . You are creating a redundancy of data just to use the foreign key insertion impairment functionality that, in my view, is a gambiarra.

The correct way is:

1. Prevent direct insertion into the table 

DENY INSERT ON dbo.comments TO PUBLIC

2. Create a Stored Procedure that performs this insertion, checking if the post accepts 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 to Stored Procedure 

GRANT EXECUTE ON dbo.InsertComment TO PUBLIC

I'm assuming your schema will look like this: 

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)
);
    
21.11.2014 / 19:52
0

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

    
22.11.2014 / 12:11
Calculating with Excel times are not very accurate Button with text box high in Android API 14