How can I have a primary key in a VIEW (a key that doesn't depend on source tables)

Question

I'm creating a VIEW out of 3 TABLES. Each TABLE has it's primary key. However, in the VIEW in order to have a primary key, I'll have to use a composite primary key (combination of primary keys of 3 TABLES).

I would however like to add a column in the VIEW as a primary key that is created just for the purpose of the VIEW. As part of the VIEW definition, it should be UNIQUE(autoincrement since it would be mostly an INT). How can I achieve this?

I am using MySQL 5.1

Answer 1

The highest voted answer using

SELECT @rownum:=@rownum+1 as id, mytable.*
FROM (SELECT @rownum:=0) r, mytable;

Is incorrect - you cannot create a view in mysql that uses a sub-select in the FROM. You're only option is to treat a set of columns as a composite key.