Oracle CHECK constraints with SYS_ name not being generated about ora2pg HOT 6 CLOSED

Hi,

Yes this is right, system auto-generated constraint are not exported, just because they are also auto-generated with PostgreSQL. Is there's any constraint that you think must be exported and that are not?

Best regards,

from ora2pg.

Well, problem is that those CHECKS where created by the user in Oracle, but as they assigned no name, Oracle created a name. For example, creating a table in Oracle this way

create table myTable (
  record_id number(10) not null,
  is_active char(1) check (is_active in ('Y', 'N'))
);

with or2pg I get

CREATE TABLE mytable (
    record_id numeric(10) NOT NULL,
    is_active char(1)
);

and the CHECK constraint does not show up :(

Best regards.

from ora2pg.

That's right this kind constraint was not exported, it is not fixed in commit c4df0c4. There's still some work to do to export those constraints into the create statement instead of ALTER command.

Thanks for your report.

from ora2pg.

Thanks to you for your attention to this one, and glad to being helping :)

Regards.

from ora2pg.

If it is of any help, I've used this SQL to get all the CHECK constraints, even the ones with SYS_ on the name:

drop table const_check;
create table const_check as
  select constraint_name, table_name, to_lob(search_condition) as condition
  from user_constraints
  where constraint_type = 'C';
select * from const_check where condition not like '%NULL';
select 'alter table ' || table_name ||' add constraint '|| 
  constraint_name ||' CHECK('||condition||');'
from const_check
where table_name not like 'BIN%'
  and condition not like '%NULL' and constraint_name like 'SYS_%';

from ora2pg.

Ora2Pg use this one:

SELECT CONSTRAINT_NAME, R_CONSTRAINT_NAME, SEARCH_CONDITION, DELETE_RULE, DEFERRABLE, DEFERRED, R_OWNER, TABLE_NAME ,OWNER
FROM ALL_CONSTRAINTS WHERE CONSTRAINT_TYPE='C' AND STATUS='ENABLED'

the previous bug comes from the additional condition: AND GENERATED != 'GENERATED NAME', this is what I'd removed from the SQL query.

from ora2pg.