Foreign Key with "backup"?

#1

Hi,

I have projects that *may* be split into phases and timereports records
that refer to a project and possibly to a phase.

It's not difficult to enforce this in software, what I'm unsure is if at
all is possible to express it in SQL

The situation is as follows

CREATE TABLE project (
project_code varchar(10) PRIMARY KEY,
...
);
CREATE TABLE phase (
code varchar(10) PRIMARY KEY,
project_code varchar(10) PRIMARY_KEY
...
);
CREATE TABLE timereport (
...
project_code varchar(10) NOT NULL,
phase_code varchar(10) NULL,
...
);
ALTER TABLE timereport ADD CONSTRAINT prj_phase
FOREIGN KEY (project_code, phase_code) REFERENCES phase(project_code, code)

This works *if* there is a project phase in table 'phase'. If there is no
phase, I want to retain the possibility to have a foreign key constraint
that points to the project.

The only solution I have is to create a view 'project_phase' that is an
OUTER JOIN between project and phase and make the foreign key constraint
of the table timereport reference that view.

Is there any other solution that I should be aware of?

thanks in advance
sandro

#2

ortles wrote:

Quote:

Hi,

I have projects that *may* be split into phases and timereports records
that refer to a project and possibly to a phase.

It's not difficult to enforce this in software, what I'm unsure is if at
all is possible to express it in SQL

The situation is as follows

CREATE TABLE project (
project_code varchar(10) PRIMARY KEY,
...
);
CREATE TABLE phase (
code varchar(10) PRIMARY KEY,
project_code varchar(10) PRIMARY_KEY
...
);
CREATE TABLE timereport (
...
project_code varchar(10) NOT NULL,
phase_code varchar(10) NULL,
...
);
ALTER TABLE timereport ADD CONSTRAINT prj_phase
FOREIGN KEY (project_code, phase_code) REFERENCES phase(project_code, code)

This works *if* there is a project phase in table 'phase'. If there is no
phase, I want to retain the possibility to have a foreign key constraint
that points to the project.

The only solution I have is to create a view 'project_phase' that is an
OUTER JOIN between project and phase and make the foreign key constraint
of the table timereport reference that view.

Is there any other solution that I should be aware of?

thanks in advance
sandro

The use of NULL at all is questionable at best. That said, nothing stops
you from declaring a second foreign key reference from timereport to
project.

However, the use of NULL (and in particular the use of NULL in part of a
compound key) is wonky suggesting a poor design from the start.

#3

Bob Badour wrote:

Quote:

ortles wrote:

Hi,

I have projects that *may* be split into phases and timereports records
that refer to a project and possibly to a phase.

It's not difficult to enforce this in software, what I'm unsure is if at
all is possible to express it in SQL

The situation is as follows

CREATE TABLE project (
project_code varchar(10) PRIMARY KEY,
...
);
CREATE TABLE phase (
code varchar(10) PRIMARY KEY,
project_code varchar(10) PRIMARY_KEY
...
);
CREATE TABLE timereport (
...
project_code varchar(10) NOT NULL,
phase_code varchar(10) NULL,
...
);
ALTER TABLE timereport ADD CONSTRAINT prj_phase
FOREIGN KEY (project_code, phase_code) REFERENCES phase(project_code, code)

This works *if* there is a project phase in table 'phase'. If there is no
phase, I want to retain the possibility to have a foreign key constraint
that points to the project.

The only solution I have is to create a view 'project_phase' that is an
OUTER JOIN between project and phase and make the foreign key constraint
of the table timereport reference that view.

Is there any other solution that I should be aware of?

thanks in advance
sandro

The use of NULL at all is questionable at best. That said, nothing stops
you from declaring a second foreign key reference from timereport to
project.

?? I thought that a second foreign key would at best be a second
requirement, so that the first one should be satisfied as well. Am i
wrong?

Quote:

However, the use of NULL (and in particular the use of NULL in part of a
compound key) is wonky suggesting a poor design from the start.

Why that? can you explain why you say so?
As I said a view with an OUTER JOIN allows me to avoid that. I tested it
and just works. So I wouldn't call it poor design but in this moment I
was just wandering if there is a way to avoid a view whose only purpose
is to enforce a foreign key.

It's a fact that not all projects need to be split into phases, and
that if I don't have a phase there's no point in adding a phase field. I
don't think that forcing all projects to declare a default phase would
be a better design.

sandro

#4

ortles wrote:

Quote:

Bob Badour wrote:

ortles wrote:

Hi,

I have projects that *may* be split into phases and timereports records
that refer to a project and possibly to a phase.

It's not difficult to enforce this in software, what I'm unsure is if at
all is possible to express it in SQL

The situation is as follows

CREATE TABLE project (
project_code varchar(10) PRIMARY KEY,
...
);
CREATE TABLE phase (
code varchar(10) PRIMARY KEY,
project_code varchar(10) PRIMARY_KEY
...
);
CREATE TABLE timereport (
...
project_code varchar(10) NOT NULL,
phase_code varchar(10) NULL,
...
);
ALTER TABLE timereport ADD CONSTRAINT prj_phase
FOREIGN KEY (project_code, phase_code) REFERENCES phase(project_code, code)

This works *if* there is a project phase in table 'phase'. If there is no
phase, I want to retain the possibility to have a foreign key constraint
that points to the project.

The only solution I have is to create a view 'project_phase' that is an
OUTER JOIN between project and phase and make the foreign key constraint
of the table timereport reference that view.

Is there any other solution that I should be aware of?

thanks in advance
sandro

