Following the advice I got from my last topic I have now created my tables and now I want to populate them with my data but I get errors
below is my full sql command. From last time I learned that one foreign key can be implemented in one table and cannot be duplicated
to work around this I changed my foreign key as you can see to "fkone" . but why am I getting errors when trying to add information
I mean I have not gotten errors in the first two tables so what am I "doing wrong/ not understanding"
CREATE TABLE CUSTOMER (
CUSTOMERNAME varchar (25),
STREET varchar (25),
CUSTOMERCITY varchar (25),
CONSTRAINT CUSTOMER_pk PRIMARY KEY (CUSTOMERNAME));
CREATE TABLE DEPOSIT (
CUSTOMERNAME varchar (25),
BRANCHNAME varchar (25),
ACCOUNTNUMBER int,
BALANCE decimal (7,2),
CONSTRAINT DEPOSIT_pk PRIMARY KEY (ACCOUNTNUMBER));
CREATE TABLE LOAN(
CUSTOMERNAME varchar (25),
BRANCHNAME varchar (25),
LOANNUMBER int,
AMOUNT decimal (7,2),
CONSTRAINT LOAN_pk PRIMARY KEY (LOANNUMBER));
CREATE TABLE BRANCH (
BRANCHNAME varchar (25),
BRANCHCITY varchar (25),
ASSETS decimal (7,2) ,
CONSTRAINT BRANCH_pk PRIMARY KEY (BRANCHNAME));
ALTER TABLE DEPOSIT
add CONSTRAINT fk_BRANCH
FOREIGN KEY (BRANCHNAME)
REFERENCES BRANCH (BRANCHNAME)
ON DELETE CASCADE;
ALTER TABLE DEPOSIT
add CONSTRAINT fk_CUSTOMER
FOREIGN KEY (CUSTOMERNAME)
REFERENCES CUSTOMER (CUSTOMERNAME)
ON DELETE CASCADE;
ALTER TABLE LOAN
add CONSTRAINT fkone_BRANCH
FOREIGN KEY (BRANCHNAME)
REFERENCES BRANCH (BRANCHNAME)
ON DELETE CASCADE;
ALTER TABLE LOAN
add CONSTRAINT fkone_CUSTOMER
FOREIGN KEY (CUSTOMERNAME)
REFERENCES CUSTOMER (CUSTOMERNAME)
ON DELETE CASCADE;
INSERT INTO BRANCH (BRANCHNAME,BRANCHCITY,ASSETS) VALUES ('YORKSHIRE','NOTTINGHAM','10000');
INSERT INTO BRANCH (BRANCHNAME,BRANCHCITY,ASSETS) VALUES ('MIDLANDS','NOTTINGHAM','20000');
INSERT INTO BRANCH (BRANCHNAME,BRANCHCITY,ASSETS) VALUES ('ROYALBANK','NOTTINGHAM','25000');
INSERT INTO BRANCH (BRANCHNAME,BRANCHCITY,ASSETS) VALUES ('HFE','DERBY','15000');
INSERT INTO BRANCH (BRANCHNAME,BRANCHCITY,ASSETS) VALUES ('SOUTHERN','DERBY','30000');
INSERT INTO CUSTOMER (CUSTOMERNAME,STREET,CUSTOMERCITY) VALUES ('JONES','VICTORIA','NOTTINGHAM');
INSERT INTO CUSTOMER (CUSTOMERNAME,STREET,CUSTOMERCITY) VALUES ('PATEL','CHURCH','NOTTINGHAM');
INSERT INTO CUSTOMER (CUSTOMERNAME,STREET,CUSTOMERCITY) VALUES ('SMITH','DERBY','LEICESTER');
INSERT INTO CUSTOMER (CUSTOMERNAME,STREET,CUSTOMERCITY) VALUES ('AHMED','CHURCH','DERBY');
INSERT INTO CUSTOMER (CUSTOMERNAME,STREET,CUSTOMERCITY) VALUES ('BRAUN','ALFRED','DERBY');
INSERT INTO CUSTOMER (CUSTOMERNAME,STREET,CUSTOMERCITY) VALUES ('CHAN','VICTORIA','NOTTINGHAM');
Problems occur when trying to insert the following data into my tables
INSERT INTO DEPOSIT (CUSTOMERNAME,BRANCHNAME,ACCOUNTNUMBER,BALANCE) VALUES ('JONES','YORKSHIRE','1','100');
INSERT INTO DEPOSIT (CUSTOMERNAME,BRANCHNAME,ACCOUNTNUMBER,BALANCE) VALUES ('BRAUN','MIDLANDS','20','150');
INSERT INTO DEPOSIT (CUSTOMERNAME,BRANCHNAME,ACCOUNTNUMBER,BALANCE) VALUES ('AHMED','ROYALBANK','30','480');
INSERT INTO DEPOSIT (CUSTOMERNAME,BRANCHNAME,ACCOUNTNUMBER,BALANCE) VALUES ('SMITH','MIDLANDS','21','600');
INSERT INTO DEPOSIT (CUSTOMERNAME,BRANCHNAME,ACCOUNTNUMBER,BALANCE) VALUES ('PATEL','ROYALBANK','31','450');
INSERT INTO DEPOSIT (CUSTOMERNAME,BRANCHNAME,ACCOUNTNUMBER,BALANCE) VALUES ('PATEL','MIDLANDS','22','70');
INSERT INTO DEPOSIT (CUSTOMERNAME,BRANCHNAME,ACCOUNTNUMBER,BALANCE) VALUES ('BRAUN','SOUTHERN','41','2000');
INSERT INTO DEPOSIT (CUSTOMERNAME,BRANCHNAME,ACCOUNTNUMBER,BALANCE) VALUES ('JONES','HFE','42','4100');
ERROR at line 1:
ORA-02291: integrity constraint (N0430265.FK_CUSTOMER) violated - parent key not found
If your wondering what I am trying to achieve this is the given task I am meant to do.
- Add the following four tables to your
database using the SQL statement CREATE TABLE (primary keys underlined)
Customer
(customerName, street, customerCity)
- Deposit
(customerName, branchName, accountNumber,
balance) - where branchName and customerName are foreign keys to tables Branch
and Customer. - Loan
(customerName, branchName, loanNumber,
amount) - where branchName and customerName are foreign keys to tables Branch
and Customer. - Branch
(branchName, branchCity, Assets).
2.Add
the following rows of data to each table using the INSERT command:
| Branch |
branchName | | assets | branchCity |
Yorkshire | | 10000 | Nottingham |
Midlands | | 20000 | Nottingham |
| Customer |
customerName | | street | customerCity |
Jones | | Victoria | Nottingham |
Patel | | Church | Nottingham |
Deposit | |
customerName | branchName | accountNumber | balance | |
Jones | Yorkshire | 1 | 100 | |
| Loan | |
customerName | branchName | loanNumber | amount |
Jones | Yorkshire | 11 | 3000 |
Thanks for taking your time to help I appreciate every feedback I get that can improve my SQL because I am getting into it and I want to ace my coursework