Foreign key keeps saying not enough values-CodePudding

I have been trying to get this thing working for a few day now (foreign key) and it just don't work, and feel like every solution I used don't work, so i'm asking here to learn what was the problem and how to fix it

Table creation :

CREATE TABLE CUSTOMER
(
Customer_ID varchar(255) NOT NULL,
Customer_Name varchar(50),
Customer_Gender varchar(10),
Customer_DOB varchar(20) ,
CONSTRAINT CUSTOMER_PK PRIMARY KEY(Customer_ID)

) ;

CREATE TABLE PAYMENT
(
Payment_ID varchar(255) NOT NULL,
Cust_ID varchar(255),
Payment_Method varchar(30),
Payment_Date varchar(20),
Payment_Total NUMBER(10,2) ,
CONSTRAINT PAYMENT_PK PRIMARY KEY(Payment_ID),
CONSTRAINT fk_customer FOREIGN KEY(Cust_ID) REFERENCES CUSTOMER(Customer_ID)
) ;

Inserting values :

INSERT INTO CUSTOMER VALUES ('1277','Jenny','Female',    ( TO_Date ( '03/04/1988' , 'DD/MM/yyyy')));
INSERT INTO CUSTOMER VALUES ('3423','Bryan','Male',      ( TO_Date ( '15/06/1990' , 'DD/MM/YYYY')));
INSERT INTO CUSTOMER VALUES ('4385','Mohd Shafik','Male',( TO_Date ( '20/08/1993' , 'DD/MM/YYYY')));

INSERT INTO PAYMENT VALUES ('24P','Cash',            ( TO_Date ( '11/02/2022' , 'DD/MM/YYYY')),24.50);
INSERT INTO PAYMENT VALUES ('09p','Online Transfer', ( TO_Date ( '08/04/2022' , 'DD/MM/YYYY')),25.00);
INSERT INTO PAYMENT VALUES ('10P','Cash',            ( TO_Date ( '08/07/2022' , 'DD/MM/YYYY')),22.50);

The foreign keys are now working , but just for life of me can't figure out why the it spits out ORA-00947: not enough values and

ORA-01400: cannot insert NULL into ("SQL_GUUNNGDQAOXJVYPBKNMILVXJR"."PAYMENT"."PAYMENT_ID") ORA-06512: at "SYS.DBMS_SQL", line 1721

Also, Please explain to me how references works, i read a few places but they use words that just confuse me. Please and thank you!

CodePudding user response：


ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';


CREATE TABLE CUSTOMER
(
Customer_ID varchar(255) NOT NULL,
Customer_Name varchar(50),
Customer_Gender varchar(10),
Customer_DOB DATE ,
CONSTRAINT CUSTOMER_PK PRIMARY KEY(Customer_ID)
) ;

CREATE TABLE PAYMENT
(
Payment_ID varchar(255) NOT NULL,
Customer_ID varchar(255),
Payment_Method varchar(30),
Payment_Date DATE,
Payment_Total NUMBER(10,2) ,
CONSTRAINT PAYMENT_PK PRIMARY KEY(Payment_ID),
CONSTRAINT fk_customer FOREIGN KEY(Customer_ID) REFERENCES CUSTOMER(Customer_ID)
) ;


INSERT INTO CUSTOMER VALUES ('1277','Jenny','Female',    ( TO_Date ( '03/04/1988' , 'DD/MM/yyyy')));


INSERT INTO PAYMENT VALUES ('24P','1277','Cash',            ( TO_Date ( '11/02/2022' , 'DD/MM/YYYY')),24.50);

SELECT * FROM Customer;

CUSTOMER_ID    CUSTOMER_NAME    CUSTOMER_GENDER    CUSTOMER_DOB
1277            Jenny                   Female    03-APR-1988

SELECT * FROM payment;

PAYMENT_ID    CUSTOMER_ID    PAYMENT_METHOD    PAYMENT_DATE    PAYMENT_TOTAL
24P            1277    Cash    11-FEB-2022    24.5

CodePudding user response：

Use DATE data types to store date values (and VARCHAR2 instead of VARCHAR) in your tables:

CREATE TABLE CUSTOMER(
  Customer_ID     varchar2(255) NOT NULL,
  Customer_Name   varchar2(50),
  Customer_Gender varchar2(10),
  Customer_DOB    DATE,
  CONSTRAINT CUSTOMER_PK PRIMARY KEY(Customer_ID)
);

CREATE TABLE PAYMENT(
  Payment_ID     varchar2(255) NOT NULL,
  Cust_ID        varchar2(255),
  Payment_Method varchar2(30),
  Payment_Date   DATE,
  Payment_Total  NUMBER(10,2) ,
  CONSTRAINT PAYMENT_PK PRIMARY KEY(Payment_ID),
  CONSTRAINT fk_customer FOREIGN KEY(Cust_ID) REFERENCES CUSTOMER(Customer_ID)
);

Then name the columns in your INSERT statements:

INSERT INTO CUSTOMER (
  customer_id, customer_name, customer_gender, customer_dob
) VALUES (
  '1277','Jenny','Female', TO_Date('03/04/1988', 'DD/MM/yyyy')
);
INSERT INTO CUSTOMER (
  customer_id, customer_name, customer_gender, customer_dob
) VALUES (
  '3423','Bryan','Male', TO_Date('15/06/1990', 'DD/MM/YYYY')
);
INSERT INTO CUSTOMER (
  customer_id, customer_name, customer_gender, customer_dob
) VALUES (
  '4385','Mohd Shafik','Male', TO_Date('20/08/1993' , 'DD/MM/YYYY')
);

Then for the PAYMENT inserts, you have 5 columns in the table but only 4 pieces of data being inserted:

INSERT INTO PAYMENT (
  payment_id, payment_method, payment_date, payment_total
) VALUES (
  '24P','Cash', TO_Date('11/02/2022', 'DD/MM/YYYY'),24.50
);
INSERT INTO PAYMENT (
  payment_id, payment_method, payment_date, payment_total
) VALUES (
  '09p','Online Transfer', TO_Date( '08/04/2022' , 'DD/MM/YYYY'),25.00
);
INSERT INTO PAYMENT (
  payment_id, payment_method, payment_date, payment_total
) VALUES (
  '10P','Cash', TO_Date('08/07/2022', 'DD/MM/YYYY'),22.50
);

You have not provided a Cust_ID value so it will default to NULL in those rows.

If you want to provide a Cust_ID then add it to the statement:

INSERT INTO PAYMENT (
  payment_id, cust_id, payment_method, payment_date, payment_total
) VALUES (
  'ABC', '1277', 'Cash', TO_Date('08/07/2022', 'DD/MM/YYYY'),22.50
);

db<>fiddle here