ora-01400 cannot insert null values solution and commands

The ORA-01400: cannot insert NULL into error occurs in Oracle when you try to insert a NULL value into a column that has a NOT NULL constraint. This means the column requires a non-null value, and the query is attempting to insert a NULL.

Solutions:

1. Insert a Non-NULL Value

Ensure that you are providing a valid value for all NOT NULL columns in the INSERT statement.

Check the table definition to identify columns with the NOT NULL constraint:

DESC table_name;

Example:

DESC employees;

This will show the columns, data types, and constraints (NOT NULL) for each column.

Correct the INSERT statement to include valid non-null values for all NOT NULL columns:

INSERT INTO table_name (column1, column2, column3)

VALUES (value1, value2, value3);

Example:

INSERT INTO employees (employee_id, first_name, last_name)

VALUES (101, 'John', 'Doe');

2. Provide Default Values

If the application is inserting data into only some columns, and others are NOT NULL, you can set default values for those columns.

Alter the table to set a default value for the column:

ALTER TABLE table_name MODIFY column_name DEFAULT default_value;

Example:

ALTER TABLE employees MODIFY hire_date DEFAULT SYSDATE;

This ensures that if a value is not provided for hire_date, the system will automatically insert the current date.

3. Insert into All Required Columns

If the error occurs because you missed specifying a column in the INSERT statement, include it in the statement along with its value.

If you attempt:

INSERT INTO employees (employee_id, first_name)

VALUES (101, 'John');

And the column last_name is NOT NULL, this will result in an ORA-01400 error. The solution is to include the missing NOT NULL column:

INSERT INTO employees (employee_id, first_name, last_name)

VALUES (101, 'John', 'Doe');

4. Remove the NOT NULL Constraint (Not Recommended for Production)

If it's acceptable for the column to allow NULL values, you can remove the NOT NULL constraint from the column.

Alter the column to remove the NOT NULL constraint:

ALTER TABLE table_name MODIFY column_name NULL;

Example:

ALTER TABLE employees MODIFY last_name NULL;

Note: This is generally not recommended unless you are sure that NULL values are acceptable for that column.

Example Scenario:

Let’s assume you have a table employees with the following structure:

CREATE TABLE employees (
  employee_id NUMBER NOT NULL,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50) NOT NULL,
  hire_date DATE NOT NULL
);

If you attempt to insert a row without specifying values for last_name or hire_date:

INSERT INTO employees (employee_id, first_name)

VALUES (101, 'John');

You will get the ORA-01400 error because last_name and hire_date are NOT NULL.

To fix this, you must modify your INSERT statement to include values for all NOT NULL columns:

INSERT INTO employees (employee_id, first_name, last_name, hire_date)

VALUES (101, 'John', 'Doe', SYSDATE);

Key Takeaways:

Check which columns are NOT NULL.

Provide non-null values in the INSERT statement for all NOT NULL columns.

Optionally, you can use default values for columns or remove the NOT NULL constraint, but this should be done carefully.