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

  

What is the need to use Not Null?

You should create a column as Not Null when you see the need for that field to be filled at insert time. 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 not do any good thing you have the street number without knowing the NAME or the CEP.

Some columns in tables can have their value as null because of whether or not a given data exists for that particular object.

A good example of this is the (cell) phone field, some people may not have a phone number to tell you at registration time and others will have, in which case the field with Default null would be very good.

  

If you enter a field that is set to Not   Null, are we going to have an Exception?

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

Can not enter NULL value in column 'Name', table  'Examples.dbo.Tabela'; the column does not allow nulls. INSERT failed.

  

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

Yes, if not the above error will be displayed.

  

When should I use Not Null fields?

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

In case if your field is of type "Not Null" you should always assign a value to it before saving, even if it is an empty value only the quotes '' in the case of characters. However there is also a use of this functionality, 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 response has not yet been taken for that matter. if it is true or false we know it was answered. Same thing goes for dates, being the date null, we can consider that a payment has not yet been made, and when completed, this has been paid.

Hello.

I always have this default value problem in the field. First it 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 I saw the problems. Records that had the NULL DateTime fields were not listed or even other queries that need to compare values. Because NULL is unknown, the query ignored it in SELECT.

In order to allow the user not to fill in some fields, I used a Default value, in the case of CURRENT_DATE, foreign keys Default '0' or 'None selected', Booleans Default '0', and so on .

Whenever a field is part of a query I'd rather leave it NOT NULL.

I hope I have contributed.

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

This requirement will depend on the field being referenced in some relation you make in the DB layer, otherwise, if it is a field that needs to have a value for some reference made with that field in the layer or layers higher than DB. So the choice to leave a field like " NOT NULL " is at the discretion of the application design.

In some cases, such as in the use of relationships 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, other than the fact that it is the first layer of an application, is the most important, since the designer can draw almost all the software on that layer, which, once working well round, is really the layer that will perform all the work of data processing of the application, thus, will cover in the application, many positive aspects in the traffic, speed, usability and security requirements, since the mass processing of data is almost all done by the correct database relations, the application only has to worry about the "user-to-face" face-to-face, which considerably facilitates the development, usability, processing speed and consequently security of the application.

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

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

But for example: suppose that in an application you have defined the correct relationships in the database, logically you will depend on hours of work to draw and format the tables in the database to meet the criteria of the design of your application, now these hours that you will spend drawing well the database, are the hours that you will gain in the development of the application's UI when the client will already be sure to "shake the bag, kkkkkkkkk".

I hope it helps.

Health and Peace!