Hints and tips on database

These are some helpful hints on various topics.

 

MSQQL count(columnt) doesn't support all data types

In MSSQL queries like

select count(col1) from table1

will fail if column type is uniqueidentifier, text, image, ntext. Those types are not supported also in aggregate functions and subqueries.


Oracle dual table or how to migrate Mysql tableless queries

If you are familiar with Mysql, you probably use sometimes tableless queries like this:
 
SELECT 12 * (1 + 10);
 
Well, if you're migrating to Oracle you'll be surprised to find that those queries are not allowed here. The solution? Use the dual table like this:
 
SELECT 12 * (1 + 10) from dual
 
Dual is special table that is automatically created. Its schema has only one column called dummy, and is populated with just one row with value 'X'. In short - this makes it very useful for our case.

Creating read-only table in Oracle


A handy way to prevent any data modifications (update, delete, insert) in certain table in Oracle database is to create trigger that always fails:
 
create or replace no_dml
before insert or update or delete
on my_table
begin
    raise_application_error (-20000, 'my_table is read-only!');
end;

Using this approach you can select which tipe of data modifications to prevent. For example you can create table that allows only inserts without modifying existing data.

 


Find constraint details by its name in Oracle


When creating Oracle database schema it's useful to put names on all constraints to easily identify constraint violations later. But what happens if a constraint doesn't have name preset? Basicly you get a similar error:
 
ORA-00001: unique constraint (MYUSER.SYS_C00009843) violated
 
Now to identify which is the constraint and on which columns, run the following query:
 
select cons.table_name, cons.constraint_name, cons.constraint_type,
    cols.table_name, cols.column_name, cols.position
from user_constraints cons 
    inner join user_cons_columns cols 
        on cons.owner = cols.owner and cons.constraint_name = cols.constraint_name 
where cons.constraint_name = 'SYS_C00009843'