CODEWITHSUNDEEP
c#sqlsql-serverconstraints
James S
James S

Reputation: 25

Unique constraint only when two columns are duplicated in a row

I have a voting table which contains the company name of the user who has been voted on and the unique customer number of the user who voted on the company.

users vote (upvote or downvote) on other companies and have to provide their unique customer number to vote.

I want to create a unique constraint which allows the user to only vote once but cannot vote twice on the same Company however I want it to be that the same customer number can vote on multiple queries. I only know of the unique constraint but this doesn't work because it will only allow the user to vote once on ANY company as it activates the unique constraint.

For Example (this would be all good):

CompanyID  Voted(customer number)
Maccas     12345
BMW        12345
Maccas     66666

and I want to constrain the following (by activating a constraint when both company name and customer number are the same):

CompanyID  Voted
Maccas     12345
Maccas     12345 (should prevent duplicate of both columns co-existing)

My Code:

string voteTable = "INSERT INTO dbo.votes (CompanyID,Voted) Values (@CompanyID,@Voted)";

canUserVote(); //checks if customer number exists

                        this.companyName = Convert.ToString(e.CommandArgument);

                        SqlCommand insertVoterDetails = new SqlCommand (voteTable,voteDb); //inserts voter details to votes table
                        insertVoterDetails.Parameters.AddWithValue("@CompanyID", this.companyName);
                        insertVoterDetails.Parameters.AddWithValue("@Voted", custVoteTextBox.Text);

Thanks for the help!

Upvotes: 0

Views: 501

Answers (1)

Amnesh Goel
Amnesh Goel

Reputation: 2655

When you created your table then add a composite primary key in following manner..

CREATE TABLE Voting
(
  [CompanyID] INT NOT NULL,
  [Voted] INT NOT NULL

  CONSTRAINT PK_CompanyVote PRIMARY KEY NONCLUSTERED ([CompanyID], [Voted])
)

or as follows

create table Voting (
     CompanyID integer not null,
     Voted integer not null,
     primary key (CompanyID, Voted)
);

Here is the SQLFiddle and following is the whole code for you. Here I have created basic company, user and company user table, and inserted some dummy records.

Note: I have imposed foreign key constraints as well to make sure that you are inserting right values. However if you want then you can leave this part.

Create tables

create table C
(
CompanyID int primary key,
CompanyName varchar(50)
);

Create table U
(
UserID int primary key,
UserName varchar(50)
);

Create table CU
(
CompanyID int,
UserID int,
FOREIGN KEY (CompanyID) REFERENCES C(CompanyID),
FOREIGN KEY (UserID) REFERENCES U(UserID),
primary key (CompanyID, UserID)
);

Insert statements

Insert into c values (101,'Google')
Insert into c values (102,'Yahoo')
Insert into c values (103,'Microsoft')

Insert into U values (1001,'Mike')
Insert into U values (1002,'John')
Insert into U values (1003,'Hanis')

Insert into CU values (101,1001)
Insert into CU values (101,1002)
Insert into CU values (102,1002)

When I fired following query then I got an error as well, which is expected in your case.

Insert into CU values (102,1002)

Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK__CU__FCEF90863A56FFA3'. Cannot insert duplicate key in object 'dbo.CU'. The duplicate key value is (102, 1002). The statement has been terminated.

Upvotes: 1

Related Questions