Hi all, I'm guessing this is a fairly common question, but I've drawn a blank so far with all workarounds possible. Here's the rough scenario: We have data streaming in constantly from various offsite locations. This comprises of several details: a session, a unit of that session, and a measurement of that unit. Data is captured and transferred on a timed process, so often data is repeated in the data transfer packets. This repeating is unavoidable as the software used to capture dumps this data for each measurement and each unit for the session it's working on.
Due to the volume, a bulk update is done using "values()" with an insert statement. Unfortunately, often there are repeats of either session, unit, or measurement (as there could be new measurement or unit for the capture that needs to be associated to the session). The problem I've been experiencing is fairly straightforward (I hope): I have two, sometimes three columns in any given record that need to always be unique. This comprises an ID (the key column), and one (depending on the table, sometimes two) GUIDs which should be unique at all times for the entire table. I've tried setting the additional columns to be a primary key (which in turn sets them up to be "unique" when viewed under "Schema Indices" on the MySQL Administrator tool); however this does not give an error (or fail silently with "insert ignore") when I insert a duplicate - mySQL seems quite happy to add the duplicate record. At the moment, I'm running a process in the beginning which simply gathers all guids from the database and compares them as it runs through the data (then adds new ones as it runs).. This is hardly reliable, and also means starting the service would take several hours to gather the existing guids at current data levels... almost frightening to think, what will end up happening as the data expands. I'm hoping that I'm just missing something really daft and that there is a much easier way to ensure several columns are always unique in my table, while still benefitting from the bulk load insert? Details on installation used: MySQL version: v 4.1.14 (scheduled for upgrade in 2008) OS: Windows Server 2003 (std edition) Memory: 2GB
