Skip to Main Content

Another newb error SQL

CuriousStudentJoOct 17 2013 — edited Oct 17 2013

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. 

  1. Add the following four tables to your
    database using the SQL statement CREATE TABLE (primary keys underlined) 

   

      Customer
      (customerName, street, customerCity) 

  1. Deposit
    (customerName, branchName, accountNumber,
    balance) - where branchName and customerName are foreign keys to tables Branch
    and Customer. 
  2. Loan
    (customerName, branchName, loanNumber,
    amount) - where branchName and customerName are foreign keys to tables Branch
    and Customer. 
  3. 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

This post has been answered by CuriousStudentJo on Oct 17 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Nov 14 2013
Added on Oct 17 2013
4 comments
337 views