ORACLE - Cannot insert a NULL value to a NON-Primary Key

Question

I Have searched the web and various forums but I cannot figure out why this won't work. My Database is made up from the following Tables:

CREATE TABLE CUSTOMER(
custid Number(4),
cfirstname varchar2(30),
csurname varchar2(20) NOT NULL, 
billingaddr varchar2(30), 
cgender varchar2(1),
CONSTRAINT custpk PRIMARY KEY (custid),
CONSTRAINT genderconst CHECK(cgender in ('M','F','m','f'))
);

CREATE TABLE PRODUCT(
prodid Number(4),
prodname varchar2(30),
currentprice Number(6,2),
CONSTRAINT cprice_chk CHECK(currentprice >= 0 AND currentprice <=5000 ),
CONSTRAINT prodpk PRIMARY KEY (prodid),
CONSTRAINT pricepos CHECK((currentprice >= 0))
);

CREATE TABLE SALESPERSON(
spid Number(4),
spfirstname varchar2(30),
spsurname varchar2(30),
spgender varchar2(1),
CONSTRAINT salespk PRIMARY KEY (spid)
);

CREATE TABLE SHOPORDER(
ordid Number(4),
deliveryaddress varchar2(30),
custid Number(4) NOT NULL,
spid Number(4) NOT NULL,
CONSTRAINT orderpk PRIMARY KEY (ordid),
CONSTRAINT orderfk1 FOREIGN KEY (custid) REFERENCES CUSTOMER(custid),
CONSTRAINT orderfk2 FOREIGN KEY (spid) REFERENCES SALESPERSON(spid)
);

CREATE TABLE ORDERLINE(
qtysold Number(4),
qtydelivered Number(4),
saleprice Number (6,2),
ordid Number(4) NOT NULL,
prodid Number(4) NOT NULL,
CONSTRAINT qty_chk CHECK (qtydelivered >= 0 AND qtydelivered <=99),
CONSTRAINT price_chk CHECK(saleprice >= 0 AND saleprice <=5000 ),
CONSTRAINT linefk1 FOREIGN KEY (ordid) REFERENCES SHOPORDER(ordid),
CONSTRAINT linefk2 FOREIGN KEY (prodid) REFERENCES PRODUCT(prodid)
);

And I am using an insert statement to insert the following:

INSERT INTO SHOPORDER(ordid, deliveryaddress, spid)
VALUES (41, NULL, 23);

Whether I use '' or NULL it gives me the error:

ORA-01400: cannot insert NULL into ("S9710647"."SHOPORDER"."CUSTID");

My issue that I have not set deliveryaddress as a Primary key nor is it a Foreign key or contain any NOT NULL CoNSTRAINTS.

Is there a factor that I am missing here? The majority of forums have had people with problems relating to constraints. I cannot see any conflicting constraints.

Cheers

Answer 1

You're only inserting the columns ordid, deliveryaddress and spid into SHOPORDER which means the others will probably default to NULL.

However, you've declared custId as NOT NULL so that's not allowed. You can actually tell what the complaint is by looking at the error message:

ORA-01400: cannot insert NULL into ("S9710647"."SHOPORDER"."CUSTID");
                                                            ^^^^^^

It's clearly having troubles with the CUSTID column there and you know you haven't explicitly set that, so it must be the default value causing you grief.

You can fix it by either inserting a specific value in to that column as well, or by giving a non-NULL default value to it, though you'll have to ensure the default exists in the CUSTOMER table lest the orderfk1 foreign key constraint will fail.

Answer 2

The problem is that this:

INSERT INTO SHOPORDER(ordid, deliveryaddress, spid)
VALUES (41, NULL, 23);

uses the default values for all columns that you don't specify an explicit value for, so it's equivalent to this:

INSERT INTO SHOPORDER(ordid, deliveryaddress, custid, spid)
VALUES (41, NULL, NULL, 23);

which violates the NOT NULL constraint on custid.