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

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  ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dmitry KurashkinDeveloperCommented:
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 dbaCommented:
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 EngineerCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.