Re: Help on integrity constraint issue

Did you mean Microsoft SQL Server would behave differently from Oracle in this situation? Since I don't have SQL Server, I cannot verify this.

BTW, the DDLs I used were for testing purpose and you don't want to write anything complicated than it should be for just simple testing, such as alter table.....etc.

"Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote in message news:<3C51CAA1.8A91C3EB_at_exesolutions.com>...
> Perhaps the Oracle architectural model has totally escaped you. Oracle is
> NOT SQL Server. There are no dirty reads. Session two hasn't a clue what
> session 1 is doing until session 1 commits.
>
> And please reconsider creating primary keys as you are doing it. All this
> does is produce objects with nonsense names. Use alter table in the
> future.
>
> Daniel Morgan
>
>
>
> Morris Luk wrote:
>
> > Hi:
> >
> > I'm using oracle8i on Solaris 2.7 and did the followings:
> >
> > create table test1_dept(dept_no number(5) primary key, dept_name
> > varchar2(30));
> >
> > create table test1_emp(emp_no number(5) primary key, dept_no
> > number(5), constraint dept_fk foreign key (dept_no) references
> > test1_dept );
> >
> > Then I did the following in one of the session (session 1):
> > insert into test1_dept values(100, 'test1');
> >
> > I did not commit or rollback
> >
> > Then I did the following in another session (session 2):
> > insert into test1_emp values(200, 100);
> >
> > Why did session 2 start hanging or waiting for session 1 to either
> > rollback or commit? Why didn't or can't oracle return integrity
> > constraint violation to session 2 instead of creating deadlock if
> > session 1 never commits or rollbacks? Returning an error seems more
> > natural because when you do select * from test1_dept on session 2, you
> > receive 0 rows returned anyway.
> >
> > Any ideas are welcome because I would like to learn more and want to
> > make sure the way Oracle is doing now makes sense.
> >
> > Thanks in advance.
Received on Mon Jan 28 2002 - 20:46:33 CST