Thanks for the quick response.
I thought I had clearly defined the problem and the relationships. What I'm
saying is that a report can link to zero or one mail rule. A mail rule
cannot exist on it's own, it will only be created as a result of a "parent"
report requiring it. One mail rule is not shared among many reports, this
is why I choose to use the FKMailRule column in the report table.
My question is, is using a nullable FKMailRule column the accepted way of
saying "this report may link to a mail rule or it may not, however, if it
does it must pass an integrity test to ensure that it links to a valid mail
rule." Thus having a foreign key constraint and a nullable column working
together to address the requirement.
Hope this clarifies the problem set.
--
Robert Chapman, MCSD
Manager, Applications Development
prairieFyre Software Inc.
http://www.prairiefyre.com
> Robert,
> you are describing a situation where Reports has the referencing column
> (foreign key), and MailRule the referenced column (primary or other
> unique key). Cascading definitions are defined on the referencing table,
> but only act on changes in the referenced table.
> For example:
> CREATE MailRule
> (Ruleid int NOT NULL PRIMARY KEY
> ,Rule varchar(100) NOT NULL UNIQUE
> )
> CREATE Reports
> (ReportID int NOT NULL PRIMARY KEY
> ,RuleID int NULL REFERENCES MailRule(RuleID) ON DELETE CASCADE
> )
> If, in the example above, a MailRule is deleted, all corresponding
> reports are deleted. But this does not work the other way round. If you
> delete a particular Report, the corresponding MailRule is not deleted.
> If you want the MailRules to be automatically deleted, then you are
> basically saying, the there is not a 1:n relation between MailRule and
> Reports, but a 1:1 relation (or a n:1 relation).
> You need to know the relations before you can model your database.
> Hope this helps.
> Gert-Jan
> > This is more of a design question I suppose. Consider a scenario where
I
> > have a report table and mail rule table. Each report can be linked, but
> > does not have to be, to one mail rule. Therefore, the report table has
a
> > FKMailRule column that keys to the mail rule pkey column. To
accommodate
> > for the fact the report does not have to have a mail rule I have
included a
> > row in the mail rule table with a pkey of 0, making it a magical control
> > row. So when adding a report to the database and add one row to the
report
> > table and I may or may not add one row to the mail rule table.
> > I want to use a cascading delete on the report table, so that whenever a
> > report is deleted the linked mail rule is deleted also. As you can
imagine
> > the cascade will delete the magical row 0 killing my control row
concept.
> > AFAIK, this leaves with 2 options:
> > 1) Use a trigger to re-create the row 0 when it is nuked
> > - this seems bogus and open to concurrency issues
> > 2) Change the FKMailRule column to allow NULL and get rid of the magical
row
> > 0 concept
> > - this means that I have to use LEFT OUTER joins in my queries and cover
off
> > the NULL values in client programs
> > To be honest, I have never used a nullable column before, this potential
> > solution only occurred to me last night. Is using the nullable column
the
> > accepted way of solving this type "might have a link" problem while at
the
> > same time preserving the foreign key integrity? Does the left outer
join
> > cause any performance issues as opposed to regular inner joins?
> > Any guidance is much appreciated.
> > Cheers
> > --
> > Robert Chapman, MCSD
> > Manager, Applications Development
> > prairieFyre Software Inc.
> > http://www.prairiefyre.com
by