Constraint bug?

Hi,

Either there is something I'm not seeing, or there is a bug in the way that primary key constraints are enforced. Any suggestions out there?

I have a table that looks like this:

	SQL> desc fa035
	 Name                            Null?    Type
	 ------------------------------- -------- ----
	 STATE_CODE                      NOT NULL VARCHAR2(2)
	 ACADYR                          NOT NULL NUMBER(4)
	 TRAVEL_ALLOWANCE                         NUMBER(8)
	

It has a primary key constraint on the combination of STATE_CODE and ACADYR.

	SQL> select * 
          2  from  sys.dba_cons_columns 
          3  where constraint_name='FA035_PK';

	OWNER                          CONSTRAINT_NAME
	------------------------------ ----------------------
	TABLE_NAME                     COLUMN_NAME              POSITION
	------------------------------ ---------------------- ----------
	OBERLIN                        FA035_PK
	FA035                          STATE_CODE                      1
	
	OBERLIN                        FA035_PK
	FA035                          ACADYR                          2

So far, so good. Now, I try the following insert:

	SQL> insert into fa035
	  2  select state_code,1994,travel_allowance from fa035;
	insert into fa035
	            *
	ERROR at line 1:
	ORA-00001: unique constraint (OBERLIN.FA035_PK) violated

Hmmm. There must already be some 1994 rows.

        SQL> select count(*) from fa035 where acadyr=1994;         

	  COUNT(*)
	----------
	         0

Nope. No 1994 rows. Well, let's try this manually:

        SQL> select * from fa035 where acadyr = 1993 and rownum=1;         

	ST     ACADYR TRAVEL_ALLOWANCE
	-- ---------- ----------------
	AK       1993             1494

	SQL> insert into fa035 values ('AK',1994,1494);

	1 row created.

Okay. Now this looks like a bug.

Jennifer R. Amon            PHONE: (216) 775-6987   
Houck Computing Center        FAX: (216) 775-8573    
Oberlin College          
Oberlin, OH 44074        INTERNET: bamon_at_ocvaxc.cc.oberlin.edu


_____________________________________________________________________

_____________________________________________________________________

Jennifer R. Amon            PHONE: (216) 775-6987   
Houck Computing Center        FAX: (216) 775-8573    
Oberlin College          
Oberlin, OH 44074        INTERNET: bamon_at_ocvaxc.cc.oberlin.edu


_____________________________________________________________________