Example
I try to run a script that inserts some data into a
table. Unfortunately, the script violates a
check constraint:
tq84@thedb> @fill-sales-hist
insert into tq84.sales_hist
*
ERROR at line 1:
ORA-02290: check constraint (TQ84.SYS_C001013371) violated
The name of the violated constraint is quite undecipherable: TQ84.SYS_C001013371.
So, what does this contraint refer to? cons.sql might be helpful:
dwh_dm_riskfinance@THEDB> @cons TQ84.SYS_C001013371
Owner: TQ84
constraint name SYS_C001013371
type: C
Check constraint
Condition: valid_from < valid_to
Table: SALES_HIST
Columns: VALID_TO
VALID_FROM
cons.sql
declare
-- Show information about a constraint.
--
-- Parameter: name of constraint
--
-- Currently: only PK, FK, unique and check constraints supported.
--- NOT NULL constraints not yet supported.
r_constraint dba_constraints%rowtype;
r_constraint_pk dba_constraints%rowtype;
r_index dba_indexes%rowtype;
owner_ varchar2(30);
constraint_name_ varchar2(30);
dot_pos number;
begin
dbms_output.new_line;
dot_pos := instr('&1', '.');
if dot_pos > 0 then
owner_ := upper(substr('&1', 1, dot_pos-1));
constraint_name_ := upper(substr('&1', dot_pos + 1 ));
else
constraint_name_ := upper('&1');
end if;
dbms_output.put_line('Owner: ' || owner_);
dbms_output.put_line('constraint name ' || constraint_name_ );
begin
select * into r_constraint from dba_constraints
where
constraint_name = constraint_name_ and
nvl(owner_, owner) = owner;
dbms_output.put_line('type: ' || r_constraint.constraint_type);
exception when no_data_found then
-- Maybe it's a unique constraint in an index-disguise...
select * into r_index from dba_indexes
where
index_name = constraint_name_ and
nvl(owner_, owner) = owner and
uniqueness ='UNIQUE';
dbms_output.put_line(' Unique key constraint [defined as index]');
dbms_output.new_line;
dbms_output.put_line(' Table: ' || r_index.table_name);
dbms_output.new_line;
for cols in (
select column_name
from dba_ind_columns
where
index_name = constraint_name_ and
nvl(owner_, index_owner) = index_owner
order by column_position
) loop
dbms_output.put_line(' ' || cols.column_name);
end loop;
end;
if r_constraint.constraint_type = 'R' then -- {
select * into r_constraint_pk from dba_constraints
where
constraint_name = r_constraint.r_constraint_name and
owner = r_constraint.r_owner;
dbms_output.put_line(' Foreign Key constraint');
dbms_output.new_line;
dbms_output.put_line(' ' || rpad(r_constraint.owner , 30) || ' ' || r_constraint_pk.owner);
dbms_output.put_line(' ' || rpad(r_constraint.table_name, 30) || ' -> ' || r_constraint_pk.table_name);
dbms_output.put_line(' ' || rpad('-', 30, '-') || ' ' || rpad('-', 30, '-'));
for cols in (
select
fk_col.column_name column_name_fk,
pk_col.column_name column_name_pk
from
dba_cons_columns fk_col,
dba_cons_columns pk_col
where
fk_col.constraint_name = r_constraint .constraint_name and
pk_col.constraint_name = r_constraint_pk.constraint_name and
fk_col.position = pk_col.position
order by
fk_col.position
) loop
dbms_output.put_line(' ' || rpad(cols.column_name_fk, 30) || ' ' || cols.column_name_pk);
end loop;
-- }
elsif r_constraint.constraint_type in ('P', 'U') then -- {
if r_constraint.constraint_type = 'P' then
dbms_output.put_line(' Primary Key constraint');
else
dbms_output.put_line(' Unique key constraint');
end if;
dbms_output.new_line;
dbms_output.put_line(' Table: ' || r_constraint.table_name);
dbms_output.new_line;
for cols in (
select column_name
from dba_cons_columns
where
constraint_name = r_constraint.constraint_name and
nvl(owner_, owner) = owner
order by position
) loop
dbms_output.put_line(' ' || cols.column_name);
end loop;
-- }
elsif r_constraint.constraint_type = 'C' then -- {
dbms_output.put_line(' Check constraint');
dbms_output.new_line;
dbms_output.put_line(' Condition: ' || r_constraint.search_condition);
dbms_output.new_line;
dbms_output.put_line(' Table: ' || r_constraint.table_name);
dbms_output.put (' Columns: ');
for cols in (
select /* position, */ -- Seems to be null for check constraints...
row_number() over (order by position) row_,
column_name
from dba_cons_columns
where
constraint_name = r_constraint.constraint_name and
owner = r_constraint.owner
order by position
) loop
if cols.row_ = 1 then
dbms_output.put_line(cols.column_name);
else
dbms_output.put_line(' ' || cols.column_name);
end if;
end loop;
dbms_output.new_line;
end if; -- }
end;
/