SQL Server Diagrams. 'Optional' Relationships

[TomLeMes]

Hi everyone,

I've just started using the 'Diagrams' section of SQL Enterprise Manager. I'm completely new to it, so this is probably a very basic question.

I've set all the relationships between tables (linking pks to fks across tables), but some of these relationships are optional (i.e. can be left blank. Sorry, I'm sure there's probably a proper technical word for this kind of thing). SQL complains if I try to leave such fields blank (null) when inserting records (incidentally, I'm doing this from a VFP frontend).

I see I could just clear the "Enforce Relationship for INSERTS and UPDATES" checkbox on the relationship properties window - but is this the correct thing to do for this situation? Are there any other major considerations I should be concerned about? Sorry if this is a bit general...

 

[rac2]

Good question. I dont have the anwer to your question, but it started me thinking. Lets hope one of the true experts will address it.

I like your comment "... there's probably a proper technical word for this kind of thing... " . I think the technical word might be, that is not a relation. Can a relation be optional? I dont think so.

The cardinality of some relations might suggest that it is optional, Thing A may have zero or many Things B. Parents may have none or many children. This does not cause the problem you are describing. Table A is not required to have rows in Table B, the need for a value of the foreign key in B referring to A never comes up.

The issue comes when in some stage we dont know the relationship (foreign key value) yet but eventually we will find out.

Or it might be the case that this one is a new kind of thing and we have not yet created those kind of things in the foreign key table, but we sure will be doing that. In this case it might be worthwhile to re-organizing the computer processes so that the new key value is created before the new thing is added.

In either case the issue might be handled by creating primary key values for Unknown, or Other which can be used when the relationship is "optional".

But it might also be worthwhile thinking about why the relationship is optional and whether there might be a different structure to handle the optional cases.

 

[mrdenny]

I've had the occation to do what you are trying to do. I don't know of any technical name for it. What I did was clear the Enforce Relationships for Inserts and Updates checkbox. This will leave the relationship definned by it will not invorse relational integrity.

Granted this does break the concept of relating data, but every once in a while it does need to be done.

The better idea would be to redesign your tables so that you don't have to have relationships which aren't defined.

You can also insert a record into your parent table with a value of null (however this will only work if you don't have a primary key definned on the parent column).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TomLeMes]

Thanks Rac2, some interesting points for me to think about.

To give this some 'real world' context, I'm talking about a table of Loans. Some loans have an associated insurance policy. A loan can have only have one policy (or none), but a single policy can cover several loans. So our LOAN table has an FK_INSURANCE field which allows us to link to the INSURANCE table, but some Loans will never have a policy.

So perhaps your idea of creating a primary key value for 'NONE' would work. But perhaps (as you say) this just isn't a relation at all.

This whole diagram thing grew from other people wanting to know how the fields on my various tables fit together (hence wanting to show a 'link' between tables that perhaps isn't strictly speaking a relation). The diagram seemed like a good way of showing them these links and at the same time setting up constraints.

 

[rac2]

Possibly " SQL complains if I try to leave such fields blank (null) when inserting records " for a different reason than you thought.

I made tables for Loans and InsurancePolicy with a foreign key constraint on the Loans table, the insurance_id in the Loans table must match an insurance_id in the InsurancePolicy table.

I created this foreign key constraint with Create Diagrams wizard in Enterprise Manager.

When I try to add a row to Loans with an insurance_id which is not in InsurancePolicy SQL does indeed object.

When I add a row with NULL for insurance_id, SQL is happy.

Because what I did not say above is that the insurance_id column in Loans allows NULL values.

Regarding the unchecking of the option to Enforce Relationship on INSERT and UPDATE. After I uncheck it I can add a row to Loans with an insurance_id which is not in the InsurancePolicy table (one might say with a bogus insurance_id).

So the SQL complaint may come from the NOT NULL constraint on insurance_id in the Loans table, not from the FOREIGN KEY constraint.


It may be that the sense of "optional" can be stated like this -
A loan may have zero or one insurance policy.
An insurance policy may cover zero or many loans.

Although it may well be the case that you will not have any records for policies that do not cover some loan in which case it would be
An insurance policy may cover one or many loans.



Also, I misused the term relation in my earlier post. A relation is a table. A relationship is an association between two tables.

 

[TomLeMes]

Thanks again for your input Rac2, that all makes complete sense.

Tom