Constructing SQL tables - use or not fields with Not Null?

Question

I have a question regarding the construction of tables and field use Not Null.

I know that for fields of Primary Key, it is necessary for him to be Not Null, but in other fields, what is the need to use Not Null? from what I understand if a field is set as Not Null, it did not accept null values, then in case, my application at the time of performing the insert, is not informed a certain field that is set as Not Null, we’ll have an exception?

If a field is set to Not Null, I am obliged to always mention it at the time of insert?

When to use fields Not Null?

Answer 1

Need to use Not Null?

You should create a column like Not Null when you see the need that that field has to be filled in at the time of the Insert. For example, imagine an address table a crucial field is the name of the STREET and the ZIP code these fields are very important to be informed, after all it would do no good to have the street number without knowing the NAME or the ZIP code.

Some columns in tables may have their value as null because there is or is not a data for that particular object.

A good example of this is the field phone (mobile), some people may not have a phone number to inform at the time of registration and other people will have, in this case the field with the Default null would be very nice.

Otherwise a certain field set as Not is reported Null, we have an Exception?

Yes, if you try to make one insert or updade in a column being not null will receive an exception.

Cannot insert NULL value in column 'Name', table 'Examples.dbo.Table'; column does not allow nulls. INSERT failure.

If a field is set to Not Null, I am required to always mention it at the time of the Insert?

Yes, if not the above error will be displayed.

When to use Not Null fields?

In cases where the field value has to exist (Ids as primary key) or fields must be required as the user name in a user table, but this depends a lot on the business rule of your application.

Answer 2

In case your field is of type "Not Null" you must always assign a value to it before saving, even if it is an empty value only the '' quotes in the case of characters. But there is also a use of this feature, which I particularly use a lot, if I have a "bool" field that is to record a user response, if this field is still in the database as "null" we know that a valid answer to that question has not yet been taken. If it’s true or false, we know it’s been answered. Same thing goes for dates, being the date null, we can consider that a payment has not yet been made, and when filled, this has been paid.

Answer 3

Hello.

I always have this problem of the default value in the field. First always left with Default Null, If the user filled or not the table would accept. When I started the relationship with other tables in the application is that I saw the problems. Records that had Datetime NULL fields were not listed or even other queries that need to compare values. Since NULL is unknown the query ignored it in SELECT.

To allow the user not to have to fill some fields, I started using a default value, in the case of CURRENT_DATE dates, foreign keys Default '0' or 'None selected', booleans Default '0', and so on.

Whenever a field is part of a query I prefer to leave it NOT NULL.

I hope I contributed.

Answer 4

"... it must be Not Null, but in other fields, what is the need to use Not Null? ..."

This accuracy will depend on the field being referenced in some relation that you do in the DB layer, otherwise, if it is a field that must have a value for some reference made with this field in the layer or layers higher than DB. Thus the choice to leave a field as "NOT NULL" is at the discretion of the design of the.

In some cases, as in the use of relations in the upper layers of the DB layer, the designer can leave a field set with a value "DEFAULT {value}" if he needs the value of that field for a reference in the application code.

But, if the DB layer, which is not because it is the first layer of an application is the most important, because the designer can draw almost all the software in that layer, that once it is working very round, is really the layer that will accomplish all the work of processing the application data, thus, will cover in the application, innumerable positive aspects in the issues traffic, speed, usability and security, since the processing of the data mass is almost all being done by the correct relations in the database, the application only has to worry about the "face-to-face user-data" which considerably facilitates the development, the usability, processing speed and hence application security.

Therefore, all fields that are referenced need to be either set to "NOT NULL" or set to "DEFAULT {value}".

I in some case prefer to do this check in the upper layers of the application.

But for example: Suppose that in a Voce application you have set the correct relationships in the database, logically you will depend on working hours to draw and format the tables in the database to meet the criteria of your application design, well, those hours that Voce will spend drawing well the database, are the hours that Voce will gain in the development of the application UI when the client will already be surely te "wiping the bag, kkkkkkkkk".

hope it helps.

Health and Peace!