Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Реализовать constraint / 25 сообщений из 44, страница 1 из 2
  1  все
21.02.2021, 15:49
    #40047675
wsnet
wsnet
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
Всем привет, подскажите следующее,
есть таблица master - содержащее уникальный индекс на два поля,
есть таблица detail - должна содержать внешний ключ на эти уникальные поля таблицы master

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
drop table softmaster.master;
drop table softmaster.detail;

create table softmaster.master(a int, b int not null);
create unique index softmaster.uq_a_b on softmaster.master (decode(b, null, null, a), decode(a, null, null, b));

-- insert records
insert into softmaster.master(a, b) values (1, 1);
insert into softmaster.master(a, b) values (1, 2);

insert into softmaster.master(a, b) values (null, 1);
insert into softmaster.master(a, b) values (null, 1);

create table softmaster.detail(a int, b int, c int);

-- При попытке создания внешнего ключа
alter table  softmaster.detail
  add constraint fk_detail_a_b foreign key (a, b)
  references softmaster.master(a, b);  
  
-- Выдается ошибка ORA-02270 no matching primary or unique key for then column list



Собственно вопрос, каким образом можно выкрутиться в данном случаи, только триггер на detail?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 15:54
    #40047677
AlexFF__|
AlexFF__|
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
wsnet

есть таблица master - содержащее уникальный индекс на два поля

Нет в этой таблице уникального индекса на два поля.
Об этом прямо и говорит ошибка: ORA-02270 no matching primary or unique key for then column list

Вы это хотите сделать?
Нестандартный подход
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 16:11
    #40047683
wsnet
wsnet
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
AlexFF__|, хорошо, это ясно, но вопрос в ином был, как выкрутиться?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 16:12
    #40047684
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
AlexFF__|
Нет в этой таблице уникального индекса на два поля.
Об этом прямо и говорит ошибка: ORA-02270 no matching primary or unique key for then column list
Отвратительно слабовато. Ошибка ничего про индексы не говорит в принципе.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 16:13
    #40047685
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
wsnet
каким образом можно выкрутиться в данном случаи
RTFM virtual columns & unique constraint
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 16:34
    #40047692
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
Как Elic намекнул + invisible:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
drop table detail purge;
drop table master purge;

create table master(a int,b int not null);
alter table master
  add (
       a1 int invisible generated always as (decode(b,null,null,a)),
       b1 int invisible generated always as (decode(a,null,null,b))
      );

create unique index uq_a_b on master(a1,b1);

alter table master
  add constraint uk_master
    unique(a1,b1);

-- insert records
insert into master(a,b) values(1,1);
insert into master(a,b) values(1,2);

insert into master(a,b) values(null,1);
insert into master(a,b) values(null,1);

create table detail(a int,b int,c int);
alter table detail
  add (
       a1 int invisible generated always as (decode(b,null,null,a)),
       b1 int invisible generated always as (decode(a,null,null,b))
      );

alter table  detail
  add constraint fk_detail_a_b foreign key(a1,b1)
  references master(a1,b1);



Ну и в чем сакральный смысл менять a на b (decode(a,null,null,b)) если a not null и b на a (decode(b,null,null,a)) если b not null?

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 17:15
    #40047700
wsnet
wsnet
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
SY, Elic спасибо.

SY
Как Elic намекнул + invisible:

Ну и в чем сакральный смысл менять a на b (decode(a,null,null,b)) если a not null и b на a (decode(b,null,null,a)) если b not null?

SY.


SY, прибегнуть пришлось из-за того, что изначально уникальным ключом было одно поле, а не две:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
SQL> create table softmaster.master(a int, b int not null);

Table created.

SQL> alter table softmaster.master
  2    add constraint uq_a unique (a);

Table altered.

SQL> insert into softmaster.master(a, b) values (1, 1);

1 row created.

SQL> insert into softmaster.master(a, b) values (null, 1);

1 row created.

SQL> insert into softmaster.master(a, b) values (null, 1);

1 row created.

SQL> alter table  softmaster.master drop constraint uq_a;

Table altered.

SQL> alter table softmaster.master
  2    add constraint uq_a_b unique (a, b);
  add constraint uq_a_b unique (a, b)
                 *
ERROR at line 2:
ORA-02299: cannot validate (SOFTMASTER.UQ_A_B) - duplicate keys found



Быть может Oracle позволяет по-иному выйти из ситуации?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 17:23
    #40047702
wsnet
wsnet
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
SY
Как Elic намекнул + invisible:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
drop table detail purge;
drop table master purge;

create table master(a int,b int not null);
alter table master
  add (
       a1 int invisible generated always as (decode(b,null,null,a)),
       b1 int invisible generated always as (decode(a,null,null,b))
      );

create unique index uq_a_b on master(a1,b1);

alter table master
  add constraint uk_master
    unique(a1,b1);

-- insert records
insert into master(a,b) values(1,1);
insert into master(a,b) values(1,2);

