Solved

Primary Key

Posted on 2014-01-23
8
270 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 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 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 89
Stored Proc - Rewrite 42 77
Getting local user timezone in Sql Server 5 40
I am new to using JSON in SQL Server 2 57
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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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