Запрос


:ID_SUBJECT(FLOAT,IN)=1624664
:PHONE(VARCHAR[10],IN)='8342474312'
:PHONE_NAME(VARCHAR[2],IN)='56'
:DATE_BEGIN(IN)=<NULL>
:OLD_ID_SUBJECT(FLOAT,IN)=1624664

Дает ошибку
Error: ORA-00001: unique constraint (ISS.UK_SP_PH_SUB_BEGIN) violated

Таблица



Проверяю:


1 запись выбрана.

Я её же и апдейчу,
почему не дает?

> SP.PHONE = '8342474312'
> and
> SP.ID_SUBJECT = 1624664
>
> 1 запись выбрана.
>
> Я её же и апдейчу,

т.е. ни SP.PHONE, ни SP.ID_SUBJECT не меняется.
А уже только они определяют в таблице только одну запись

> :DATE_BEGIN(IN)=<NULL>
А если не нулл попробовать?

UPDATE SUBJECT_PHONE
SET
-- ID_SUBJECT = 1624664, -- и так тоже
PHONE = '8342474312',
PHONE_NAME = 'Некий',
DATE_BEGIN = to_date('01.03.2011')
WHERE
ID_SUBJECT = 1624664

тоже самое..Error: ORA-00001: unique constraint (ISS.UK_SP_PH_SUB_BEGIN) violated

полное определение таблицы такое

CREATE TABLE "ISS"."SUBJECT_PHONE"
  ( "ID_SUBJECT" NUMBER,
"PHONE" VARCHAR2(11),
"PHONE_NAME" VARCHAR2(1000),
"DATE_BEGIN" DATE,
"DATE_END" DATE,
"ORDER_SORT" NUMBER,
"ALTERNATE_ADDRESS" VARCHAR2(1000),
"UPDATE_STATUS" NUMBER,
"IN_09" VARCHAR2(1) DEFAULT 'Y' NOT NULL ENABLE,
"IS_FREE" VARCHAR2(1) DEFAULT 'N' NOT NULL ENABLE,
"IS_PARALLEL" VARCHAR2(1) DEFAULT 'N' NOT NULL ENABLE,
"PAID_INFORM" VARCHAR2(1) DEFAULT 'Y' NOT NULL ENABLE,
"PCOMMENT" VARCHAR2(200),
"ADDRESS" VARCHAR2(500),
 CONSTRAINT "UK_SP_PH_SUB_BEGIN" UNIQUE ("ID_SUBJECT", "PHONE", "DATE_BEGIN")
 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "SPRAVKA"  ENABLE,
 SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,
 SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS,
 SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS,
 CONSTRAINT "FK_SP_ID_SUBJECT" FOREIGN KEY ("ID_SUBJECT")
  REFERENCES "ISS"."SUBJECT" ("ID_SUBJECT") ENABLE
  ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
 STORAGE(INITIAL 65536 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "SPRAVKA" ;

 CREATE INDEX "ISS"."IX_SP_ID_SUBJECT" ON "ISS"."SUBJECT_PHONE" ("ID_SUBJECT")
 PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "SPRAVKA" ;

 CREATE INDEX "ISS"."IX_SP_PHONE" ON "ISS"."SUBJECT_PHONE" ("PHONE")
 PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "SPRAVKA" ;

 CREATE UNIQUE INDEX "ISS"."UK_SP_PH_SUB_BEGIN" ON "ISS"."SUBJECT_PHONE" ("ID_SUBJECT", "PHONE", "DATE_BEGIN")
 PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "SPRAVKA" ;

 ALTER TABLE "ISS"."SUBJECT_PHONE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

 ALTER TABLE "ISS"."SUBJECT_PHONE" ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

 ALTER TABLE "ISS"."SUBJECT_PHONE" ADD SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS;

 ALTER TABLE "ISS"."SUBJECT_PHONE" MODIFY ("IN_09" NOT NULL ENABLE);

 ALTER TABLE "ISS"."SUBJECT_PHONE" MODIFY ("IS_FREE" NOT NULL ENABLE);

 ALTER TABLE "ISS"."SUBJECT_PHONE" MODIFY ("IS_PARALLEL" NOT NULL ENABLE);

 ALTER TABLE "ISS"."SUBJECT_PHONE" MODIFY ("PAID_INFORM" NOT NULL ENABLE);

 ALTER TABLE "ISS"."SUBJECT_PHONE" ADD CONSTRAINT "UK_SP_PH_SUB_BEGIN" UNIQUE ("ID_SUBJECT", "PHONE", "DATE_BEGIN")
 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "SPRAVKA"  ENABLE;

> OW ©   (14.03.11 11:37) [3]


select count(*) as CNT  from SUBJECT_PHONE
WHERE
ID_SUBJECT = 1624664 - сколько показывает?

> select count(*) as CNT  from SUBJECT_PHONE
> WHERE
> ID_SUBJECT = 1624664 - сколько показывает?


6

да-да, тут, кажется, сейчас..

ну да, логично. Все правильно
UPDATE SUBJECT_PHONE
SET
ID_SUBJECT = :ID_SUBJECT,
PHONE = :PHONE,
PHONE_NAME = :PHONE_NAME,
DATE_BEGIN = :DATE_BEGIN
WHERE
ID_SUBJECT = :OLD_ID_SUBJECT
and PHONE = :OLD_PHONE
Так должно быть, конечно.

Согласен, cтупил :-)

Даже так:
UPDATE SUBJECT_PHONE
SET
 ID_SUBJECT = :ID_SUBJECT,
--  PHONE = :PHONE,      { TODO : Сделать в диалог }
 PHONE_NAME = :PHONE_NAME,
 DATE_BEGIN = :DATE_BEGIN,
 DATE_END = :DATE_END,
 ORDER_SORT = :ORDER_SORT,
 ALTERNATE_ADDRESS = :ALTERNATE_ADDRESS,
 UPDATE_STATUS = :UPDATE_STATUS,
 IN_09 = :IN_09,
 IS_FREE = :IS_FREE,
 IS_PARALLEL = :IS_PARALLEL,
 PAID_INFORM = :PAID_INFORM,
 PCOMMENT = :PCOMMENT,
 ADDRESS = :ADDRESS
WHERE
 ID_SUBJECT = :OLD_ID_SUBJECT
 and PHONE = :OLD_PHONE

>OW ©   (14.03.11 11:17)  
1. select * from v$version

2. покажите результат:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

1624664 8342474312 NULL

Да в общем все решилось,
всем спасибо