Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Newb needs help

CuriousStudentJoOct 14 2013 — edited Oct 14 2013

Hello This is my first time requesting help from forums so please if I make a mistake I do apologize.

Before I start im not asking you to do my homework for me but rather point me in the right different and  a lil correction here and there.

so that you do constantly ask me what I am trying to achieve I will show you what my task wants me to do and how I have done it .. Bare in mind I  only started SQL 3 days ago***


"

 

  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). 

       
The foreign keys should be written so that if the primary key row is deleted the
corresponding foreign key row is also deleted

  • the
    foreign keys should be given constraint names
  • account
    numbers should be integers
  • balance,
    amount and assets should be decimals with two decimal places and a default of
    0.00

"

Below is how I attempted to achieve the above task

* Note I first created my table with only primary keys which I found and  and less confusing than created the tables with foreign keys all at once

CREATE TABLE Customers
(
CustomerName varchar (255),
street varchar (255),
customerCity varchar (255)
PRIMARY KEY (customerName) );                                            

CREATE TABLE Deposit (
customerName varchar (255),
branchName varchar (255),
accountNumber int,
balance decimal (7,2),
PRIMARY KEY (accountNumber) );

ALTER TABLE Deposit
add CONSTRAINT fk_BranchFOREIGN
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;

CREATE TABLE LOAN (
customerName varchar (255),
branchName varchar (255),
loanNumber int,
amount decimal (7,2) ,
PRIMARY KEY (loanNumber) );

ALTER TABLE Loan
add CONSTRAINT fk_Branch
FOREIGN KEY (branchName)
REFERENCES Branch (branchName)
ON DELETE CASCADE;


ALTER TABLE Loan
add CONSTRAINT fk_Customer
FOREIGN KEY (customerName)
REFERENCES Customer (customerName)
ON DELETE CASCADE;


CREATE TABLE Branch (
branchName varchar (255),
branchCity varchar (255),
Assets decimal (7,2) ,
PRIMARY KEY (branchName) );

The problem

I have managed to add the two foreign keys in the first table which in this case is the "Deposit" table

however when I try to add the two foreign keys in the second table which is "Loan" table it gives me the following error

SQL> ALTER TABLE LOAN

2          add CONSTRAINT fk_branch

3          FOREIGN KEY  (branchName)

4          REFERENCES Branch  (branchName)

5         ON DELETE CASCADE;

add CONSTRAINT fk_Branch

                               *

ERROR at line 2:

ORA-02264: name already used by an existing constraint

How to I solve this situation I am now concluding the two following theories

1. I am an idiot maybe you cannot have two foreign keys in two different tables

2. I am supposed to add the foreign keys at the same time as I do the PK

3. My lecturer has miraculously made a mistake

Thank you for taking time at looking at what I have done please remember im a noob so im new to this SQL im just trying to get that first  by understanding  how to create databases with no errors.

This post has been answered by Frank Kulash on Oct 14 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 11 2013
Added on Oct 14 2013
4 comments
528 views