Many developers and DBAs who spent their lives on M$ SQL or Oracle turn up their noses at mysql, they refer to it with disdain. According to them the lack of foreign key support in mysql means it is not a proper RBMS and not standard compliant. Nothing can be further from the truth.Mysql has supported foreign keys for a long time now many people simply don't know of it or use it because foriegn keys cannot be used with MyISAM type of tables. You need to use <a target="_blank" rel="noopener noreferrer nofollow" href="http://www.innodb.com">InnoDB</a> type tables instead.Since many of the mysql tutorials on the web were written long long ago before InnoDB support was added, they rarely refer to the use of this table type or foreign keys. Fortunately it's a trival matter to convert your tables from MyISAM to InnoDB - provided that their data integrity has not been violated.ALTER TABLE tablename TYPE=InnoDB is all that is needed to perform the conversion. Now you are ready to add foreign keys - let's look at an example.<code>alter table userProfile add Foreign key (userId) REFERENCES users(userId);</code>This foreign key is useful as it can stop you entering a User Profile for a non existent user. Even more useful is to have a foreign key that results in an unwanted user's profile being deleted when you delete the entry in the user's table. You an also prevent a use from being deleted at all if another table refers to that user. Before we create such a foreign key, let's drop the old one we created. <code>alter table userProfile drop foreign key userId;</code>Beware; this drop foreign key statement will fail if there are inconsistencies in your data. The only remedy then is to create a new table do an INSERT .. SELECT query , drop the original table and rename the new table to use the old table name then take a deep breath.Before we were so rudely disturbed, we were discussing cascading deletes, here is how you should create your foreign key if you want to make use of this feature: <code>alter table userProfile add constraint foreign key (userId) references users(userId) on delete cascade;</code>

Foreign Keys and mysql

When you are inserting data into a table that has a generated id you often need to know what the generated id was. With mysql it's real easy, just use the <code>last_insert_id()</code> function. When you enter data into a mysql table that has an <code>auto_increment</code> field you can then make use of this method call retrieve the identifier that was generated. If you happen to be using PHP the job is even easier &nbsp;because you can retrieve this number <code>mysql_insert_id()</code> function of PHP without needing an extra query. At first glance it's counterpart in <a target="_blank" rel="noopener noreferrer nofollow" href="https://www.postgresql.org/">postgres</a> seems to be the <code>currval()</code> function. But appearences can be deceptive. You don't need to pass in any parameters at all to <code>last_insert_id()</code> or the <code>mysql_insert_id()</code> ; &nbsp;the database always returns the id associated with the last executed insert query for that connection. <code>currval</code> on the other hand takes the name of the related sequence as a parameter. So it seems the &nbsp;only way to determine the insert id in pg is by inspecting the database meta data - a cumbersome task at the best of times; it takes quite a bit of effort and querying to get the task done. On the other hand as a programmer you already know the relationship between the sequence and the column so it's a simple matter to create your own function that can return the id for you. In fact it's a mere 5 lines of code.<pre><code>CREATE FUNCTION add_user(char,char,integer) RETURNS BIGINT AS '
 INSERT INTO users(userName,userPassword,userStatus) VALUES($1,$2,$3);
 SELECT CURRVAL(''users_userid_seq'');
' LANGUAGE SQL;</code></pre> Instead of executing an&nbsp;Insert query as you might have done with mysql you do a SELECT when you want to add data to your table. Sounds counter intuitive? See this example:<code>SELECT add_user('raditha'.'123',1);</code> The select is actually using a stored function that does an insert! As with any other select you are presented with a resultset and it contains the value that was last generated by the sequence for the current connection to the data Interestingly when talking to an MS sql server with JDBC you always get back a result set even with an INSERT query if the insert results in the auto increment field been updated. You can do the same by carrying out a select on @@id Follow Up: see the <a target="_blank" rel="noopener noreferrer nofollow" href="../../postgres/index.html">mysql 2 pgsql migration guide</a>

Monthly Archives for 2004 08

Foreign Keys and mysql

last_insert_id or currval