Solved

Primary Key

Posted on 2014-01-23
8
267 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
  • 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 142

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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