ORA-01400: cannot insert NULL

Question

I am creating a new table "old_booking" based on an old one "booking" and inserting "dateTo" row into the new table from the old one.

I keep getting the error in sqlplus cmd line

ORA-01400: cannot insert NULL into ("SYSTEM", "OLDBOOKING", HOTELNO")

why is this and how can I fix it?

CREATE TABLE Booking
(hotelNo        CHAR(8)         NOT NULL
,guestNo        CHAR (8)        NOT NULL
,dateFrom        DATE
,dateTo            DATE
,CONSTRAINT FkhotelNo2    FOREIGN KEY (hotelNo) REFERENCES Hotel(hotelNo)
,CONSTRAINT FkguestNo    FOREIGN KEY (guestNo) REFERENCES Guest (guestNo)
);

CREATE TABLE Old_Booking
(hotelNo        CHAR(8)         NOT NULL
,guestNo        CHAR (8)        NOT NULL
,dateFrom        DATE
,dateTo          DATE
);

INSERT INTO oldBooking(dateTo)
SELECT dateTo FROM Booking;

Answer 1

As a minimum you need to add the not null columns in your insert statement:

INSERT INTO oldBooking(hotelNo, guestNo, dateTo) 
SELECT hotelNo, guestNo, dateTo FROM Booking;

You could also change the table definition for the old_booking table to allow null values by removing the 'NOT NULL' statements on hotelNo and guestNo.