ORA-01400: cannot insert NULL into

Question

I have two tables:

create table country(
country_code varchar2(2) PRIMARY KEY,
country nvarchar2(57) NOT NULL
);
SQL> desc airport_final
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 IATA_CODE                      VARCHAR2(3)
 CITY_CODE                      VARCHAR2(3)
 CITY_NAME                      NVARCHAR2(24)
 COUNTRY_CODE                       VARCHAR2(2)
 COUNTRY_NAME                       NVARCHAR2(33)
 NO_OF_AIRPORTS                     NUMBER(1)
 AIRPORT_CODE                       VARCHAR2(3)
 CITY_WITH_COUNTRY                  NVARCHAR2(61)
 AIRPORT_NAME                       NVARCHAR2(80)

When I select uique/distinct country_code then its showing 23 records:

 SQL> SELECT distinct(country_code),country_name from airport_final;

230 rows selected.

But when I try to insert these records from airport_final table to country table then:

SQL> INSERT INTO country(country_code,country)
SELECT distinct(country_code),country_name from airport_final;  2  
SELECT distinct(country_code),country_name from airport_final
                              *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("TESTING"."COUNTRY"."COUNTRY")

What should I do?

Best Regards

@Berend Yes some country_names are null but all country_code exists — Java_Beginner
– Java_Beginner, Commented Dec 13, 2023 at 19:59
You have a NOT NULL constraint on COUNTRY, so COUNTRY_NAME (where the data is coming from) must also be NOT NULL. — pmdba
– pmdba, Commented Dec 13, 2023 at 20:38
So, there's your answer. If country name is null, you can't insert it in the country field, which is not null — Berend
– Berend, Commented Dec 13, 2023 at 20:42
@pmdba You are absolutely right. Sometime solution is in front of our eyes but we are not getting it. Thanks from the bottom of my heart — Java_Beginner
– Java_Beginner, Commented Dec 13, 2023 at 21:33

warashi nguyen · Accepted Answer · 2024-01-11 16:01:08Z

-2

SELECT *
FROM airport_final
WHERE country_name IS NULL;
UPDATE airport_final
SET country_name = 'SomeDefaultValue'
WHERE country_name IS NULL;
INSERT INTO country (country_code, country)
SELECT DISTINCT country_code, country_name
FROM airport_final;

answered Jan 11, 2024 at 16:01

warashi nguyen

2332 silver badges6 bronze badges

Add a comment |

Stack Exchange Network

ORA-01400: cannot insert NULL into

1 Answer 1

Hot Network Questions

ORA-01400: cannot insert NULL into

1 Answer 1

Related

Hot Network Questions