首先要确定约束在哪一字段上,使用sql:select * from user_constraints where table_name='TABLENAME',(这里要注意TABELNAME必须是大写),这样数据表所有的约束都会呈现出来,找到报错中SYS_C0053416约束条件,若本sql确能查询出该SYS_C0053416,删除该check约束问题也就可以解决,查看search_condition字段可以确定该约束使用在哪个字段上。
删除check语句:ALTER TABLE TABLENAME DROP CONSTRAINT SYS_C0053416
查询外键
https://blog.csdn.net/qq441568267/article/details/73963817
Oracle中查询该表中的主键被哪些表引用的SQL语句:
[sql] view plain copy
- select c.TABLE_NAME
- from all_constraints p, all_constraints c
- where p.table_name = '表名'
- and p.OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')
- and c.OWNER=SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')
- and c.constraint_type = 'R'
- and p.CONSTRAINT_NAME = c.R_CONSTRAINT_NAME
oracle中查询该表中的外键来源于哪些表的SQL语句:
[sql] view plain copy
- select cl.table_name
- from user_cons_columns cl
- left join user_constraints c
- on cl.constraint_name = c.r_constraint_name
- where c.constraint_type = 'R'
- and c.table_name = '表名'
PS:查询owner的SQL语句: select SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') from dual