mysql Foreign key constraint is incorrectly formed error

I ran into this same problem with HeidiSQL. The error you receive is very cryptic. My problem ended up being that the foreign key column and the referencing column were not of the same type or length. The foreign key column was SMALLINT(5) UNSIGNED and the referenced column was INT(10) UNSIGNED. Once I made them both the same …

Read more

SQL – HAVING vs. WHERE

WHERE clause introduces a condition on individual rows; HAVING clause introduces a condition on aggregations, i.e. results of selection where a single result, such as count, average, min, max, or sum, has been produced from multiple rows. Your query calls for a second kind of condition (i.e. a condition on an aggregation) hence HAVING works correctly. As a rule of thumb, use WHERE before GROUP BY and HAVING after GROUP BY. …

Read more

What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?

The @@identity function returns the last identity created in the same session. The scope_identity() function returns the last identity created in the same session and the same scope. The ident_current(name) returns the last identity created for a specific table or view in any session. The identity() function is not used to get an identity, it’s used to create an identity in a select…into query. The …

Read more

Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio

You would mostly be using COUNT to summarize over a UID. Therefore COUNT([uid]) will produce the warning: Warning: Null value is eliminated by an aggregate or other SET operation. whilst being used with a left join, where the counted object does not exist. Using COUNT(*) in this case would also render incorrect results, as you would then be counting the …

Read more

No more data to read from socket error

For errors like this you should involve oracle support. Unfortunately you do not mention what oracle release you are using. The error can be related to optimizer bind peeking. Depending on the oracle version different workarounds apply. You have two ways to address this: upgrade to 11.2 set oracle parameter _optim_peek_user_binds = false Of course underscore parameters …

Read more