Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2003-08-24 : 21:09:30
|
I've got a join table that links users to actions, and uses a smalldatetime field to log when the action occured. This table gets a *lot* of inserts.In order to prevent an error from a dupe key insert, right now I'm doing a if not exists (select * from table where i_users=@iUsers and i_actions=@iActions and sdatetime=@sdDate) insert into table (i_users,i_actions,sdatetime) VALUES (@iUsers,@iActions,@sdDate) The table has a primary key on i_users,i_actions,sdatetime.The intent is to just ignore it if a user has hit reload on a page or otherwise triggered the same action twice (there are no actions that care about being invoked twice in a row).I'd love some way to tell SQL server to just let me try the insert and ignore it if there's a duplicate. The insert is happening in an SP -- is there any try/catch equivelent that I can use around the insert so the dupe primary key error doesn't make it back to the ASP app?I've thought about just adding an identity column to the table and putting the primary key on that, but that seems silly. Ideas?Thanks-b |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-08-24 : 21:37:55
|
There is no way to "ignore" the dups but you could try a slightly different technique for insertion...INSERT INTO TableSELECT i_users,i_actions,sdatetime FROM (SELECT @i_users AS i_users,@i_actions as i_actions, @sdatetime as sdatetime) AS XWHERE NOT EXISTS(SELECT 1 FROM Table WHEREi_users = @i_users AND i_actions = @i_actions AND @sdatetime = sdatetime) DavidM"SQL-3 is an abomination.." |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-08-24 : 21:42:37
|
| Ok, I've thought about this a bit more, and realized that whenver I post "How can I get around this problem which I'm having with good database design," the answer is (and should be) "just design the thing right in the first place".So here are the business requirements that led to my PK problem:Some users on my system are limited to a certain number of actions per day, for some action types. Others are not limited. All actions, though, need to be logged. Though duplicate actions (within the 1 minute resolution of a smalldatetime) are fine or desirable to ignore.So the app has two requirements:- For some users, see how many times they've performed an action in the past 24 hours- For all users, log the actionI'm starting to think that this table doesn't (gasp!) need a PK. It seems like the requirements can be met by a nonclustered, non-primary, INGORE_DUP_KEY key on i_users,i_actions,and sdatetime. There is no scenario where a particular row from this table would be referenced somewhere. Hence, I'm thinking that I may have my very first table without a PK. Does that make sense?Thanks-b |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-08-24 : 21:54:13
|
| Why not use a DateTime column instead of SmallDateTime?DavidM"SQL-3 is an abomination.." |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-08-24 : 22:36:49
|
| IMHO, transaction tables of this nature are fine to implement using an IDENTITY as a primary key....- Jeff |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-08-24 : 23:16:30
|
| Why even bother Jeff? Just call it a bag and not worry about a key at all..DavidM"SQL-3 is an abomination.." |
|
|
|
|
|
|
|
|
Any way to ignore attempt at dupe PK insert?