0

I have a table with some data. I use type POINT because sometimes I need to select the data based on this parameter.

I need to add a constraint that will disallow the insert of the same pair of values.

I receive this error when I try to simply add a unique index.

Error 3728: Spatial indexes can't be primary or unique indexes

Point can have negative values. Thus, probably cantor pairing function is not the case for it to be stored as LONG/INT.

Is there any other way to implement that check? I know I can use varchar for that but I'd like to use some data type like integer/decimal for that. Or maybe some kind of trigger that checks coming value is unique?

Either way, I want database-level control for that.

xercool
  • 883
  • 1
  • 8
  • 24

1 Answers1

0

Solved by using conditional insert: MySQL Conditional Insert

insert into `tbl` (`position`) select * from (select point(%f, %f) `p`) `tmp` where not exists ( select * from `tbl` `t` where `t`.`position` = `tmp`.`p` )

Please let me know if someone knows any other way.

xercool
  • 883
  • 1
  • 8
  • 24