Solved

Primary Key

Posted on 2014-01-23
8
269 Views
Last Modified: 2014-01-23
Experts-

I've created the table below and was hoping to have brandID and agid as my primary keys.  We will have duplicate agid(1, 2, 10, 12 etc) but the combo of the channelID,agid and brandID is what I am trying to use as the primary key.  So if I enter channelID = 350 agid = 1 and brandID = 89 and another record of channelID = 350 agid = 1 and brandID = 10, I want to be allow to enter this into the db.

So the table looks like this
brandID    channelID   agid   headline   headertext
89              350               1
89              350               2
89              349               1
10              350               1
10                350             2
10                 350           16
CREATE TABLE [dbo].[messageMatching](
	[brandID] [int] NOT NULL,
	[channelID] [int] NOT NULL,
	[agid] [int] NOT NULL,
	[headline] [varchar](350) NOT NULL,
	[headerText] [varchar](350) NOT NULL,
 CONSTRAINT [PK_messageMatching] PRIMARY KEY CLUSTERED 
(
	[brandID] ASC,
[channelID] ASC,
	[agid] 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

Open in new window


The issue that I am having is when I try to insert record such as brandID = 10 channelID=                 350 agid = 2, I am getting a constraint violation error.

Any help would be appreciated

Thanks
nick
0
Comment
Question by:nmarano
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39802863
Just to be clear, the sample data and example insert do not reflect the real scenario, correct?  

I ask because you already have a row with "brandID = 10, channelID = 350, and agid = 2" in the sample of table you gave above.  Are you trying to UPDATE this row, maybe?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39802878
The issue that I am having is when I try to insert record such as brandID = 10 channelID=                 350 agid = 2, I am getting a constraint violation error.

which is normal, because you have that record already!
0
 
LVL 2

Author Comment

by:nmarano
ID: 39802937
Sorry-  To be clear...The data in the table looks like this....

brandID    channelID   agid   headline   headertext
89              350               1
89              350               2
89              349               1


So when I add a record of brandID = 10 channelID =350 and agid = 1 I am getting the constraint message.   I understand that I should get that message if the record exist, but I do not have any brandID = 10 in the DB yet
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39802960
sorry, but that is not possible. at least not on this table alone.
 one possible explanation would be a trigger (on this table) that inserts/updates another table with another constraint that is violated.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39802979
I agree with Guy.  Another possibility if there is no trigger is you have a foreign key constraint for one, or more, of brandID, channelID, and agid.  If brandID = 10 does not exist in the database yet, is it possible there is a brands table that you have to INSERT the brand in first.
0
 
LVL 2

Author Comment

by:nmarano
ID: 39802999
This is a brand new table with no relations set up yet.  I was simply trying this before I tried a bulk upload.  

I though the same thing that it shouldn't give me the message, but it did

I'll scrub some of the data to see if there may be a dupe in the data itself although I already did this.

Thanks
Nick
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 39803031
Also check for constraints that were created in error, maybe during design phase.  One could have been on a combination of the three columns that is not unique.
0
 
LVL 2

Author Comment

by:nmarano
ID: 39803090
Experts-

I dunno what to say....I dropped and recreated the table and uploaded all of the different files to the table without issue.  Not sure why though as I am using the same data as yesterday.

Thank you all for your comments, and I wish I had a better reason why it is now working except that it just is....
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem when I run a simple storeproc - help 4 30
average of calculation (TSQL) 4 33
find SQL job run average duration 24 54
Database Mail Profiles 1 19
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question