SQL Foreign key issue with Primary Keys

Question

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.

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

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

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

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

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

and you set email in second table like foreign? add here statement. — Simon Dorociak, May 30 '12 at 21:46
1) Post your DDL for the tables. 2) Its a really bad idea to use email address as a primary key. — Perception, May 30 '12 at 21:49
You tagged your question [sql] but which? Oracle, MS SQL Server, MySQL... also, post the query which reports the error, that will help people resolve your issue. — Tony, May 30 '12 at 21:50
possible duplicate of [SQL Server 2008: The columns in table do not match an existing primary key or unique constraint](http://stackoverflow.com/questions/4667818/sql-server-2008-the-columns-in-table-do-not-match-an-existing-primary-key-or-un) — Tony, May 30 '12 at 21:58
http://stackoverflow.com/questions/3804108/is-email-address-a-bad-primary-key/3804174#3804174 — HLGEM, May 30 '12 at 22:38
WHy on earth woudl you want to remove payments if you remove a user? This will mess up your financial records and woudl be a very bad thing to do. What you shoudl do is inactivate users who are longer active, but never remocve thier historical payment data, the payment should never disappear. This kind of realtionships is why you should not use CASCADE DELETE. If there are payments, the user should NOT be removed. EVER. — HLGEM, May 30 '12 at 22:42
@HLGEM Good point. I put the `CASCADE DELETE` in my original answer as somewhat of a force of habit without recognizing the domain of OP's question. — swasheck, May 30 '12 at 23:01

swasheck · Accepted Answer · 2012-05-30T22:39:50.063

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

edited May 30 '12 at 22:39

answered May 30 '12 at 22:28

swasheck

4,644
2
29
56

Msg 1776, Level 16, State 0, Line 1 There are no primary or candidate keys in the referenced table 'Users' that match the referencing column list in the foreign key 'FK__Investmen__UserI__15502E78'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors. – Wild Goat May 30 '12 at 22:33
Sorry. I've made a few edits. I hadn't changed the PK on the original (Users) table to [ID] yet. – swasheck May 30 '12 at 22:34
Unfortunatelly tells what it is invalid syntax : Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ','. Msg 319, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. – Wild Goat May 30 '12 at 22:38
This is what happens when I don't just write my own :). I've updated it with SQL that I modified in my own SSMS and it works – swasheck May 30 '12 at 22:40
Each table should have a primary key. This is the way each record in the table is uniquely identified. In order to conform to the [relational model](http://en.wikipedia.org/wiki/Relational_model) and not duplicate data, you can use this key as a reference in another related table. As @HLGEM noted, email is not a good candidate. In my answer, I took a unique key (the identity `ID` column) and used it to related Investments to Users. I don't actually recall your original post, but I think that you weren't relating the same things. If you MUST use email, then a more descriptive column – swasheck May 30 '12 at 23:16
in `Investors` called `InvestorEmail` would have been a way to join the two. Having said that, I would still agree with @HLGEM and say that email is in no way a good PK candidate. – swasheck May 30 '12 at 23:17

Tony · Answer 2 · 2012-05-30T22:17:29.033

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.

From 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.

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?

It would help if you posted the DDL for the tables. In Management Studio right-click on the table and select "Script As..." and then "CREATE". Post the SQL script it displays so we can check the tables are correctly defined. — Tony, May 30 '12 at 22:09

SQL Foreign key issue with Primary Keys

2 Answers2