insert into master(a,b) values(null,1);
insert into master(a,b) values(null,1);

create table detail(a int,b int,c int);
alter table detail
  add (
       a1 int invisible generated always as (decode(b,null,null,a)),
       b1 int invisible generated always as (decode(a,null,null,b))
      );

alter table  detail
  add constraint fk_detail_a_b foreign key(a1,b1)
  references master(a1,b1);



Ну и в чем сакральный смысл менять a на b (decode(a,null,null,b)) если a not null и b на a (decode(b,null,null,a)) если b not null?

SY.


Увы, моя версия:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SQL> select * from v$version;

BANNER
-------------------------------------------------------------------

Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 18:17
    #40047714
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
wsnet
Увы, моя версия:
invisible не обязателен.
Но версия - гуано.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 18:34
    #40047721
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
wsnet


Увы, моя версия:


Ну убери invisible (правда придется указывать список полей там где раньше был *):

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> drop table detail purge;
drop table detail purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> drop table master purge;
drop table master purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> create table master(a int,b int not null);

Table created.

SQL> alter table master
  2    add (
  3         a1 int generated always as (decode(b,null,null,a)),
  4         b1 int generated always as (decode(a,null,null,b))
  5        );

Table altered.

SQL>
SQL> create unique index uq_a_b on master(a1,b1);

Index created.

SQL>
SQL> alter table master
  2    add constraint uk_master
  3      unique(a1,b1);

Table altered.

SQL>
SQL> -- insert records
SQL> insert into master(a,b) values(1,1);

1 row created.

SQL> insert into master(a,b) values(1,2);

1 row created.

SQL>
SQL> insert into master(a,b) values(null,1);

1 row created.

SQL> insert into master(a,b) values(null,1);

1 row created.

SQL>
SQL> create table detail(a int,b int,c int);

Table created.

SQL> alter table detail
  2    add (
  3         a1 int generated always as (decode(b,null,null,a)),
  4         b1 int generated always as (decode(a,null,null,b))
  5        );

Table altered.

SQL>
SQL> alter table  detail
  2    add constraint fk_detail_a_b foreign key(a1,b1)
  3    references master(a1,b1);

Table altered.

SQL>



Но главное: в чем сакральный смысл менять a на b (decode(a,null,null,b)) если a not null и b на a (decode(b,null,null,a)) если b not null?

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 18:45
    #40047723
wsnet
wsnet
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
SY, вот это и не весьма устраивает нет invisible,
Вопрос а кроме виртуальных столбцов есть ли альтернативные варианты? Триггер, например?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 18:52
    #40047724
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
wsnetкаким образом можно выкрутиться в данном случаи, только триггер на detail?

Ты создавай не уникальный индекс, а unique constraint. Оно позволяет null-ы и
удовлетворяет foreign key.

PS: А с совпадением значений второго поля придётся разбираться бизнес-логикой. Ну или хотя бы бытовой: на какую из двух записей должна ссылаться запись в detail (null, 1)?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 19:10
    #40047732
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
wsnet
Вопрос а кроме виртуальных столбцов есть ли альтернативные варианты? Триггер, например?
Чудак, триггером ты сможешь лишь более трудозатратно заполнять столбцы, которые тебе всё равно придётся добавить в таблицу.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 19:11
    #40047733
wsnet
wsnet
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
Dimitry Sibiryakov
wsnetкаким образом можно выкрутиться в данном случаи, только триггер на detail?

Ты создавай не уникальный индекс, а unique constraint. Оно позволяет null-ы и
удовлетворяет foreign key.

PS: А с совпадением значений второго поля придётся разбираться бизнес-логикой. Ну или хотя бы бытовой: на какую из двух записей должна ссылаться запись в detail (null, 1)?

Не очень ясно как в данном случаи мне поможет unique constraint?
Ведь таблица заполнена изначально данными. Здесь указывал на это 22284458

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
SQL> create table softmaster.master(a int, b int not null);

Table created.

SQL> insert into softmaster.master(a, b) values (1, 1);

1 row created.

SQL> insert into softmaster.master(a, b) values (1, 2);

1 row created.

SQL>
SQL> insert into softmaster.master(a, b) values (null, 1);

1 row created.

SQL> insert into softmaster.master(a, b) values (null, 1);

1 row created.

SQL> alter table softmaster.master
  2   add constraint uq_a_b unique (a, b);
 add constraint uq_a_b unique (a, b)
                *
ERROR at line 2:
ORA-02299: cannot validate (SOFTMASTER.UQ_A_B) - duplicate keys found
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 19:11
    #40047734
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
Dimitry Sibiryakov
Ты создавай не уникальный индекс, а unique constraint. Оно позволяет null-ы и
Ты не в теме. Не позорься.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 19:29
    #40047738
