ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Реализовать constraint
25 сообщений из 44, страница 1 из 2
  1  все
Реализовать constraint
    #40047675
Фотография wsnet
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wsnet
Участник
Всем привет, подскажите следующее,
есть таблица 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:49
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047677
Фотография AlexFF__|
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexFF__|
Участник
wsnet

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

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

Вы это хотите сделать?
Нестандартный подход
...
Рейтинг: 0 / 0
21.02.2021, 15:54
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047683
Фотография wsnet
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wsnet
Участник
AlexFF__|, хорошо, это ясно, но вопрос в ином был, как выкрутиться?
...
Рейтинг: 0 / 0
21.02.2021, 16:11
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047684
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
AlexFF__|
Нет в этой таблице уникального индекса на два поля.
Об этом прямо и говорит ошибка: ORA-02270 no matching primary or unique key for then column list
Отвратительно слабовато. Ошибка ничего про индексы не говорит в принципе.
...
Рейтинг: 0 / 0
21.02.2021, 16:12
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047685
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
wsnet
каким образом можно выкрутиться в данном случаи
RTFM virtual columns & unique constraint
...
Рейтинг: 0 / 0
21.02.2021, 16:13
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047692
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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.
...
Рейтинг: 0 / 0
21.02.2021, 16:34
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047700
Фотография wsnet
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wsnet
Участник
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:15
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047702
Фотография wsnet
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wsnet
Участник
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, 17:23
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047714
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
wsnet
Увы, моя версия:
invisible не обязателен.
Но версия - гуано.
...
Рейтинг: 0 / 0
21.02.2021, 18:17
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047721
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
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:34
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047723
Фотография wsnet
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wsnet
Участник
SY, вот это и не весьма устраивает нет invisible,
Вопрос а кроме виртуальных столбцов есть ли альтернативные варианты? Триггер, например?
...
Рейтинг: 0 / 0
21.02.2021, 18:45
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047724
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
wsnetкаким образом можно выкрутиться в данном случаи, только триггер на detail?

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

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

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


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

SY.
...
Рейтинг: 0 / 0
21.02.2021, 19:33
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047741
Фотография wsnet
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wsnet
Участник
Dimitry Sibiryakov, пожалуйста почитайте топик еще раз, не надо писать чтобы написать.
...
Рейтинг: 0 / 0
21.02.2021, 19:34
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047742
Фотография wsnet
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wsnet
Участник
SY, хорошо, вы имейте ввиду многопользовательскую работу с таблицей?
...
Рейтинг: 0 / 0
21.02.2021, 19:36
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047745
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
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:38
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047746
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
wsnet
SY, хорошо, вы имейте ввиду многопользовательскую работу с таблицей?


Да.

SY.
...
Рейтинг: 0 / 0
21.02.2021, 19:39
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047748
Фотография wsnet
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wsnet
Участник
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:48
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047750
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
wsnet
Я не очень понял, а если все же вариант с триггером простейшим применить:
Ты сперва ответь, тебе нужна ссылочная целостность, с которой ты начал, или же нет?
...
Рейтинг: 0 / 0
21.02.2021, 19:49
| Ответить | Цитировать | Написать  
Реализовать constraint
    #40047753
Фотография wsnet
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wsnet
Участник
Elic
wsnet
Я не очень понял, а если все же вариант с триггером простейшим применить:
Ты сперва ответь, тебе нужна ссылочная целостность, с которой ты начал, или же нет?

Elic, скажу так тебе, в таблицу softmaster.master добавляет записи один человек (очень редко с ней работает), с таблицей softmaster.detail могут работать одновременно несколько человек по добавлению записей.
Как думаешь при таких данных нужна она или же обойдемся триггером?
...
Рейтинг: 0 / 0
21.02.2021, 19:59
| Ответить | Цитировать | Написать  
25 сообщений из 44, страница 1 из 2
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Реализовать constraint
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=25&tid=1880415]:
 0ms
get settings:
 11ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 29ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 64ms
get tp. blocked users:
 2ms
others: 253ms
total:  393ms
созд. / загр.: 393ms
x
x
Закрыть


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