Execute the following script in Query Editor to demonstrate the use of FOREIGN KEY constraint to enforce referential integrity between the Currency and CurrencyRate tables.

USE [tempdb]
GO

CREATE TABLE [dbo].[Currency](
[CurrencyCode] [nchar](3) NOT NULL,
[Name] nchar(25) NOT NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Currency_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_Currency_CurrencyCode] PRIMARY KEY CLUSTERED
(
[CurrencyCode] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [tempdb]
GO

CREATE TABLE [dbo].[CurrencyRate](
[CurrencyRateID] [int] IDENTITY(1,1) NOT NULL,
[CurrencyRateDate] [datetime] NOT NULL,
[FromCurrencyCode] [nchar](3) NOT NULL,
[ToCurrencyCode] [nchar](3) NOT NULL,
[AverageRate] [money] NOT NULL,
[EndOfDayRate] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_CurrencyRate_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_CurrencyRate_CurrencyRateID] PRIMARY KEY CLUSTERED
(
[CurrencyRateID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[CurrencyRate] WITH CHECK ADD CONSTRAINT [FK_CurrencyRate_Currency_FromCurrencyCode] FOREIGN KEY([FromCurrencyCode])
REFERENCES [dbo].[Currency] ([CurrencyCode])
GO
ALTER TABLE [dbo].[CurrencyRate] CHECK CONSTRAINT [FK_CurrencyRate_Currency_FromCurrencyCode]
GO
ALTER TABLE [dbo].[CurrencyRate] WITH CHECK ADD CONSTRAINT [FK_CurrencyRate_Currency_ToCurrencyCode] FOREIGN KEY([ToCurrencyCode])
REFERENCES [dbo].[Currency] ([CurrencyCode])
GO
ALTER TABLE [dbo].[CurrencyRate] CHECK CONSTRAINT [FK_CurrencyRate_Currency_ToCurrencyCode]
GO

-- premature population of CurrencyRate result in error
INSERT dbo.CurrencyRate( [CurrencyRateDate],
[FromCurrencyCode],
[ToCurrencyCode],
[AverageRate],
[EndOfDayRate])
SELECT getdate(),
1,
2,
0.55,
0.40
GO

/*
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CurrencyRate_Currency_FromCurrencyCode". The conflict occurred in database "tempdb", table "dbo.Currency", column 'CurrencyCode'.
The statement has been terminated.
*/

--correct population of tables
INSERT [dbo].[Currency](
[CurrencyCode],
[Name])
SELECT 1, 'USD'

INSERT [dbo].[Currency](
[CurrencyCode],
[Name])
SELECT 2, 'EUR'

INSERT dbo.CurrencyRate( [CurrencyRateDate],
[FromCurrencyCode],
[ToCurrencyCode],
[AverageRate],
[EndOfDayRate])
SELECT getdate(),
1,
2,
0.85,
0.80
GO