wsnet
wsnet
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
Elic
wsnet
Вопрос а кроме виртуальных столбцов есть ли альтернативные варианты? Триггер, например?
Чудак, триггером ты сможешь лишь более трудозатратно заполнять столбцы, которые тебе всё равно придётся добавить в таблицу.


Я не очень понял, а если все же вариант с триггером простейшим применить:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
SQL> create table softmaster.master(a int, b int not null);

Table created.

SQL> create unique index softmaster.uq_a_b on softmaster.master (decode(b, n
 null, a), decode(a, null, null, b));

Index created.

SQL> insert into softmaster.master(a, b) values (1, 1);

1 row created.

SQL> insert into softmaster.master(a, b) values (1, 2);

1 row created.

SQL> insert into softmaster.master(a, b) values (null, 1);

1 row created.

SQL> insert into softmaster.master(a, b) values (null, 1);

1 row created.

SQL> create table softmaster.detail(a int, b int, c int);

Table created.

SQL> alter table  softmaster.detail
  2    add constraint fk_detail_a_b foreign key (a, b)
  3    references softmaster.master(a, b);
  references softmaster.master(a, b)
                               *
ERROR at line 3:
ORA-02270: no matching unique or primary key for this column-list


SQL> CREATE OR REPLACE TRIGGER softmaster.ins_upd_detail
  2   before insert or update on softmaster.detail
  3    for each row
  4  declare
  5   ct number;
  6  begin
  7    if :new.b is null then
  8      :new.b := 2;
  9    end if;
 10
 11    select count(1)
 12      into ct
 13      from softmaster.master m
 14      where m.a = :new.a
 15        and m.b = :new.b;
 16    -----
 17    if ct = 0
 18      then
 19        raise_application_error(-20001,'Не найдена запись в master');
 20    end if;
 21  end;
 22  /

Trigger created.

SQL> SHOW ERRORS;
No errors.
SQL> insert into softmaster.detail(a, b) values (1, 2);

1 row created.

SQL> insert into softmaster.detail(a, b) values (11, 2);
insert into softmaster.detail(a, b) values (11, 2)
                       *
ERROR at line 1:
ORA-20001: Не найдена запись в master
ORA-06512: at "SOFTMASTER.INS_UPD_DETAIL", line 16
ORA-04088: error during execution of trigger 'SOFTMASTER.INS_UPD_DETAIL'
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 19:31
    #40047739
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
wsnetНе очень ясно как в данном случаи мне поможет unique constraint?
Ведь таблица заполнена изначально данными.

При таких данных тебе уже ничто не поможет.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 19:33
    #40047740
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
wsnet
Триггер, например?


Решение триггером потребует сериализации на уровне таблицы. UK/FK сериализует на уровне строки дубликата/родителя.

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 19:34
    #40047741
wsnet
wsnet
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
Dimitry Sibiryakov, пожалуйста почитайте топик еще раз, не надо писать чтобы написать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 19:36
    #40047742
wsnet
wsnet
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
SY, хорошо, вы имейте ввиду многопользовательскую работу с таблицей?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 19:38
    #40047745
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
wsnetinsert into softmaster.detail(a, b)values (11,2)
*
ERRORat line1:
ORA-20001: Не найдена запись в master
ORA-06512:at "SOFTMASTER.INS_UPD_DETAIL", line16
ORA-04088: error during executionof trigger 'SOFTMASTER.INS_UPD_DETAIL'

А теперь попробуй с (null,1).
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 19:39
    #40047746
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
wsnet
SY, хорошо, вы имейте ввиду многопользовательскую работу с таблицей?


Да.

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 19:48
    #40047748
wsnet
wsnet
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
Dimitry Sibiryakov, попробую, но пока задача этого не предполагает:
Код: plsql
1.
2.
3.
4.
 if ct = 0 and :new.a is not null
    then
      raise_application_error(-20001,'Не найдена запись в master');
  end if;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 19:49
    #40047750
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
wsnet
Я не очень понял, а если все же вариант с триггером простейшим применить:
Ты сперва ответь, тебе нужна ссылочная целостность, с которой ты начал, или же нет?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2021, 19:59
    #40047753
wsnet
wsnet
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Реализовать constraint
Elic
wsnet
Я не очень понял, а если все же вариант с триггером простейшим применить:
Ты сперва ответь, тебе нужна ссылочная целостность, с которой ты начал, или же нет?

Elic, скажу так тебе, в таблицу softmaster.master добавляет записи один человек (очень редко с ней работает), с таблицей softmaster.detail могут работать одновременно несколько человек по добавлению записей.
Как думаешь при таких данных нужна она или же обойдемся триггером?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Реализовать constraint / 25 сообщений из 44, страница 1 из 2
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 199ms
Закрыть
start [/forum/moderation_log.php?user_name=san4es.spb]:
 0ms
get settings:
 9ms
get forum list:
 15ms
get settings:
 10ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 63ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 52ms
get tp. blocked users:
 1ms
others: 20ms
total:  199ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]