Inserting information from other tables

Question

I am trying to insert the values into the new table here. However, I get (ORA-00001: unique constraint (MYSCHEMA.SYS_C007106) violated) which it states the condo_id as unique. Even though I am not inserting the condo_id into the new table. How can I resolve this?

insert into large_condo
    select location_num, unit_num, bdrms, baths, condo_fee, owner_num
      from condo_unit
     where sqr_ft > 1500;

Extra information:

The condo_id is the trigger. How can I bypass the trigger?

It would be sensible if you showed the schema of the large_condo table because without that, we're having to guess what your problem is. The outline schema should include all the columns and their types, plus any constraints (especially unique or primary key constraints), and any triggers on the table. We probably don't need the schema of the condo_unit table. Is there any danger that large_condo is a view on the condo_unit table? (Pretty unlikely, but funnier things have been known!) — Jonathan Leffler
If you are using an insert statement that doesn't list the columns, that implies that you must be providing a value for every column (otherwise, Oracle would have no idea which subset of columns you were trying to populate). If you're actually running the insert statement you posted here, you must be inserting a value for condo_id. I don't know which column that is but give that primary keys are generally the first column of the table, I'd guess that you are trying to use location_num as the condo_id which probably doesn't make sense and isn't unique. — Justin Cave

Dave Costa Dave Costa · Accepted Answer · 2016-09-25T00:15:48

Two possibilities I can think of:

A default value is set for the condo_id column. You should be able to see this by querying USER_TAB_COLUMNS.
There is a trigger that sets a value for the condo_id column. You can look for the trigger name in USER_TRIGGERS then view the trigger source in USER_SOURCE.

Inserting information from other tables

3 Answers