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
3
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
2
I searched for the error message you are seeing (without the table name) and the general consensus seems to be that a PRIMARY KEY
or UNIQUE
contraint has not been correctly set in your tables. The error also tells me you are (probably) using SQL Server.
我搜索了您看到的错误消息(没有表名),并且普遍的共识似乎是您的表中没有正确设置PRIMARY KEY或UNIQUE约束。该错误还告诉我你(可能)使用SQL Server。
From technet.microsoft.com:
来自technet.microsoft.com:
The columns on the primary key side of a foreign key relationship must participate in either a Primary Key or a Unique Constraint. After setting up a Primary Key or a Unique constraint for one of the tables you've selected, you can then define other relationships for that table.
外键关系的主键侧的列必须参与主键或唯一约束。在为您选择的某个表设置主键或唯一约束后,您可以为该表定义其他关系。
Check your PRIMARY KEYS
in both tables. Without the actual DDL of your tables it's difficult to be of more help.
检查两个表中的PRIMARY KEYS。没有表格的实际DDL,很难获得更多帮助。
EDIT: You have Email
as the PRIMARY KEY
in your Users
table but I do not see an Email
field in the Investments
table. Which fields are you joining on in your contraint?
编辑:您在“用户”表中将电子邮件作为PRIMARY KEY,但我在“投资”表中看不到“电子邮件”字段。你在哪个领域加入了你的约束?
#1
3
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
2
I searched for the error message you are seeing (without the table name) and the general consensus seems to be that a PRIMARY KEY
or UNIQUE
contraint has not been correctly set in your tables. The error also tells me you are (probably) using SQL Server.
我搜索了您看到的错误消息(没有表名),并且普遍的共识似乎是您的表中没有正确设置PRIMARY KEY或UNIQUE约束。该错误还告诉我你(可能)使用SQL Server。
From technet.microsoft.com:
来自technet.microsoft.com:
The columns on the primary key side of a foreign key relationship must participate in either a Primary Key or a Unique Constraint. After setting up a Primary Key or a Unique constraint for one of the tables you've selected, you can then define other relationships for that table.
外键关系的主键侧的列必须参与主键或唯一约束。在为您选择的某个表设置主键或唯一约束后,您可以为该表定义其他关系。
Check your PRIMARY KEYS
in both tables. Without the actual DDL of your tables it's difficult to be of more help.
检查两个表中的PRIMARY KEYS。没有表格的实际DDL,很难获得更多帮助。
EDIT: You have Email
as the PRIMARY KEY
in your Users
table but I do not see an Email
field in the Investments
table. Which fields are you joining on in your contraint?
编辑:您在“用户”表中将电子邮件作为PRIMARY KEY,但我在“投资”表中看不到“电子邮件”字段。你在哪个领域加入了你的约束?