Generate column with unique ID in MySQL view

I've often found the need to create views which do not have a single-column primary key, but we must have this primary key column in order to publish a data store which connects to such a view. I suspect that other practitioners have encountered the same issue, so I thought I'd write a quick note about the solutions that I have found. Perhaps some of this information could be included in a KB article.

In SQL Server and Oracle, the ROW_NUMBER() function meets our needs. Your view definition might look something like this:

SELECT ISNULL(CAST(ROW_NUMBER() OVER (ORDER BY your_ordering_column_name) AS int), 0) AS id, [other column names]
FROM your_tables

However, in MySQL we have to be more creative. There is a way to generate row numbers by making use of a variable inside the query:

SELECT t.*, @rownum := @rownum + 1 AS rank
FROM YOUR_TABLE t, (SELECT @rownum := 0) r

However, if you try to cre...

OriginalPostID-272952

  • ...ate a view using this method, you'll get an error message telling you that variables are not allowed in view definitions. To get around this restriction, we can create a function that initializes a session variable and increments it each time the function is called. If you’re using the MySQL database included with cloud installations of Appian, this query will create the function for you:

    CREATE DEFINER=`appian`@`%` FUNCTION `func_inc_var_session`() RETURNS INT(11) COMMENT 'Used to generate unique row IDs in views' NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER begin SET @var := IFNULL(@var,0) + 1; return @var; end

    If you’re on your own MySQL instance, you may need to replace `appian`@`%` with `root`@`localhost`.

    Then, you can create a view definition as follows:

    SELECT func_inc_var_session() AS id, [other column names]
    FROM your_tables

    The id column in thsi view will contain a unique ID that Appian can treat as a primary key, and this will allow you to publish ...
  • ...a data store that connects to the view. Credit for this solution goes to the users who posted on this stackoverflow entry: stackoverflow.com/.../create-a-view-with-column-num-rows-mysql
  • @johns222 few days ago i too had the same requirement, where my view was joining 6 tables and was returning duplicates for each columns value, so if i treat any one of the column as primary key while mapping it with cdt, and if i query this CDT and use the response in a GridField, then it used to give error because the Primary key value needs to be unique, so i was having the same requirement where a auto sequence has to generate for each row, when i used this query snippet(suggested in Stackoverflow) in a Normal SQL Query, it was working fine but when i used the same in View, it was giving error.

    @Johns is this solution working for you? if yes, please let me know the steps (or the query for a single column in that view which will hold the autosequence) which you have followed to enable autosequence in view for each row
  • The other solution is to concatenate the PKs from all the tables you are selecting together with a : or - or whatever as viewID to make a unique field. Example: 1:2:2:76
  • @christineh i tried that, initially it was working properly because of having unique PKs combination, but after having huge number of rows getting return from View, i got the duplicate PK again (which was rare, but got that) and again i came back into the same situation where i was earlier. So i tried the solution mention by @Johns but while creating the view it was giving Syntax error but while executing the same in Normal Query was working fine and returning sequence number for each row.
  • Interesting. If you were getting duplicates, I would think that you either did not separate; cause 1:11 is diff from 11:1; use the pk from every table in the joins, or have a unique field from each table. I would look into those. Thanks
  • @aloks Yes, the approach I described is currently working for me. Make sure that your func_inc_var_session() function includes the null check. Christine's approach would probably work better in many cases, and I might try that next time I have this issue.
  • @johns222 @christineh thanks for your valuable inputs, yes the solution mention by @Christineh even worked for me earlier also up-to much extent (apart from some odd cases), however that should work, so if i get such requirement in future, then i will try to implement both of yours suggestions once again

    Thanks.

 Discussion posts and replies are publicly visible