Solved

Primary Key

Posted on 2014-01-23
8
268 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 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
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.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query 8 51
SQL Server - Find Parent/Child relationship tree between numeric range. 7 47
ms sql + get number in list out of total 7 36
SQL DATEADD 10 70
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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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