Will a key in sql still stay a key in a view

Question

Let's say I have a mysql table called FISH with fields A, B and C.

I run SELECT * FROM FISH. This gets me a view with all fields. So, if A was a key in the original table, is it also a key in the view? Meaning, if I have a table FISH2, and I ran

 SELECT * FROM (SELECT * FROM FISH) D, (SELECT * FROM FISH2) E WHERE D.A = E.A

Will the relevant fields still be keys?

Now, let's take this 1 step further. If I run

SELECT * FROM (SELECT CONCAT(A,B) AS DUCK, C FROM FISH) D, (SELECT CONCAT(A,B) AS DUCK2, C FROM FISH2) E WHERE D.DUCK = E.DUCK2

If A and B were keys in the original tables, will their concatenation also be a key?

Thanks :)

Answer 1

If A is a key in fish, any projection on fish only, will produce a resultset where A is still unique.

A join between table fish and any table with 1:1 relation (such as fish_type) will produce a result set where A is unique.

A join with another table that has 1:M or M:M relation from fish (such as fish_beits) will NOT produce a result where A is unique, unless you provide a filter predicate on the "other" side (such as bait='Dynamite').

SELECT * FROM (SELECT * FROM FISH) D, (SELECT * FROM FISH2) E WHERE D.A = E.A

...is logically equivalent to the following statement, and most databases (including MySQL) will perform the transformatiion:

select *
  from fish
  join fish2 on(fish.a = fish2.a)

Whether A is still unique in the resultset depends on the key of fish2 and their relation (see above).

Concatenation does not preserve uniqueness. Consider the following case:

concat("10", "10") => "1010"
concat("101", "0") => "1010"

Therefore, your final query...

SELECT * 
  FROM (SELECT CONCAT(A,B) AS DUCK, C FROM FISH) D
      ,(SELECT CONCAT(A,B) AS DUCK2, C FROM FISH2) E 
 WHERE D.DUCK = E.DUCK2

...won't (necessarily) produce the same result as

select * 
  from fish 
  join fish2 on(
       fish.a = fish2.a
   and fish.b = fish2.b
  )

I wrote necessarily because the collisions depend on the actual values. I hunted down a bug about some time ago where the root cause was exactly this. The code had worked for several years before the bug manifested itself.