Search code examples
sqloracle-databaseunique-constraintora-00001

1->1/1->N relationship in Oracle?

I have 2 tables

T_Foo
foo_id
fooHeader

T_FooBodys
foo_id
foobody

foo_id are primary key for both of their respective tables. In second table, foo_id is foreign key to first table. I generate the PK for both table values using a sequence and try to make insert - once into FooHeader and twice into FooBody.

MY code crashes on the second insert into t_FooBodys in the loop from error "ORA-00001: unique constraint (USERID.FooBodys_PK) violated"

So I have 2 questions:

1) What is the fundamental difference between MSSQL Server and Oracle here? This worked fine in SQL Server! I had one-to-one/many relationships all the time in there

2) What is the simplest way to fix this besides adding another key and essentially ending the shared primary key concept?

Thanks much

Solution

  • foo_id are primary key for both of their respective tables

    You cannot have duplicate entries for a PK with either MS SQL Server or Oracle, so you have made a mistake somewhere. You may have set up the FK relationship in SQL Server, but there is no way that you set foo_id in T_FooBodys as a PK and it still allowed duplicate entries.