primary_key problem in MySql view

Question

I have 3 tables. and I want to create a view. There is no problem about generating SQL query and creating a view.

But when I want to use this view in my web app, it says me (LIKE THIS):

Your view's column doesn't have a primary key

So I want to set a PK to a column in my view.

Please help. Thanks.

• Colin 't Hart: I'm guessing your web app framework is trying to do an update on the view?
• Mirjalal: yes. my web app framework requires a pk for any column in view.

Answer 1

You can't have a PRIMARY KEY on a VIEW. See here, here . You have to put PRIMARY KEYs and INDEXes on the underlying tables. A VIEW is a stored query - you can't have KEYs on a query.

• Mirjalal: first thanks for replying. and the second: okay I understand. and the view has store the column which it has a primary key in its own table.
• Vérace: I believe that you've understood - the view is over the columns of the base tables and it is they that would have the INDEXes and PRIMARY KEYs.
• Mirjalal: I understood your answer like this: "there is no need to set a primary key for the if any column of this view has a PK." my view has a column named as 'iduser'. this column is also in USER table & it has primary key in this table. So should I set a primary in view for this or another column? If your answer "No", but I have a problem about this. My system is not working properly. It says me error like this: "Your view doesn't have a primary key".
• Vérace: Edit your question and put in your table definitions and your view defintion. I'm puzzled! p.s. English not an issue :-)
• Mirjalal: and sorry for bad english

Answer 2

Note that Vérace's answer is specific to the MySQL product, and not general for SQL Views. For instance, SQL Server allows the definition of Indexed Views which can have both clustered and non-clustered indices.

These are of course just aggregate tables such as one could create and maintain oneself, but in this case the DBMS performs all that bookkeeping automatically. All the normal benefits of using declarative structures in place of functional programming naturally ensue.