My question is very similar to this one, but for adaptive server enterprise. I use BCP to load data into the database. During bulk import no foreign key constraint checks are performed.
What is the best way to check foreign key constraints after the data has been imported?
I'm not aware of any commands in Sybase ASE similar to MSSQL's alter table/check check constraint.
A few ideas on how to force a check of the FK constraint:
bcp into a staging table, then insert to the target table by selecting from the staging table; this would kick out an error but not necessarily tell you which row(s) failed the FK check (unless you choose to insert/select 1 row at a time)
update the FK column(s) with the current value(s); ASE isn't smart enough to know that the before/after values are the same so it'll run the FK check; again, this will generate an error but not tell you which rows failed the FK check; it's also not very efficient if it does go through and update a bunch of rows (and associated indexes); something like:
update child_table set fk_col = fk_col
where ... for-the-child-rows-just-inserted ...
not exists query to find the rows that fail the FK check, something like:
select * from child c
where not exists(select 1 from parent p where p.pk = c.fk)
and ... for-the-child-rows-just-inserted ...