CODEWITHSUNDEEP
sql
Amey Banaye
Amey Banaye

Reputation: 57

I have been assigned a project for currency exchange rate IN SQL . How to save exchange rates for currencies?

I Have to create a table currencies

CREATE TABLE currencies (
       from_currency        CHAR(30) NOT NULL,
       ExchRate             REAL NOT NULL,
       to_currency          CHAR(30) NOT NULL,
       PRIMARY KEY (from_currency),
       FOREIGN KEY (to_currency) REFERENCES currencies(from_currency)
)

I have to save exchange rate for 6 currencies: (inr,eur,cad,usd,gbp,cfp) But the problem is i can't store same values in first column i.e. I can save my currency rate as follow 

from_curr      ER --> to_curr 
INR        --> 60 -->   USD
USD        --> 0  -->   USD
GBP        --> 70 -->   USD

e.t.c. for all the currencies but now the problem is while i try to store the currency for other er like

INR        --> 1  -->   USD
or
GBP        --> 70 -->   USD

I get error duplicates values are not allowed ? How to solvve this problem ?

Upvotes: 1

Views: 1237

Answers (3)

Thomas Weller
Thomas Weller

Reputation: 11717

Of course you can't. That's because you defined PRIMARY KEY (). Thus only distinct value is allowed for this column. What you need instead is that pairs of from_currency/to_currency are unique. You can do this by defining a composite PK in your table:

CREATE TABLE currencies (
       from_currency        CHAR(30) NOT NULL,
       ExchRate             REAL NOT NULL,
       to_currency          CHAR(30) NOT NULL,
       PRIMARY KEY (from_currency, to_currency)
)

Upvotes: 1

Szymon
Szymon

Reputation: 43023

You should either create a composite primary key as a pair of 2 currencies is uniquely identifying a record for you.

CREATE TABLE currencies (
   from_currency        CHAR(30) NOT NULL,
   ExchRate             REAL NOT NULL,
   to_currency          CHAR(30) NOT NULL,
   PRIMARY KEY (from_currency, to_currency)
)

Or alternatively you can add a new column for the primary key, e.g. as autoincrement column:

CREATE TABLE currencies (
   [Id] [int] IDENTITY(1,1) NOT NULL
   from_currency        CHAR(30) NOT NULL,
   ExchRate             REAL NOT NULL,
   to_currency          CHAR(30) NOT NULL,
   PRIMARY KEY ([Id])
)

Upvotes: 1

Sparky
Sparky

Reputation: 15105

You are not storing a currency, but rather a pair of currencies. 

CREATE TABLE currency_exchange_rates (
       from_currency        CHAR(30) NOT NULL,
       to_currency          CHAR(30) NOT NULL,
       ExchRate             REAL NOT NULL,
       PRIMARY KEY (from_currency,to_currency),
)

I would also suggest a separate table holding the six currencies and linking the table above to ensure valid currency codes. You might also want to hold a date field (and make it part of the primary key), since rates can change over time...

Upvotes: 1

Related Questions