Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Primary Key

Posted on 2014-01-23
8
Medium Priority
?
272 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 60

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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 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 60

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 60

Accepted Solution

by:
Kevin Cross earned 1000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

688 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