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

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

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.

Views don't have primary keys or indexes - the mysql engine will use the indexes and keys defined on the base table(s).

A view is just a stored sub-query. The idea of a PK is irrelevant.

...unless you need an indexed view, in which case you need a Clustered Index on the view (but still not necessarily a Primary Key)

(although, I'm talking SQL Server... I'm not sure how this translates to MySQL)

It depends a little bit on what you attempt to do with the primary key. A composed key is crudely nothing more as a concatenation of the fields is composed of. So in order to ''create'' a primary compound key for a view you could concatenate the PKs from the three tables: concat_ws("+", table1.ID, table2.ID, table3.ID) as PK inside your view, which is in fact similarly to what most rdbms do when creating compound indexes or keys. This could be used to search in the view and you could even, depending on the storage engine, create an index on this construct improve performance.

you could use various methods to insert a unique ID to the view data, such as:

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

However this is not a primary key, it is not consistant and will change when the data changes.

What exactly do you need a key for?