Blog

Default Values and NOT NULL Constraints

Default Values and NOT NULL Constraints

In SQL database management systems, default values ​​and NOT NULL constraints are used to control the table data record information inserted by the user into database tables. Default values ​​and NOT NULL constraints help manage and control the integrity and consistency of table data, preventing or controlling errors or missing data values ​​in the table when the database user adds or updates new record information in the existing table.

Default Values and NOT NULL Constraints

Let’s learn more about default values ​​and NOT NULL constraints in SQL database management systems.

Default Values ​​in Database Management Systems.

In SQL database management systems, a default value is a data value that is automatically inserted into a table column during the INSERT data operation process when no data value is explicitly defined. Here, the default value helps manage that the table column is always inserted with a valid value, even if the database user does not provide any input value. Default values ​​can be especially useful for columns where missing values ​​could generate errors or issues, or where a specific default value is frequently used in a database table.

Features of Default Values ​​in DBMS.

  • Automatic Insertion – If no value is provided for a database table column, the default value is automatically used and inserted into that column.
  • Only for Non-NULL Columns – Database users can specify or set default values ​​only for those table columns that do not have a NOT NULL constraint. However, if no value is input, it overrides the NULL value in these table columns.
  • Data Integrity – Default values ​​ensure that a consistent value is provided in the table column, even if no data value is explicitly given.

Syntax for setting default values ​​in DBMS.

Database users can specify default values ​​for a table column when creating or modifying a new table.

Example of setting default values ​​when creating a new table.

CREATE TABLE employe (

employe_id INT PRIMARY KEY,

f_name VARCHAR(120),

l_name VARCHAR(110),

emp_hire_date DATE DEFAULT CURRENT_DATE,

status VARCHAR(40) DEFAULT ‘Active’

);

In this example.

  • Here, in this example, the default value for the emp_hire_date column in the employee table is set to CURRENT_DATE, which will automatically insert the current date as the default date if no date is inserted during the INSERT operation process in the database table column.
  • The default value for the status column is set to ‘Active’, meaning that if no status is specified in the table column, it will default to ‘Active’.

Example of adding a default value to an existing table column.

If the table already exists in the system, and the database user wants to add a default value to a table column, the database user can use the ALTER TABLE command or statement.

ALTER TABLE employe

MODIFY status VARCHAR(40) DEFAULT ‘Active’;

In this condition, when inserting new data into the table, if no value is specified or set, the table’s status column will now default to ‘Active’.

Behaviour of default values ​​in a database table.

Here, insert table column values ​​for emp_hire_date or status in the employee table without specifying them.

INSERT INTO employe (employee_id, f_name, l_name)

VALUES (403, ‘Siddhi’, ‘Deora’);

The new row will be inserted into the database table as follows.

emp_hire_date = CURRENT_DATE (current date)

status = ‘Active’

NOT NULL Constraint Values ​​in DBMS. The NOT NULL constraint feature in database tables ensures that a database table column cannot contain any NULL values. When a database user defines a NOT NULL constraint on a table column, every row in the database table must have a value for that column. This prevents inserting records into the table without providing a proper value for the constrained column.

Features of the NOT NULL constraint in DBMS.

  • Prevents NULL values ​​- The NOT NULL constraint ensures that a database table data column cannot contain NULL values, guaranteeing that every database table row will have a value for that column.
  • Mandatory data – This constraint is used for table columns where data is mandatory. For example, essential fields in a database table, such as employee name, employee email, or contact information in an employee table.
  • Data integrity – Data integrity in a database table ensures that a table column field is always populated with a proper value.

Syntax for applying the NOT NULL constraint.

The NOT NULL constraint can be applied when creating a new table in a database or later using the ALTER TABLE command or statement.

Example of applying NOT NULL when creating a database table.

CREATE TABLE employee (

employee_id INT PRIMARY KEY,

f_name VARCHAR(130) NOT NULL,

l_name VARCHAR(90) NOT NULL,

emp_hire_date DATE

);

In this example.

  • In this example, the f_name and l_name columns in the employee table cannot be set to NULL. If an INSERT command statement attempts to insert a row without providing values ​​for these columns, an error will be generated.
  • The emp_hire_date column does not have a NOT NULL constraint defined. Therefore, it can accept NULL values.

Example of adding a NOT NULL constraint to an existing table column.

Database users can modify existing table columns to apply the NOT NULL constraint.

ALTER TABLE employee

MODIFY emp_hire_date DATE NOT NULL;

This ensures that the emp_hire_date column in the existing database table cannot have NULL values ​​defined in the future.

Interaction between Default Values ​​and NOT NULL Constraints in DBMS.

  • NOT NULL with Default – If a column in a database table has both a NOT NULL constraint and a default value, then if no column value is provided, the column uses the default value. This ensures that the table column can never be NULL.
  • Default without NOT NULL – If a table column does not have a NOT NULL constraint defined, but has a default value, then if no table column value is provided, the default value will be inserted. However, NULL values ​​will still be permitted if indicated.

Here is an example of the combination of default values ​​and NOT NULL.

CREATE TABLE employee (

employee_id INT PRIMARY KEY,

f_name VARCHAR(130) NOT NULL,

l_name VARCHAR(100) NOT NULL,

emp_hire_date DATE DEFAULT CURRENT_DATE NOT NULL

);

In this case.

  • In this example employee table, f_name and last_name are necessary table column fields. This is due to the NOT NULL constraint.
  • The emp_hire_date table column must have a value and cannot be NULL. If no value is provided, the default value CURRENT_DATE will be used.

Conclusion on Default Values ​​and NOT NULL Constraints.

  • In SQL database management systems, default values ​​provide a method to automatically insert a predefined value into a database table column when no data value is provided during an INSERT operation. This helps maintain consistency in the database and reduces manual table data entry.
  • The NOT NULL constraint in database tables ensures that necessary table columns always contain data, preventing missing data values ​​that could cause problems in table data analysis or database application Behavior.

Leave a Reply