how to quickly find out constraint which prevent me from dropping that table.

marrowyung
marrowyung used Ask the Experts™
on
hi,

As I am testing Oracle to MariaDB converison and when I migrate to MariaDB some table may fail to create when there are foreign key prevent me from creating tables .

from your point of view, how can I check quickly which MySQL constraint I have to remove before dropping that table and recreate again .

it is a very time consuming task if I have to check tables by tables .. ( 3xxx tables!)

or I should look at Oracle constraint instead to give hints  ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you are migrating from one DBMS to another, I think, you have the same table structure. To check constraints on Oracle side, you can use the following query:

select f.owner from_owner, f.table_name from_table_name, t.owner to_owner, t.table_name to_table_name
  from all_constraints f
  join all_constraints t on f.r_constraint_name = t.constraint_name
 where f.owner = '<your schemta>'
   and f.table_name = '<your table name>'

Open in new window


This query returns a list of tables. Put the mane of the table instead of "<your table name>" and you will get a list of all referenced tables.
Geert GOracle dba
Top Expert 2009

Commented:
for oracle
you can't drop a table when there are locks from other users on it

time consuming ?
what else would you be doing otherwise ?
at least you have a project to keep your mind on :)
marrowyungSenior Technical architecture (Data)

Author

Commented:
Dmitry Kurashkin,

"I think, you have the same table structure"

it will not be the same structure, always not as it is diff DB.


"To check constraints on Oracle side, you can use the following query:
"



as I said I need to check MariaDB/MySQL constraint and see which one I can drop so that I can drop the tables I want to recreate.


Geert G.


"you can't drop a table when there are locks from other users on it"

it is not my case, both DB has no one access is it is a UAT PoC DB.

also my question is about drop MySQL/MariaDB constraint.

problem is we are trying to test the process of migrating tables from Oracle to MariaDB, but if we find problems we need to drop migrated table in MariaDB and start again.

this kind of constraint make me can't drop it  IN MARIADB side.
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi,

If you are using MariaDB version 10.2.22 and newer you can list the constraints like this
select constraint_schema as table_schema,
       table_name,
       constraint_name,
       check_clause as definition
from information_schema.check_constraints
order by table_schema,
         table_name;

Open in new window


before version 10.2.22 it is done like this

select table_schema,
       table_name,
       constraint_name
from information_schema.table_constraints
where constraint_type = 'CHECK'
order by table_schema,
         table_name;

Open in new window


Other usefull "catalog/system" tables can be found here

Regards,
    Tomas Helgi
marrowyungSenior Technical architecture (Data)

Author

Commented:
let me check, I might have one single query to check this but I need to verify, double check it tomororw.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial