SQL主键的外键问题

I do have a problem connecting two tables on MSSQL Management studio. My goal is connect tables by foreign key and if I delete user I want 2nd table entry will be deleted automatically. I plan to use DELETE Cascade method for that.

我在MSSQL Management Studio上连接两个表时遇到问题。我的目标是通过外键连接表，如果我删除用户，我想要第二个表项将被自动删除。我计划使用DELETE Cascade方法。

User:
CREATE TABLE [dbo].[Users](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Email] [nvarchar](89) NOT NULL,
[Name] [nvarchar](25) NOT NULL,
[Midname] [nvarchar](25) NOT NULL,
[Surname] [nvarchar](25) NOT NULL,
[Phone] [varchar](15) NOT NULL,
[Country] [smallint] NOT NULL,
[Manager] [nvarchar](89) NOT NULL,
[Referrer] [nvarchar](89) NOT NULL,
[Rank] [tinyint] NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
[Email] 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

SET ANSI_PADDING OFF
GO

Email is Primary key

电子邮件是主键

Payments:
 CREATE TABLE [dbo].[Investments](
[ID] [bigint] NOT NULL,
[Investor] [nvarchar](89) NOT NULL,
[Sum] [decimal](19, 4) NOT NULL,
[Currency] [smallint] NOT NULL,
[Credit] [decimal](19, 4) NOT NULL,
[CreditRate] [decimal](19, 4) NOT NULL,
[Rate] [tinyint] IDENTITY(1,1) NOT NULL,
[Date] [smalldatetime] NOT NULL,
[Comment] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Investments] PRIMARY KEY CLUSTERED 
(
        [ID] 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

ID is Primary key

ID是主键

My FK should be like USER->PAYMENTS or PAYMENTS->USER?

我的FK应该像USER-> PAYMENTS或PAYMENTS-> USER？

When I am trying to connect User -> Payments using foregn key by Email -> Investor, it tell me such error:

当我尝试通过电子邮件 - >投资者使用foregn密钥连接用户 - >付款时，它告诉我这样的错误：

The columns in table 'Payments' do not match an existing primary key or UNIQUE constraint.

表'付款'中的列与现有主键或UNIQUE约束不匹配。

Could you please explain me where problem is? And what I am doing wrong?

你能解释一下我的问题在哪里吗？我做错了什么？

2 个解决方案

#1

Change your structure to:

将您的结构更改为：

CREATE TABLE [dbo].[Users](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Email] [nvarchar](89) NOT NULL,
[Name] [nvarchar](25) NOT NULL,
[Midname] [nvarchar](25) NOT NULL,
[Surname] [nvarchar](25) NOT NULL,
[Phone] [varchar](15) NOT NULL,
[Country] [smallint] NOT NULL,
[Manager] [nvarchar](89) NOT NULL,
[Referrer] [nvarchar](89) NOT NULL,
[Rank] [tinyint] NOT NULL);

ALTER TABLE [Users]
ADD CONSTRAINT PK_UsersID PRIMARY KEY (ID);

and then

接着

CREATE TABLE [dbo].[Investments](
[ID] [bigint] NOT NULL,
[UserID] [bigint] NOT NULL,
[Sum] [decimal](19, 4) NOT NULL,
[Currency] [smallint] NOT NULL,
[Credit] [decimal](19, 4) NOT NULL,
[CreditRate] [decimal](19, 4) NOT NULL,
[Rate] [tinyint] IDENTITY(1,1) NOT NULL,
[Date] [smalldatetime] NOT NULL,
[Comment] [nvarchar](max) NOT NULL);

ALTER TABLE Investments
ADD CONSTRAINT PK_InstestmentsID PRIMARY KEY (ID);

ALTER TABLE Investments
ADD CONSTRAINT FK_UsersInvestments
FOREIGN KEY (UserID)
REFERENCES Users(ID);

Then join Users.ID on Investments.UserID

然后在Investments.UserID上加入Users.ID

2 个解决方案

#1

更多相关文章

随机推荐