SQL trouble and google isn't being helpful.

S. Dale Morrey sdalemorrey at gmail.com
Sat Dec 14 14:47:02 MST 2013


I have a table that looks like this...

Index (self incrementing number), Address (string), Transaction (string),
Amount (probably long)

What I need to do is enforce a rule that a given combination of address and
transaction must be unique without making either Address or Transaction,
individually unique.

Ideally I would like to do something along the lines of
SELECT SUM(Amount) FROM Transactions WHERE Address='someaddress';

The problem that I'm facing is that there is a non zero probability that
two or more worker units could be trying to insert the same
transaction/address at the same time.  These would also have the same
amounts.  Under this table layout, the double insert would stick in two
rows.

Somehow I need to constrain the pair.  I guess I could concatenate
Address+Transaction and force that to be unique.  Then replace the query
statement with

SELECT SUM(Amount) FROM Transactions WHERE Chimera LIKE(Address);

But as you can tell from my naming the field Chimera, that solution just
doesn't sit right.
Any of you DB pros have a possibly better solution?

Thanks!


More information about the PLUG mailing list