Rails: Oracle constraint violation

Question

I'm doing maintenance work on a Rails site that I inherited; it's driven by an Oracle database, and I've got access to both development and production installations of the site (each with its own Oracle DB). I'm running into an Oracle error when trying to insert data on the production site, but not the dev site:

ActiveRecord::StatementInvalid (OCIError: ORA-00001: unique constraint (DATABASE_NAME.PK_REGISTRATION_OWNERSHIP) violated: INSERT INTO registration_ownerships (updated_at, company_ownership_id, created_by, updated_by, registration_id, created_at) VALUES ('2006-05-04 16:30:47', 3, NULL, NULL, 2920, '2006-05-04 16:30:47')):
/usr/local/lib/ruby/gems/1.8/gems/activerecord-oracle-adapter-1.0.0.9250/lib/active_record/connection_adapters/oracle_adapter.rb:221:in `execute'
app/controllers/vendors_controller.rb:94:in `create'

As far as I can tell (I'm using Navicat as an Oracle client), the DB schema for the dev site is identical to that of the live site. I'm not an Oracle expert; can anyone shed light on why I'd be getting the error in one installation and not the other?

Incidentally, both dev and production registration_ownerships tables are populated with lots of data, including duplicate entries for country_ownership_id (driven by index PK_REGISTRATION_OWNERSHIP). Please let me know if you need more information to troubleshoot. I'm sorry I haven't given more already, but I just wasn't sure which details would be helpful.

UPDATE: I've tried dropping the constraint on the production server but it had no effect; I didn't want to drop the index as well because I'm not sure what the consequences might be and I don't want to make production less stable than it already is.

Curiously, I tried executing by hand the SQL that was throwing an error, and Oracle accepted the insert statement (though I had to wrap the dates in to_date() calls with string literals to get around an "ORA-01861: literal does not match format string" error). What might be going on here?

Answer 1

Based on the name of the constraint, PK_REGISTRATION_OWNERSHIP, you have a primary key violation. If these databases aren't maintaining this data in lockstep, something/someone has already inserted a record into the registration_ownerships table in your production database with company_ownership_id=2 & registration_id=2920. (I'm guessing at the specifics based on the names)

If this particular set of values needs to exist in the production database,

1) check that what's already there isn't what you're trying to insert. if it is, you're done.

2) If you need to insert your sample data as-is, you need to modify the existing data & re-insert it (and all the dependent/refering records), then you can insert your values.

Answer 2

If you query the table and find no matching rows, then one of the following may be the cause:

1. The session is trying to insert the row twice.
2. Another session has inserted the row, but hasn't committed yet.

Also, check that the state of the unique constraint is the same between dev and prod. Perhaps the one on dev is marked as not validated - check that the index exists on dev and is a unique index (note: in Oracle it is possible to have a unique constraint validated by a non-unique index).