Constraint error: (201) Duplicate keys are not allowed
j-berman opened this issue · 2 comments
I've encountered what appears to be a bug with insertOrReplace. When attempting to insertOrReplace a duplicate into a table with a unique constraint on multiple columns -- and no columns in this unique constraint are the primary key -- the 201 constraint error is thrown. I reproduced it using code similar to the Quick Start:
<!doctype html>
<html>
<head>
<meta charset="utf-8" />
<title>Minimal example of using Lovefield</title>
<script src="bower_components/lovefield/dist/lovefield.min.js"></script>
</head>
<body>
<script>
var schemaBuilder = lf.schema.create('todo', 1);
schemaBuilder.createTable('Item').
addColumn('id', lf.Type.INTEGER).
addColumn('description', lf.Type.STRING).
addColumn('another_number', lf.Type.INTEGER).
addPrimaryKey(['id']).
addUnique('uq_description_and_another_number', ['description', 'another_number']);
var todoDb;
var item;
schemaBuilder.connect().then(function(db) {
todoDb = db;
item = db.getSchema().table('Item');
var row = item.createRow({
'id': 1,
'description': 'Get a cup of coffee',
'another_number': 123
});
return db.insertOrReplace().into(item).values([row]).exec();
}).then(function() {
return todoDb.select().from(item).exec();
}).then(function(results) {
results.forEach(function(row) {
console.log(row['description'] + row['another_number']);
document.body.textContent =
row['description'];
})
}).then(function() {
var row = item.createRow({
'id': 2,
'description': 'Get a cup of coffee',
'another_number': 123
});
console.log('201 error thrown here:');
return todoDb.insertOrReplace().into(item).values([row]).exec();
}).then(function() {
console.log('Does not reach here');
});
</script>
</body>
</html>
The exact error thrown is: Constraint error: (201) Duplicate keys are not allowed, index: Item.uq_description_and_another_number, key: ["Get a cup of coffee",123]
This can be avoided by just using the multi-key unique constraint as the primary key (and removing the unique constraint altogether), which is fine for what I'm doing. But this looks to be a bug with Lovefield
If this is confirmed a bug, I'd be happy to look into it. I'm very appreciative of this package
Either I am missing something, or this is working as intended.
The schema adds a constraint that basically says "no two rows should have the same description+another_number pair". Then you are adding a 2nd row (indicated by using a different primary key value) that has the same pair of description+another_number, clearly violating the constraint. Lovefield returns an error and also specifies exactly which constraint is violated.
Are you expecting that the 2nd row is supposed to replace the 1st one? If so, that's an incorrect expectation. The primary key specifies whether the "insert" or the "replace" part of insertOrReplace takes effect, and clearly here the "insert" one is in effect.
Are you expecting that the 2nd row is supposed to replace the 1st one?
Yep, this is what I expected. Understood on your explanation. It also looks like I overlooked this portion of the docs that says duplicates are determined by primary key
Behavior-wise this is a little confusing (Postgres for example would allow you to update the duplicate row that violates either a uniqueness constraint or primary key constraint with ON CONFLICT DO UPDATE), but the expected behavior is written right there in the Lovefield docs so no complaints on my end. And there are ways to work around this that aren't the end of the world
Apologies for missing it in the docs