Foreign Key reference in mysql innodb

Question

Simply put: I have a table with 2 columns: one is username and other is nickname. I have another table where I have 2 columns: one is username and other is countNicknames.

I want countNicknames to contain the number of nicknames each user has (and whenever I insert a new nickname to the table1, the value under table2.countNicknames will automatically update.

Can you please write down how to construct the second table to reference the first one?

Answer 1

Why not just count when you need the value?

Answer 2

Well, @Lasse's suggestion is better, but ... there is two other another options...

1. Does MySql have triggers? If it does, then you would add an Insert, Update, Delete trigger on the first table that updates (or inserts or deletes as necessary) the second table's CountNickNames attribute every time a record is inserted, Updated or deleted from the first table...

   Create Trigger NickNameCountTrig On NickNameCountTable For Insert, Update, Delete As Update nct Set CountNickNames = (Select Count(*) From FirstTable Where Name = nct.Name) From NickNameCountTable nct Where Name In (Select Distinct Name from inserted Union Select Distinct Name From deleted) -- ----------------------------------------------- Insert NickNameCountTable (Name, CountNickNames) Select name, count(*) from FirstTable ft Where Not Exists (Select * From NickNameCountTable Where Name = ft.Name) -- ------ This is optional ----------------------- Delete NickNameCountTable Where CountNickNames = 0

2. Does MySql have indexed views (or some equivilent)? apparently not - so never mind this option ....

Answer 3

I want countNicknames to contain the number of nicknames each user has (and whenever I insert a new nickname to the table1, the value under table2.countNicknames will automatically update.

This is effectively what a view will do.

CREATE OR REPLACE VIEW user_nickname_count AS
    SELECT t.username,
           COUNT(*) 'countNicknames'
      FROM TABLE t
  GROUP BY t.username

A view looks like a table, so you cah join to it if needed. And the only overhead is that it effectively is just a SELECT query being run - the values are calculated on demand, rather than having to setup triggers.

For more info on views, see the documentation.