0

I am trying to define a table where I want to define primary key has reverse of column. I was wondering if its possible in innodb ?

I am creating a table 

create table abc 
(
  `id` varchar(255) PRIMARY KEY , 
  `key` LONGTEXT NOT NULL,
  `value` LONGTEXT  NOT NULL ,
  `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
)

but instead I want PRIMARY KEY (reverse(id))

juergen d
  • 201,996
  • 37
  • 293
  • 362
Arjit
  • 565
  • 9
  • 27

1 Answers1

0

MySQL doesn't support indexes on functions at all. So it clearly can't have them for a primary key.

Nor does MySQL support materialized views nor indices on views.1

Depending on what you're trying to accomplish, most likely you should just store your key the other way around. If your application depends on having it reverse of what's stored, create a view for the application to interact with. Unfortunately, updates will be difficult as MySQL doesn't do INSTEAD OF triggers or triggers on views at all.

Community
  • 1
  • 1
derobert
  • 49,731
  • 15
  • 94
  • 124
  • The problem is way the system is locking mysql rows, when many thread try and accure locks on rows mysql is giving deadlocklooser exception. I think the problem is because my id is stored in sequence. so locking is happening around the "id" which is a primary key. I am just trying to change the primary key so that order of locking changes. – Arjit Nov 15 '12 at 18:51
  • @Arjit I suggest asking that question instead, then. – derobert Nov 15 '12 at 19:04