Error message 8110 in SQL 2008

Question

0.00/5 (No votes)

See more:

I have build a database and created the below tables:

CREATE TABLE Signup (

UserName VARCHAR(30) NOT NULL PRIMARY KEY,
Email VARCHAR(30) NOT NULL PRIMARY KEY,
password VARCHAR(50),

)

CREATE TABLE Sigin (

UserName VARCHAR(30) FOREIGN KEY (UserName) REFERENCES Signup (UserName),
password VARCHAR(50) FOREIGN KEY (password) REFERENCES Signup (password)

)

ERRO IS:

Msg 8110, Level 16, State 0, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'Signup'.

I have then solved one by this:

CREATE TABLE Signup (

UserName VARCHAR(30) NOT NULL,
Email VARCHAR(30),
password VARCHAR(50),

CONSTRAINT [PK_DNC] PRIMARY KEY CLUSTERED ( UserName, password )

)

CREATE TABLE Sigin (

UserName VARCHAR(30) FOREIGN KEY (UserName) REFERENCES Signup (UserName),
password VARCHAR(50) FOREIGN KEY (password) REFERENCES Signup (password)

)

But now the error message is:

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'Signup' that match the referencing column list in the foreign key 'FK__Sigin__UserName__1ED998B2'.

Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

What I have tried:

How to handle this problem,please help me.

Posted 18-Jun-16 12:59pm

Stack Holder

Updated 19-Jun-16 2:28am

Add a Solution

Comments

phil.o 18-Jun-16 21:31pm

Using text fields as primary keys is a really bad idea, performance wise, let alone a composite one.
Use an integer type as primary key, which will be used as reference for linked tables.
Moreover, making unique the combination of username and email is a mistake: username should be unique, and email should be unique, both of them separately.

[no name] 19-Jun-16 10:44am

After 30 years doing sql I'm still not sure what is the right way.

"Using text fields as primary keys is a really bad idea":
I agree and not. I do it since 30 years, using integer type as "internal key" to manage relations. But how is about, that this means usually you need to suport also something like a User_ID which is the "users' primary"? It means one superfluously index more... still after 30years I have a "schizophrenic" view about this.

From the point of theory User_ID should be the primary. But often Theory and praxis are different.

Only a comment from my side, don't take it too serious
Bruno

phil.o 19-Jun-16 11:17am

It's a perfectly valuable comment, and you may have more expertise in this subject than I have. But in the present situation, having a double-text field as primary key seems really a bad choice; I would not like to have to debug a query with joins on this table :)

[no name] 19-Jun-16 12:28pm

Thank you for your reply.
Bruno

Stack Holder 19-Jun-16 7:03am

any solution of it from you?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

User 11060979 · Accepted Answer · 2016-06-19T02:28:00

In your first Approach you simply mixed up the keys.
Primary Key:

SQL

UserName VARCHAR(30) NOT NULL PRIMARY KEY,
Email VARCHAR(30) NOT NULL PRIMARY KEY,

vs. Foreign:

SQL

UserName VARCHAR(30) FOREIGN KEY (UserName) REFERENCES Signup (UserName),
password VARCHAR(50) FOREIGN KEY (password) REFERENCES Signup (password)

Anyway I prefer this way (approved with MSSQL 2014):

SQL

CREATE TABLE Signup (

 UserName VARCHAR(30) NOT NULL,
 Email VARCHAR(30) NOT NULL,
 password VARCHAR(50),
 CONSTRAINT PK_Signup PRIMARY KEY (UserName, password)
 )

CREATE TABLE Sigin (

 UserName VARCHAR(30),
 password VARCHAR(50),
CONSTRAINT FK_Signup FOREIGN KEY (UserName, password) REFERENCES Signup (UserName, password)

 )

Note 1: For "key-fields" I usually define a type for them. E.g:

SQL

CREATE TYPE DOM_USERNAME  FROM VARCHAR(30)
CREATE TYPE DOM_PWD  FROM VARCHAR(50)

Note 2: What should be the Primary
Think about using only UserName as Primary. E.g: Imagine here in CP what confusion came up in case same "user name" would be allowed for different users and the only difference would be the password...