How should an empty title be checked in a database?

Question

I am doing my first database project.

I would like to know why you should use NOT NULL in the following query

...
TITLE nvarchar(60) NOT NULL
..

Context

CREATE TABLE Questions 
(
         USER_ID integer FOREIGN KEY 
                         REFERENCES User_info(USER_ID) 
                         PRIMARY KEY 
                         CHECK (USER_ID>0), 
         QUESTION_ID integer FOREIGN KEY REFERENCES Tags(QUESTION_ID) 
                             NOT NULL 
                             CHECK (USER_ID>0), 
         QUESTION_BODY nvarchar(4000) NOT NULL,
         TITLE nvarchar(60) NOT NULL,                               /////// HERE                             
         MODERATOR_REMOVAL boolean NOT NULL,
         SENT_TIME varchar(15) NOT NULL
)

I watched VPuml's tutorial. They put all values in Logical diagram nullable, while all the rest NOT NULL. This suggests me that nullable should be used with logical diagrams.

Is there any other use of not null in databases?

I feel that we can check that the user gives value by JS, for instance, not at a database level.

Answer 1

NOT NULL is often used for foreign keys (i.e.-links to other tables). It ensures that a row links to some other table.

It is almost always used on primary keys, meaning the unique identifier in the table.

It's also a great constraint for required fields. This ensures that the transaction will fail if a user doesn't enter in a title or a question. Databases are great at enforcing constraints like this very quickly, so you see a lot of that type of business logic put into databases. Do note, however, that you'll want to check for a database error on the application side and handle it accordingly.

Anyway, NOT NULL is really for a column you always want a value in. The cases for that are actually quite a few, but hopefully this gives you some semblance of why we use it.

Answer 2

If a null value is not allowed, you should check on user entry and have NOT NULL in the database. Having NOT NULL in the database allows you to make absolutely sure that no bad data is entered into the database, regardless of mistakes in front-end code.

However, since database errors are generally bad to show your users, you should check to make sure that a null value is not being submitted before it gets to the database check.

Answer 3

NOT NULL means that a value must be placed in the column. It is used when it makes no sense for the data to exist without a value.

For instance, in your "SO" database, it makes no sense for a question to exist without the user_id column being known. A question must always have been created by a user.

Answer 4

Occasionally, a query depends on whether a certain field has a value. Having the field be NOT NULL means you only have to check against a single empty value, in this case '' (the empty string). Allowing NULL values means you may have to check for that, too, which can complicate the query.