1

I am studying for a "Database" course, and we had this exercise in an example exam that the teacher uploaded, sadly he didn't give a complete correction but just a few tips. I am having trouble understanding why in the example below he tells us to use a trigger function instead of a simple NOT NULL constraint.

This is the exercise

Now I would have just implemented the relationship between B and C like this:

CREATE TABLE B (
  b1 int PRIMARY KEY,
  b2 varchar(255)
);

CREATE TABLE C (
  c1 varchar(255) PRIMARY KEY,
  c2 int
);

CREATE TABLE BtoC (
 b1 int REFERENCES B(b1) ON DELETE RESTRICT ON UPDATE CASCADE,
 c1 varchar(255) NOT NULL REFERENCES C(c1) ON DELETE RESTRICT ON UPDATE CASCADE,  -- This would ensure the same constraint, that C has to be at least once in the relation table?
 PRIMARY KEY(b1,c1)
);

But he told us for the realtionship between table B and C, we would need a trigger function to satisfy the cardinalities of the relationship, here are his words:

"NOT NULL on NOTHING will not have any effect (NOT NULL only applies to existing rows, not to non-existing rows), and it will not guarantee that the constraint is valid. So, NOT NULL does not work for relation tables, only for foreign keys."

I can't make any sense of his reasoning, maybe some can explain this to me? I would really appreciate it.

Improve this question
5
  • "I am having trouble understanding why in the example below he tells us to use a trigger function instead of a simple NOT NULL constraint." With other words your teacher suggest to replace CHECK (column NOT NULL) for some kind of trigger that does the same thing. Commented Jun 9, 2018 at 12:53
  • @RaymondNijland Alright but, he also said that the NOT NULL constraint would not satisify the relationship cardinaltiy, that means he implies that we HAVE to use a trigger function, which implies that both are not the same, atleast that's how I understand it. Commented Jun 9, 2018 at 13:01
  • I am not sure because the teacher said "So, NOT NULL does not work for relation tables, only for foreign keys.", what does that even mean, why should NOT NULL not work on junction tables/ realtion tables? Commented Jun 9, 2018 at 13:10
  • Also "(NOT NULL only applies to existing rows, not to non-existing rows)" CHECK (column NOT NULL) and INSERT INTO table (column) VALUES(NULL) the CHECK would reject the insert which can be seen as a "non existing" row/record. Commented Jun 9, 2018 at 13:14
  • Ohhh, meaning that the "non-exisiting row" aka NULL would violate the relationship cardinailties, using a check constraint or trigger function would elminate this Commented Jun 9, 2018 at 13:17

1 Answer 1

Reset to default
1

Your version guarantees that any record in BtoC has valid C reference. It does not guarantee that there is at least one row in BtoC for each C value. The tricky part here is the "1" in the relationship.

This is a tricky sort of relationship to represent -- I don't even know if it is worth doing so in SQL.

One method is to highlight one of the values. For instance, if the data were people in households, the highlighted person could be the "head of household". You need one of those. So, you could implement this as:

CREATE TABLE C (
  c1 varchar(255) PRIMARY KEY,
  c2 int,
  b_special int not null references b(b1)
);

This technically solves the problem, but the reference B values are split in two tables rather than in one.

If you want the special B to be in BtoC, then it gets tricky. You can do that with a reverse reference:

alter table c add constraint fk_c_b_special
    foreign key (b_special, c) references btoc(b1, c1);

You will then find that inserting data becomes tricky -- rows need to exist in both tables to insert a row into either table.

Common solutions are probably:

  • Relax the constraint and let the app insert into both tables.
  • Add a "special" column to C, but don't check that the column is actually in BtoC (although the code would always insert it).
  • Add a "special" column to C, and then allow a 0-n relationship for the rest of the columns.
  • Write a delete trigger for BtoC that does not allow deletion of the last B for a given C.
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you so much the first paragraph made it directly clear, what I was missunderstanding in that relationship. Now that I understand what the exercise wants I can easliy write a PLPSQL trigger function which makes sure that this constraint is satisfied. Just one more question what means * cardinality, is it 1..* or 0..*, other than that all is clear.
@TheMaco . . . I don't understand your question in the comment. The "1" and "0" are the minimum number of matches between the tables.
Ok, sorry I didnt explain it well, in the exercise in the picture from entitiy C to B there is a cardinality *, it has no minimum cardinality, so I wanted to ask if * is like 1..* or 0..*? I hope this makes it more clear.
@TheMaco . . . I'm pretty sure * is 0 .. *. But, I should admit that it is a long time since I thought about such formalities. In practice, you would just specify what you really mean.
Ok thank you so much for explaining this to me, I really appreciate it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.