|
Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software
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
|
