Generate column with unique ID in MySQL view

Question

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...

John Stretton · Answer

...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 ...

Generate column with unique ID in MySQL view

Top Replies