根據其他人的存在觸發或檢查約束插入值

為簡單起見（因為它抓住了我的問題的本質），假設我有一個包含數據的表。

創建表 my_practice_table (

my_id       NUMBER(12), 

field_2     VARCHAR2(20),

field_3     VARCHAR2(20)

);

我想在具有以下邏輯的表上定義一個新約束：

對於插入 my_practice_table 的任何新記錄 x，

如果（x.field_2 為空）

  if [there exists a record y in the table such that 

  i.) y.my_id == x.my_id 

  AND

  ii.) y.field_2 is not null
   ]

      --do not insert x into the table

  else
     --there exists no such y in the table, so we insert x

否則（x.field_2 不為空）

if [there exists a record y in the table such that
   i.) y.my_id == x.my_id
   AND
   ii.) y.field_2 is null
  ]
    --do not insert x into the table
else 
    --there exists no such y in the table, so we insert x

範例：假設 my_practice_table 有以下數據 table1：

$$ 1 ‘hi’ ‘bye’ $$ $$ 2 ‘hello’ ‘adios’ $$ $$ 1 ‘hi’ ‘seeya’ $$ 語句： INSERT INTO table1 (my_id, field_2, field_3) VALUES (1, null, ‘ciao’);

將不起作用，因為 table1 中已經有一條 my_id 等於 1 且 field_2 不等於 null 的記錄。

範例：假設 my_practice_table 有以下數據：table2：

$$ 1 ‘hi’ ‘bye’ $$ $$ 2 null ‘adios’ $$ $$ 1 ‘hi’ ‘seeya’ $$ 語句： INSERT INTO table2 (my_id, field_2, field_3) VALUES (2, ‘hola’, ’this won’t work’);

將不起作用，因為 table2 中已經有一條記錄，其中 my_id 等於 2 並且 field_2 等於 null。

我的問題是在 Oracle 語法中解決這個問題的方法是什麼。該算法很簡單，但我對 Oracle 語法不夠滿意，無法完成工作。我是否使用 BEFORE INSERT 觸發器？在欄位 my_id 上找到匹配項後，如何掃描特定行的其他欄位中的值？Oracle 中是否有“中斷”語句可以加快速度？對於尋找可能解決方案的正確位置的任何幫助或指導，我們將不勝感激。謝謝

如果您在同一個表的觸發器內查詢要插入的表，您可能會得到“ORA-04091：表名正在發生變化，觸發器/函式可能看不到它”。錯誤。檢查約束不能包含子查詢。因此，您最好的選擇是將數據插入儲存過程的邏輯封裝並使用該過程而不是直接插入。

我可以幫助您編寫程序，但是，在我看來，您的數據模型存在邏輯缺陷。您的範例數據顯示 my_id 不是表的唯一鍵，而是許多行可能共享 my_id 的相同值。因此，如果 my_id = 1 的一行將 field_2 設置為某個值，而對於其他行 field_2 為空，該怎麼辦？你的表中沒有主鍵嗎？

無論如何，如果你想要你在文章中寫的字面意思，你可以做類似的事情

create or replace procedure my_practice_table_insert(p_my_id in number, p_field_2 in varchar2, p_field_3 in varchar2)
is
   l_cnt number;
begin
   if p_field_2 is null then
       select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is not null;
   else
       select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is null;
   end if;

   if l_cnt = 0 then
       insert into my_practice_table(my_id, field_2, field_3) values(p_my_id, p_field_2, p_field_3);
   end if;
end;

我實際上並沒有嘗試過這個過程，所以它可能會有輕微的錯誤，但這是它可以採取的一般形式。

引用自：https://dba.stackexchange.com/questions/98365