Referential Integrity

[NevilleT]

I have a design problem with enforcing referential integrity.

- I have 2 tables, tblProjects and tblBenchmarks. Each tblProject record
may have 1 or no tblBenchmarks records. There can only be one tblBenchmarks
record for each tblProject record
- Projects has a PK called ProjectNo.
- Benchmarks has a PK called ProjectNo which is also a foreign key for
tblProject
- I have a join type of "Only include rows where the joined fields from
both tables are equal"
- I have ticked the referential integrity box

When I try to enter a new project record I get an error.
"You cannot add or change a record because a related record is required in
'tblBenchmarks'."

Obviously I don't understand referential integrity properly. What I want to
achieve is to have a project record and the option to later add a single
benchmark (or not add as the case may be).

 

[Larry Daugherty]

If a project can only have one benchmark then you don't need
tblBencMark. All projects on which I've ever worked have had many
benchmarks.

If you were to allow more than one benchmark per project then your
join would be "all records from tblProject and only those records that
match from tblBenchMark".

the Primary Key of tblProject appears as the Foreign Key in
tblBenchMark. tblBenchMark can have its own Primary Key.

Until you get used to applying set theory in RDBMSs it's counter
intuitive. The records in the child table effectively identify
themselves as the "children" of the record in the parent table by
putting the Primary Key of the parent in each record as the Foreign
Key.

HTH

 

[NevilleT]

Hi Larry

Agree re should all be one table but for historical reasons I have two.
Given this limitation, I still don't understand why it will not work.

 

[Larry Daugherty]

Read *all* of my post. The answer is there. Start reading at "the
Primary Key

here's a different cut at it:

tblProject
ProjectID
ProjectName
ProjectDescription

tblBenchMark
BenchMarkID
ProjectID - Foreign Key is PK of parent table
BenchMarkName
BenchMarkDescription

For the relationship in Referential Integrity:

All of the records from tblProject and only those records from
tblBenchMark where tblBenchMark.ProjectID=tblProject.ProjectID

HTH

 

[Andy Hull]

Hi Neville

Try the following...

I recommend you make a copy of your db first
Then go into relationships and delete the one you have between tblProjects
and tblBenchmarks.

Now, set it up again but (and this bit is vital!) make sure when you create
the join drag the mouse from the ProjectNo in tblProjects to the ProjectNo in
tblBenchmarks.

So, drag FROM tblProjects TO tblBenchmarks
(not the other way round - you get different results)

When the edit relationships dialog is showing the table you dragged FROM
will be on the left hand side and the table you dragged TO will be on the
right headed Related Table/Query.

The rule is, the table that will be populated first (tblProjects in this
case) should be on the left (in the edit relationships window).

Experiment with a couple of new empty tables setting up the relationship
first one way then the other.

hth

By the way, I don't see a problem with having 2 tables as it prevents having
empty fields for all the projects without benchmarks. It all depends on what
proportion of projects don't have benchmarks.

Also, to Larry...
You wrote...
" If you were to allow more than one benchmark per project then your
join would be "all records from tblProject and only those records that
match from tblBenchMark". "

My understanding is that this is for a left outer join with tblProject as
the left table. And that it has nothing to do with whether the relationship
is 1-1 or 1-Many etc

Kind regards

Andy Hull

 

[Jamie Collins]

If a project can only have one benchmark then you don't need
tblBencMark. All projects on which I've ever worked have had many
benchmarks.

Until you get used to applying set theory in RDBMSs it's counter
intuitive. The records in the child table effectively identify
themselves as the "children" of the record in the parent table by
putting the Primary Key of the parent in each record as the Foreign
Key.

There is a design principle that a table models either an entity or
the relationship between entities but not both. It sounds like there
are two entity types here, 'project' and 'benchmark', and the
relationship between them should, in principle, be modelled using a
relationship table (a.k.a. junction table, join table, et al). The OP
stated the relationship type one-to-zero-or-one between 'project' and
'benchmark' respectively, so there should be a UNIQUE constraint
(index no duplicates) on each NOT NULL (required = true) column in
this relationship table.

Here's a different cut at it:

tblProject
ProjectID
ProjectName
ProjectDescription

tblBenchMark
BenchMarkID
BenchMarkName
BenchMarkDescription

tblProjectsBenchMarks [relationship table]
ProjectID Foreign Key REFERENCEs tblProject table ['parent' is
the wrong paradigm for SQL DBMS] and UNIQUE (or PK) in this table
BenchMarkID Foreign Key REFERENCEs tblBenchMark table (and
possible UNIQUE in this table).

Jamie.

--

 

[Pat Hartman \(MVP\)]

The join should be left outer whether the data is 1-1 or 1-m since there
may in fact be NO children.