September 10, 2008 at 11:55 am
Hi All,
Is there ever a good reason to have a table field defined as non-nullable with no default defined? This type of activity manifests itself in circular references within our database and also reeks havoc on our Redgate synchronization tool. Our dba insists this is normal practice. Is it?
September 10, 2008 at 12:04 pm
I wouldn't know about characterizing it as "normal practice", but I certainly do use it.
I use that setup for things that are required from an end-user, but are needed from a legal viewpoint to not be defaulted in. As in - I really want to make sure the user actually clicked "yes" or "no" on our policy confirmation, and not just default in one value or the other. There are a lot of scenarios where input is just plain required, and no default value is appropriate (like - your location, for a mapping program). If you need to enforce that requirement at the DB level, then Not Null with no default value is certainly one way to do it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 10, 2008 at 12:09 pm
Thanks for the quick reply Matt. I see your point, but isn't that type of validation best handled on the front-end?
September 10, 2008 at 12:23 pm
If the data is needed for DRI - you can't leave it up to the front-end to make those calls, nor can you enforce that from your front-end alone (as very well described by Mr. Codd in his definition of a RDBMS, the infamous "12 rules"). In other words "integrity independence" cannot be enforced adequately from any application layer, and is the responsiblity of the RDBMS.
What if it's a required key? As a matter of fact - a foreign key would be a rather common example of just that/
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 10, 2008 at 12:35 pm
Well, if there is no "default" value that can be justified at logical database schema design, there will be no default value at physical fase.
Having a column without a default value, only means the one inserting data will have to provide a value for that column. (and that can be a empty string, but then it is considered to be reflected about.)
IMO a better practice than just putting a default "WhoKnows".
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 10, 2008 at 1:03 pm
Good points guys. Primary Keys IMO should always be non-nullable. This is an absolute must. FK's, eh... not so much depending upon the usage of the table. I say this because this is where you may end up with circular references between tables which causes problems during data loads and table/DB synchronizations.
I don't think that the database should be enforcing the front-end validations, since from a performance standpoint, this would initiate extra DB communication and network traffic if, for example, an insert is rejected. The front-end should validate all data before server communication to increase client side performance.
I believe from a development standpoint, that the native default values of the various database types should be implemented via the 'default value' for all non-nullable types except for FK's. FK's I think can optionally be left as nullable.
Have a look here: http://www.sql-server-performance.com/articles/dev/sql_best_practices_p2.aspx
September 10, 2008 at 1:49 pm
Felix De Herrera (9/10/2008)
Good points guys. Primary Keys IMO should always be non-nullable. This is an absolute must. FK's, eh... not so much depending upon the usage of the table. I say this because this is where you may end up with circular references between tables which causes problems during data loads and table/DB synchronizations.I don't think that the database should be enforcing the front-end validations, since from a performance standpoint, this would initiate extra DB communication and network traffic if, for example, an insert is rejected. The front-end should validate all data before server communication to increase client side performance.
I believe from a development standpoint, that the native default values of the various database types should be implemented via the 'default value' for all non-nullable types except for FK's. FK's I think can optionally be left as nullable.
Have a look here: http://www.sql-server-performance.com/articles/dev/sql_best_practices_p2.aspx
Again - there is a certain amount of "it depends" based on what your business rules are.
That being said - I don't call something a foreign key unless ALL rows in a child table match up to something in the parent, and that then entails that the FK can't be null either. That disagreement aside - you will note that he STILL recommends that foreign key constraints be implemented at the data level (and not just the app side).
As to Nulls - The article tells me that the author doesn't really "get" what NULL means, so his objections to NULLs don't really wash. (There are several of his points I don't find valid at all, so I'm not sure I'd rely on them too heavily).
And yes - I am painfully aware of many procedural languages and their problems with NULL's , but that's not the data's fault, so having to work a little harder to deal with NULL's "on the way out" to , say, .NET code is well worth it. Proper use of NULLs is more than justified in an RDBMS, so I'm comfortable with letting the presentation layer figure out how to appropriately concatenate values (even NULLS).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 10, 2008 at 2:33 pm
Awesome Matt! I don't take anyone's view as being absolute. There always seems to be a mitigating circumstance that happens somewhere. You know the proverbial "Murphy's Law" kind of thing. Anyway, I was just trying to get a feel for what folks thought about this issue. You have been very helpful and I thank you for that. 😀
Felix
September 11, 2008 at 1:14 am
... I don't think that the database should be enforcing the front-end validations, since from a performance standpoint, this would initiate extra DB communication and network traffic if, for example, an insert is rejected. The front-end should validate all data before server communication to increase client side performance. ...
As usual ... it depends.
If you are concerned about data integrity, having clean data in your database, ....
If you have a single application(layer) that handles ALL your data manipulations ... but that cannot be quaranteed to be used ... :doze:
SSMS, MS office, vbscript, ...
You could as well define all columns nvarchar(max) .... the application will handle it.
It will always be a tradeoff wich part of datavalidation will also be performed at rdbms level.
You should at least use the correct datatype, implement FKs.
How much detail you put in a constraint, that's up to you.
(e.g. codeX char(1) valid values ('a','b','c')
From a db perspective, "everything" would be ideal.
From a dev-perspective, "everything" would be way to much.
How clean do you want your data to be ?
If you doni't put it _IN_ the database, it's always going to be prone to "human" error.
CREATE TABLE dbo.abc
(
col1 int NOT NULL IDENTITY (1, 1) primary key,
CodeX char(1) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.abc ADD CONSTRAINT
CK_abc_CodeX CHECK (CodeX in ('a','b','c'))
GO
insert into abc (CodeX) values ('d')
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 11, 2008 at 8:39 am
😀
Thanks ALZDBA, You are spot-on. It is rather a balancing act between development, performance needs, and database design. Compromises need to be made at both ends to get the best performance. Also there is the need to incrementally update client databases (future versions) without data loss. This also needs to be taken into consideration when developing a product's database.
I think a bunch of tables with nvarchar(max) data types is a bit extreme for database design... :hehe:
Certainly, use standard database design techniques and normalize somewhere around the third normal form. Basically, use the right techniques and tools appropriate for the job at hand.
Thanks Bud,
Felix
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply