Search code examples
sqlsql-serverdatabaseforeign-keyscreate-table

Composite key with one component being a foreign key

I have a financial app where I store ticker data on a daily basis, so i create a primary key from the Ticker and Date.

I also need to make Ticker (which is part of the above composite key) a foreign key of another table called Tickers.

However when I go to add the Foreign Key relationship within SQL Server Management Studio, it is asking me to provide link for both columns Ticker and Date, whereas the Ticker table only contains Ticker (which is what I only want to bind on).

Any ideas on what I am doing wrong please or is this not possible?

My tables are as below:

CREATE TABLE [dbo].[Tickers](
    [Ticker] [nvarchar](8) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Market] [int] NOT NULL,
    [Locale] [int] NOT NULL,
    [Type] [int] NOT NULL,
    [Active] [bit] NOT NULL,
    [PrimaryExch] [nvarchar](50) NOT NULL,
    [Updated] [datetime2](7) NOT NULL,
    [Currency] [int] NOT NULL,
CONSTRAINT [PK_Tickers] PRIMARY KEY CLUSTERED 
(
    [Ticker] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AggregateDay](
    [Ticker] [nchar](8) NOT NULL,
    [Date] [date] NOT NULL,
    [Volume] [decimal](18, 0) NULL,
    [Open] [decimal](18, 0) NULL,
    [Close] [decimal](18, 0) NULL,
    [High] [decimal](18, 0) NULL,
    [Low] [decimal](18, 0) NULL,
    [Samples] [int] NULL,
 CONSTRAINT [PK_AggregateDay] PRIMARY KEY CLUSTERED 
(
    [Ticker] ASC,
    [Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

studio

Solution

  • One problem with your set-up is that the parent and child columns do not have the same datatype: [dbo].[Tickers]([Ticker]) is [nvarchar](8), while [dbo].[AggregateDay](Ticket) is [nchar](8).

    If you align both datatypes, then you can create the relationship, like so:

    CREATE TABLE [dbo].[AggregateDay](
    [Ticker] [nvarchar](8) NOT NULL 
        REFERENCES [dbo].[Tickers]([Ticker]),  -- foreign key declaration
    [Date] [date] NOT NULL,
    [Volume] [decimal](18, 0) NULL,
    [Open] [decimal](18, 0) NULL,
    [Close] [decimal](18, 0) NULL,
    [High] [decimal](18, 0) NULL,
    [Low] [decimal](18, 0) NULL,
    [Samples] [int] NULL,
CONSTRAINT [PK_AggregateDay] PRIMARY KEY CLUSTERED 
    ([Ticker] ASC, [Date] ASC)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
    ON [PRIMARY]
) ON [PRIMARY]

    Demo on DB Fiddle