时间戳

首页 > 解决方案 > 一个组件为外键的复合键

sql - 一个组件为外键的复合键

问题描述

我有一个财务应用程序，我每天都在其中存储股票数据，所以我从Tickerand创建一个主键Date。

我还需要制作Ticker（这是上述复合键的一部分）另一个名为Tickers.

但是，当我在 SQL Server Management Studio 中添加外键关系时，它要求我提供两个列的链接Ticker和Date，而 Ticker 表只包含Ticker（这是我只想绑定的）。

请对我做错了什么有任何想法，或者这是不可能的？

我的表格如下：

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

工作室

标签： sqlsql-serverdatabaseforeign-keyscreate-table

解决方案

您的设置存在的一个问题是父列和子列的数据类型不同：[dbo].[Tickers]([Ticker])is [nvarchar](8)，而[dbo].[AggregateDay](Ticket)is [nchar](8)。

如果对齐两种数据类型，则可以创建关系，如下所示：

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]

DB Fiddle 上的演示

推荐阅读