0

I'm searching for a way to insert inside a MySQL "CREATE VIEW" a kind of autoincrement column.

I'm using an ORM which has some limitations, one of them is that each table (or view) MUST have only ONE unique identifier...

Anyway... starting from that point, my view groups results by two fields. I need a trick to add a third column, autoincremented in some way, inside this view.

Do you have any suggestion?

shA.t
  • 16,580
  • 5
  • 54
  • 111
rekam
  • 1,061
  • 3
  • 13
  • 31
  • Which ORM are you using or is that a secret? ORMs are not really designed to alter your database schema. – Tim Biegeleisen Apr 24 '15 at 09:42
  • See [this article](http://stackoverflow.com/questions/4829400/adding-id-auto-increment-after-table-exist) which recommends against adding an autoincrement column using a visual tool. – Tim Biegeleisen Apr 24 '15 at 09:46
  • It was a historical internal development. Now I need to cope with it :-) – rekam Apr 24 '15 at 09:56

2 Answers2

0

Ok, so I had to concatenate the two columns, to create a new column, really unique. It solve my problem, but it's not a solution :) !

rekam
  • 1,061
  • 3
  • 13
  • 31
0

I think your answer is adding a row-number to your VIEW like this:

SELECT (SELECT COUNT(*) sales
      FROM yourTable ti
      WHERE ti.PKColumn < t.PKColumn) as Rank, t.PKColumn, t.otherColumns
FROM yourTable t
;
shA.t
  • 16,580
  • 5
  • 54
  • 111
  • Except you can't do that in a view. specifically `ERROR 1351 (HY000): View's SELECT contains a variable or parameter` – pala_ Apr 26 '15 at 11:55
  • @pala_ Oh! I forgot that, thanks, I edit the answer and I hope I'm in a right way ;). – shA.t Apr 26 '15 at 12:02
  • 1
    That is a good answer, and a generic SQL technique which every coder should understand. That myNONsql cannot handle it is a separate matter. +1. – PerformanceDBA May 04 '15 at 11:39