Composite Primary key too long?
I am using mysql 5.5 on Ubuntu 13.04 to store similarity measures [-1,1]
between URIs. My table layout is very simple:
|--------------------------------------------------|
| uri1 | uri2 | value |
|--------------------------------------------------|
|http://foo.com/bar | http://bar.net/foo | 0.8 |
|http://foo.com/bar1 | http://bar.net/foo2 | 0.4 |
|--------------------------------------------------|
I want to make sure that for two specific uris, not more than one value is
stored. Therefore, I use the followin sql to create the table:
CREATE TABLE IF NOT EXISTS db.table(
uri1 VARCHAR(255) NOT NULL ,
uri2 VARCHAR(255) NOT NULL ,
value DOUBLE NULL ,
PRIMARY KEY (uri1, uri2),
INDEX (value) )
Unfortunately, when I am batch-inserting data (via Java JDBC), I get
Exceptions like the following:
java.sql.BatchUpdateException: Duplicate entry
'http://xmlns.com/foaf/0.1/Document-http://purl.org/linked-data/c'
for key 'PRIMARY'
It seems like the primary key is not long enough to store both URIs and I
therefore get duplicate entry exceptions when the prefix is the same
(which it is often in my data). I have checked and no "real" duplicates
are inserted. Is there a way to set the length of the primary key so that
it will always contain both URIs completely? Or is there generally a
better way to model the data?
I do not want to perform a check if a row with the supplied uri1 and uri2
already exists whenever I insert data, but rather handle the exception if
this may actually happen (which it shouldn't). Therefore, I think, it is
not feasible to just use an incrementing integer as primary key.
In my application, I will be creating several tables like this for
different measures and may later want to join them by uri1 and uri2, so
that I get a result that contains from different tables all the values for
a specific pair of uris.
Thank you very much for any suggestions!
No comments:
Post a Comment