Мастера DELPHI (документация, исходники, скачать)

Основная («Начинающим»)/ Базы / WinAPI / Компоненты / Сети / Media / Игры / Corba и COM / KOL / FreePascal / .Net / Прочее / RSDN.ORG

« предыдущая ветвь | форум | следующая ветвь »

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

OW © (2011-03-14 11:17:00)
Запрос
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 12:02:00) [1]
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

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

Кщд (2011-03-14 11:55:00) [2]
>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

OW (2011-03-14 11:53:00) [3]
Даже так:
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 (2011-03-14 11:51:00) [4]
ну да, логично. Все правильно
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тупил :-)

OW (2011-03-14 11:48:00) [5]

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

6

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

Вариант (2011-03-14 11:43:00) [6]

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

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

OW (2011-03-14 11:41:00) [7]
полное определение таблицы такое

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 (2011-03-14 11:37:00) [8]
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

Sergey13 (2011-03-14 11:24:00) [9]
> :DATE_BEGIN(IN)=<NULL>
А если не нулл попробовать?

OW (2011-03-14 11:22:00) [10]

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

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

Наверх

	« предыдущая ветвь \| форум \| следующая ветвь »