Jak mogę usunąć ograniczenie „niezerowe” w Oracle, jeśli nie znam jego nazwy?


85

Mam bazę danych, która ma ograniczenie NOT NULL na polu i chcę usunąć to ograniczenie. Czynnikiem komplikującym jest fakt, że to ograniczenie ma nazwę zdefiniowaną przez system, a nazwa tego ograniczenia różni się w przypadku serwera produkcyjnego, serwera integracyjnego i różnych baz danych deweloperów. Nasz obecny proces polega na sprawdzaniu skryptów zmian, a zautomatyzowane zadanie wykonuje odpowiednie zapytania za pośrednictwem sqlplus względem docelowej bazy danych, więc wolałbym rozwiązanie, które można po prostu wysłać bezpośrednio do sqlplus.

W mojej własnej bazie danych SQL do usunięcia będzie wyglądał następująco:

alter table MYTABLE drop constraint SYS_C0044566

Widzę ograniczenie, kiedy odpytuję all_constraintswidok:

select * from all_constraints where table_name = 'MYTABLE'

ale nie jestem pewien, jak do pracy z SEARCH_CONDITION„s LONGTyp danych lub w jaki sposób najlepiej dynamicznie usunąć wyglądał się ograniczenia nawet po znam jego imienia.

Jak więc mogę stworzyć skrypt zmiany, który może porzucić to ograniczenie na podstawie tego, czym ono jest, a nie jego nazwą?


EDYCJA: Odpowiedź @ Allana jest dobra, ale obawiam się (z powodu mojego braku wiedzy na temat Oracle), że może nie być uniwersalną prawdą, że jakiekolwiek ograniczenie, które może mieć nazwę wygenerowaną przez system, będzie wiązało się z tym sposobem usunięcia ograniczenie bez znajomości jego nazwy. Czy to prawda, że ​​zawsze będzie sposób na uniknięcie konieczności znajomości nazwy ograniczenia o nazwie systemowej podczas logicznego usuwania tego ograniczenia?


3
Tylko po to, aby zaspokoić swoją ciekawość: ograniczenie NOT NULL jest jedynym typem ograniczenia w Oracle, które można usunąć bez konieczności znajomości nazwy ograniczenia. W przypadku wszystkich innych typów ograniczeń musisz znać nazwę ograniczenia.
Jeffrey Kemp

Odpowiedzi:


166
alter table MYTABLE modify (MYCOLUMN null);

W Oracle ograniczenia not null są tworzone automatycznie, gdy nie określono wartości null dla kolumny. Podobnie są one usuwane automatycznie, gdy kolumna zostanie zmieniona, aby zezwolić na wartości null.

Wyjaśnienie poprawionego pytania : to rozwiązanie dotyczy tylko wiązań utworzonych dla kolumn „niezerowych”. Jeśli określisz „Klucz podstawowy” lub ograniczenie sprawdzające w definicji kolumny bez nadawania mu nazwy, otrzymasz wygenerowaną przez system nazwę ograniczenia (i indeks dla klucza podstawowego). W takich przypadkach musisz znać nazwę, aby ją usunąć. Najlepszą radą jest unikanie tego scenariusza, upewniając się, że określisz nazwę dla wszystkich ograniczeń innych niż „niezerowe”. Jeśli znajdziesz się w sytuacji, w której musisz ogólnie porzucić jedno z tych ograniczeń, prawdopodobnie będziesz musiał skorzystać z PL / SQL i tabel definicji danych.


Wydaje się to zbyt piękne, aby mogło być prawdziwe, ale zdecydowanie obsługuje moją obecną sprawę i jest po prostu proste! Czy są jakieś przypadki w wyroczni, w których nazwa ograniczenia mogłaby zostać wygenerowana przez system, ale nie można napisać sql, aby uniknąć takiej nazwy ograniczenia?
Chris Farmer

