Nullable Foreign key constriants
in [Programming SQL]
|
From: John Crews on 12 Dec 2009 21:57 I am just trying to get a general consenus from SQL experts on whether or not nullable Foreign Key contrainsts are a key database design or a poor database design. Comments and opinions on this is appreciatable. Thanks,
From: Tom Cooper on 12 Dec 2009 22:19 You should let foriegn keys be NULL when the business model requires that it can be NULL and not allow NULLs when the business model requires that a non null entry be made in the foreign key. For instance, for most companies, you can't have an order unless that order is going to a customer. So you would have a customer id in the Orders table, and that column would be declared NOT NULL since every order must have a customer. But, for example, if a new customer has been referred to you by an old customer then you have a business requirement to track that referral. So you have a foreign key on a column in your Customers table called ReferedByCustomerID that is a (self referencing) foreign key of Customers. If there is an entry in this column, you want it to be valid, so it would have a foreign key constraint. But not every new customer is referred by some other customer so you would want to allow the ReferredByCustomerID column to be NULL. Tom "John Crews" <trex3648(a)hotmail.com> wrote in message news:%23FtdFA6eKHA.4112(a)TK2MSFTNGP06.phx.gbl... >I am just trying to get a general consenus from SQL experts on whether or >not nullable Foreign Key contrainsts are a key database design or a poor >database design. Comments and opinions on this is appreciatable. > > Thanks, >
From: John Crews on 12 Dec 2009 22:25 thank you for your comments Tom. "Tom Cooper" <tomcooper(a)comcast.net> wrote in message news:%23xCO2M6eKHA.2188(a)TK2MSFTNGP04.phx.gbl... > You should let foriegn keys be NULL when the business model requires that > it can be NULL and not allow NULLs when the business model requires that a > non null entry be made in the foreign key. > > For instance, for most companies, you can't have an order unless that > order is going to a customer. So you would have a customer id in the > Orders table, and that column would be declared NOT NULL since every order > must have a customer. > > But, for example, if a new customer has been referred to you by an old > customer then you have a business requirement to track that referral. So > you have a foreign key on a column in your Customers table called > ReferedByCustomerID that is a (self referencing) foreign key of Customers. > If there is an entry in this column, you want it to be valid, so it would > have a foreign key constraint. But not every new customer is referred by > some other customer so you would want to allow the ReferredByCustomerID > column to be NULL. > > Tom > > "John Crews" <trex3648(a)hotmail.com> wrote in message > news:%23FtdFA6eKHA.4112(a)TK2MSFTNGP06.phx.gbl... >>I am just trying to get a general consenus from SQL experts on whether or >>not nullable Foreign Key contrainsts are a key database design or a poor >>database design. Comments and opinions on this is appreciatable. >> >> Thanks, >> >
From: --CELKO-- on 12 Dec 2009 23:06 >> I am just trying to get a general consenus from SQL experts on whether or not nullable Foreign Key constraints are a key database design or a poor database design.<< A very general question gets a very general answer :) I would prefer to use special values in the domain of the data elements involved. Remember good old "Professor TBD" (To Be Determined) when they did not have a professor for a class yet? This lets you have several special values for different situations such as"un-diagnosed" versus "diagnosed, unknown disease" -- very different kinds of missing data! I have a client who uses DUNS for his suppliers; if a new guy has no gotten one yet, then they use "Xdddddddd" until he does get one. But the real answer is that it depends on the data model.
From: John Crews on 13 Dec 2009 00:18
Celko, Thank you for reply. I was looking for a answer on general database design. If I were creating a database from the start, would it be a good design pattern to have nullable Foreign Key constraints or would it be a poor design pattern to have nullable Foreign Key constraints. However, I will attempt to illistrate an example with out having to layout the entire business requirements of the system I am designing. I would also like to point out that I have the opinion that nullable FK are the exception and not the rule and should be avoided if at all possible. I ask this only because my opinion is being challenged and I would like a consensus if I am just being hard-headed or am I generally correct on this concept. Lets say, for example, I have a table with the a PK, some attributes, and then five FK constraints to five look up tables. Upon creation of the record for this table none, one, or all of the FK values to the look up tables are known. Therefore, the potential to have a record with five null values is very likely. One solution is to have these FK attributes nullable (admittedly, this is most likely the easiest solution). Another solution is to have a record in each of the five look up tables with a value something similar to 'Not Known' (this seems to me to be the correct solution). Other solutions could be to remove the FK constriant, have the fields still be nullable, and enforce the RI via an Update or Instead of trigger or in the business layer code (Ouch....I am not a fan of any nullable attribute). Still, another possibility is to create five one-to-one tables with each having a FK constraint to their respective look up table (this would be my second choice). When the values for the look up tables are known for this particular entity then a record is created in the respective one-to-one table. So, for the above business model, am I being stubborn by insisting that our data model does not include nullable FK constraints or is not allowing nullable FK constraints a good database design practice? thanks, "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:54da716a-bebc-4b9a-b277-6d67d8375df0(a)e27g2000yqd.googlegroups.com... >>> I am just trying to get a general consenus from SQL experts on whether >>> or not nullable Foreign Key constraints are a key database design or a >>> poor database design.<< > > A very general question gets a very general answer :) > > I would prefer to use special values in the domain of the data > elements involved. Remember good old "Professor TBD" (To Be > Determined) when they did not have a professor for a class yet? This > lets you have several special values for different situations such > as"un-diagnosed" versus "diagnosed, unknown disease" -- very different > kinds of missing data! > > I have a client who uses DUNS for his suppliers; if a new guy has no > gotten one yet, then they use "Xdddddddd" until he does get one. > > But the real answer is that it depends on the data model. |