Считаю, что странное нарушение целостности какое-то..

← →
OW © (2011-03-14 11:17) [0]

Запрос
UPDATE SUBJECT_PHONE SET ID_SUBJECT = :ID_SUBJECT, PHONE = :PHONE, PHONE_NAME = :PHONE_NAME, DATE_BEGIN = :DATE_BEGIN WHERE ID_SUBJECT = :OLD_ID_SUBJECT
: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

Таблица
CREATE TABLE "ISS"."SUBJECT_PHONE" ( "ID_SUBJECT" NUMBER, "PHONE" VARCHAR2(11), "PHONE_NAME" VARCHAR2(1000), "DATE_BEGIN" DATE, "DATE_END" DATE, ALTER TABLE "ISS"."SUBJECT_PHONE" ADD CONSTRAINT "UK_SP_PH_SUB_BEGIN" UNIQUE ("ID_SUBJECT", "PHONE", "DATE_BEGIN")

Проверяю:
select * from SUBJECT_PHONE SP where SP.PHONE = "8342474312" and SP.ID_SUBJECT = 1624664
1 запись выбрана.

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

← →
OW © (2011-03-14 11:22) [1]

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

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

← →
Sergey13 © (2011-03-14 11:24) [2]

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

← →
OW © (2011-03-14 11:37) [3]

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

← →
OW © (2011-03-14 11:41) [4]

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

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;

← →
Вариант (2011-03-14 11:43) [5]

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

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

← →
OW © (2011-03-14 11:48) [6]

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

6

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

← →
OW © (2011-03-14 11:51) [7]

ну да, логично. Все правильно
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

← →
Кщд (2011-03-14 11:55) [9]

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

2. покажите результат:
select ID_SUBJECT, PHONE, DATE_BEGIN from SUBJECT_PHONE SP where SP.PHONE = "8342474312" and SP.ID_SUBJECT = 1624664

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

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

Считаю, что странное нарушение целостности какое-то.. Найти похожие ветки