The use of NULL at all is questionable at best. That said, nothing stops
you from declaring a second foreign key reference from timereport to
project.

?? I thought that a second foreign key would at best be a second
requirement, so that the first one should be satisfied as well. Am i
wrong?

You have a second requirement: Every time report is specific to a
project even when the timereport is not specific to a phase.

Quote:

However, the use of NULL (and in particular the use of NULL in part of a
compound key) is wonky suggesting a poor design from the start.

Why that? can you explain why you say so?

Entire books have been written on the subject going back 20 or 30 years.
Date's various /Writings.../ books explore the topic in great depth.

#5

Bob Badour wrote:

Quote:

The use of NULL at all is questionable at best. That said, nothing stops
you from declaring a second foreign key reference from timereport to
project.

?? I thought that a second foreign key would at best be a second
requirement, so that the first one should be satisfied as well. Am i
wrong?

You have a second requirement: Every time report is specific to a
project even when the timereport is not specific to a phase.

Exactely. If I add a second constraint *both* will be enforced, while as
you got pretty well some project may not even be present in table phase
as it does not have a phase. Adding a second constraint will not make it
fall back to the second constraint as far as I know. It would just
prevent the project from being accepted as it doesn't appear in table
phase. Unless I have a wrong idea of the role of the second constraint.

Quote:

However, the use of NULL (and in particular the use of NULL in part of a
compound key) is wonky suggesting a poor design from the start.

Why that? can you explain why you say so?

Entire books have been written on the subject going back 20 or 30 years.
Date's various /Writings.../ books explore the topic in great depth.

Strong ideas are normally easier to convey....

sandro

#6

Bob Badour wrote:

Quote:

However, the use of NULL (and in particular the use of NULL in part
of a compound key) is wonky suggesting a poor design from the start.

Why that? can you explain why you say so?

Entire books have been written on the subject going back 20 or 30 years.
Date's various /Writings.../ books explore the topic in great depth.

I would suggest reading a recent one:

http://www.amazon.com/SQL-Relational...8?tag=xaprb-20

The day we have all the other bells and whistles of tutorial D, I'll be
glad to avoid NULLs completely, but not sooner.

#7

ortles wrote:

Quote:

Bob Badour wrote:

The use of NULL at all is questionable at best. That said, nothing stops
you from declaring a second foreign key reference from timereport to
project.

?? I thought that a second foreign key would at best be a second
requirement, so that the first one should be satisfied as well. Am i
wrong?

You have a second requirement: Every time report is specific to a
project even when the timereport is not specific to a phase.

Exactely. If I add a second constraint *both* will be enforced, while as
you got pretty well some project may not even be present in table phase
as it does not have a phase. Adding a second constraint will not make it
fall back to the second constraint as far as I know. It would just
prevent the project from being accepted as it doesn't appear in table
phase. Unless I have a wrong idea of the role of the second constraint.

I don't know about postgres. Usually, partially missing keys are not
enforced.

Quote:

However, the use of NULL (and in particular the use of NULL in part of a
compound key) is wonky suggesting a poor design from the start.

Why that? can you explain why you say so?

Entire books have been written on the subject going back 20 or 30 years.
Date's various /Writings.../ books explore the topic in great depth.

Strong ideas are normally easier to convey....

NULL is not a strong idea. It was half-baked from the beginning.

#8

Marco Mariani wrote:

Quote:

Bob Badour wrote:

However, the use of NULL (and in particular the use of NULL in part
of a compound key) is wonky suggesting a poor design from the start.

Why that? can you explain why you say so?

Entire books have been written on the subject going back 20 or 30
years. Date's various /Writings.../ books explore the topic in great
depth.

I would suggest reading a recent one:

http://www.amazon.com/SQL-Relational...8?tag=xaprb-20

I have not had the pleasure of reading that one yet. How well does it
cover the problems caused by partially missing keys?

#9

Bob Badour wrote:

Quote:

I would suggest reading a recent one:

http://www.amazon.com/SQL-Relational...8?tag=xaprb-20

I have not had the pleasure of reading that one yet. How well does it
cover the problems caused by partially missing keys?

Basically, always avoiding NULLs, and providing a not null value
whenever NULLs are coming into existence.

Quote:

Strong recommendations:

* Do specify NOT NULL, explicitly or implicitly, for every column in every base table.
* Don't use the keyword NULL in any other context whatsoever (i.e., anywhere other than a NOT NULL constraint).
[...]
* Don't use NULLIF.
* Don't use outer join, and don't use the keywords OUTER, FULL, LEFT, and RIGHT (except possibly as suggested in the section "A Remark on Outer Join" below).
* Don't use union join.
* Don't specify either PARTIAL or FULL on MATCH (they have meaning only when nulls are present). For similar reasons, don't use the MATCH option on foreign key constraints, and don't use IS DISTINCT FROM. (In the absence of nulls, the expression x IS DISTINCT FROM y is equivalent to the expression x <> y.)
* [...] use COALESCE on every scalar expression that might otherwise
"evaluate to null."

#10

Marco Mariani wrote:

Quote:

Bob Badour wrote:

I would suggest reading a recent one:

http://www.amazon.com/SQL-Relational...8?tag=xaprb-20

I have not had the pleasure of reading that one yet. How well does it
cover the problems caused by partially missing keys?

Basically, always avoiding NULLs, and providing a not null value
whenever NULLs are coming into existence.

I did not mean to ask how Date recommends addressing the problems. I
meant how well the book explains the problems. Date's various
_Writings..._ books explain the myriad problems in great depth. The OP
asked why partially missing keys are wonky. Does this book explain why?