1
Dzięki ... okazuje się, że not nullograniczenia są jedynymi nazwanymi systemowymi w moim schemacie, które prawdopodobnie kiedykolwiek wpłyną na mnie w ten sposób.
Chris Farmer

16

Próbować:

alter table <your table> modify <column name> null;

1

Pamiętaj tylko, że jeśli pole, które chcesz nadać wartości null, jest częścią klucza podstawowego, nie możesz. Klucze podstawowe nie mogą mieć pól zerowych.


1

Aby odkryć zastosowane ograniczenia, użyj poniższego kodu:

-- Set the long data type for display purposes to 500000.

SET LONG 500000

-- Define a session scope variable.

VARIABLE output CLOB

-- Query the table definition through the <code>DBMS_METADATA</code> package.

SELECT dbms_metadata.get_ddl('TABLE','[Table Described]') INTO :output FROM dual;

To zasadniczo pokazuje instrukcję tworzenia, w jaki sposób tworzona jest tabela, do której się odwołuje. Wiedząc, jak tworzona jest tabela, możesz zobaczyć wszystkie ograniczenia tabeli.

Odpowiedź zaczerpnięta z bloga Michaela McLaughlina: http://michaelmclaughlin.info/db1/lesson-5-querying-data/lab-5-querying-data/ Z jego klasy Database Design I.


0

Miałem ten sam problem, próbując obejść niestandardowe ograniczenie sprawdzające, które musiałem zaktualizować, aby zezwolić na różne wartości. Problem polega na tym, że ALL_CONSTRAINTS nie ma sposobu, aby stwierdzić, do której kolumny są stosowane ograniczenia. Sposób, w jaki udało mi się to zrobić, polega na wysłaniu zapytania do ALL_CONS_COLUMNS, a następnie porzuceniu każdego z ograniczeń według ich nazwy i odtworzeniu go.

wybierz constraint_name z all_cons_columns, gdzie table_name = [TABLE_NAME] i column_name = [COLUMN_NAME];


0

Coś takiego przydarzyło mi się, gdy robiłem kopie struktur do tymczasowych tabel, więc usunąłem not null.

DECLARE
   CURSOR cur_temp_not_null IS
        SELECT table_name, constraint_name  FROM all_constraints WHERE table_name LIKE 'TEMP_%' AND  owner='myUSUARIO';

   V_sql VARCHAR2(200); 

BEGIN
  FOR c_not_null IN cur_temp_not_null
   LOOP
     v_sql :='ALTER TABLE ' || c_not_null.table_name || ' DROP CONSTRAINT '|| c_not_null.constraint_name;
     EXECUTE IMMEDIATE  v_sql;     
  END LOOP;
END;

Stack Overflow to witryna wyłącznie w języku angielskim. Istnieje jednak przepełnienie stosu en español . Zobacz moją edycję.
help-info.de

0

Jeśli ograniczenie dotyczące kolumny STATUS zostało utworzone bez nazwy podczas tworzenia tabeli, Oracle przypisze mu losową nazwę. Niestety nie możemy bezpośrednio modyfikować ograniczenia.

Kroki polegające na usunięciu nienazwanego ograniczenia połączonego z kolumną STATUS

  1. Zduplikuj pole STATUS do nowego pola STATUS2
  2. Zdefiniuj ograniczenia CHECK w STATUS2
  3. Przeprowadź migrację danych ze STATUS do STATUS2
  4. Usuń kolumnę STATUS
  5. Zmień nazwę STATUS2 na STATUS

    ALTER TABLE MY_TABLE ADD STATUS2 NVARCHAR2(10) DEFAULT 'OPEN'; ALTER TABLE MY_TABLE ADD CONSTRAINT MY_TABLE_CHECK_STATUS CHECK (STATUS2 IN ('OPEN', 'CLOSED')); UPDATE MY_TABLE SET STATUS2 = STATUS; ALTER TABLE MY_TABLE DROP COLUMN STATUS; ALTER TABLE MY_TABLE RENAME COLUMN STATUS2 TO STATUS;

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.