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