История страницы

Мы можем удалить ограничение в базе данных Oracle, используя constraint-name. Имя ограничения может быть сгенерировано базой данных Oracle или задано пользователем.

Синтаксис для удаления ограничений в Oracle,
ALTER TABLE tablename
DROP CONSTRAINT constraintName;

Но есть отдельная история для ограничения NOT NULL и ограничения PRIMARY KEY. Мы не можем использовать синтаксис выше для ограничения NOT NULL. Ограничение первичного ключа можно удалить с помощью синтаксиса выше, но есть и другой способ.

Удаление ограничения NOT NULL в Oracle

Мы можем удалить ограничение NOT NULL в базе данных Oracle из существующего столбца, но этот столбец не должен использоваться в ограничении PRIMARY KEY.

В Oracle, когда столбец изменяется так, чтобы разрешить значения NULL, база данных Oracle автоматически снимает ограничения NOT NULL.

Синтаксис, разрешающий значение NULL для существующего столбца:
ALTER TABLE tablename
MODIFY(columnName NULL);

Пример удаления ограничений NOT NULL из столбцов sno и address таблицы «nulltest»:

SQL> CREATE TABLE nulltest (
       sno NUMBER(10) NOT NULL,
       name VARCHAR2(15) NOT NULL
     );
Table created.

SQL> DESC nulltest;
 Name     Null?    Type
 -------- -------- ------------
 SNO      NOT NULL NUMBER(10)
 NAME     NOT NULL VARCHAR2(15)

SQL>  ALTER TABLE nulltest 
      MODIFY (sno NULL, name NULL);
Table altered.

SQL> DESC nulltest;
 Name     Null?    Type
 -------- -------- ------------
 SNO               NUMBER(10)
 NAME              VARCHAR2(15)

Удаление ограничения PRIMARY KEY в Oracle

Для таблицы может быть только одно ограничение PRIMARY KEY, поэтому удалить ограничение PRIMARY KEY очень просто.

Синтаксис:-
ALTER TABLE tableName
DROP PRIMARY KEY

Используя приведенный выше синтаксис, мы можем удалить ограничение PRIMARY KEY на уровне столбца, а также на уровне таблицы. Пример:-

SQL> CREATE TABLE primarytest (
       sno NUMBER(10) PRIMARY KEY
     );
Table created.

SQL> ALTER TABLE primarytest 
     DROP PRIMARY KEY;
Table altered.

Примечание:- Обычно мы не можем удалить ограничение PRIMARY KEY вместе с REFERENCED KEY. Чтобы преодолеть эту проблему, Oracle предоставил предложение CASCADE вместе с ALTER DROP, которое используется для удаления ограничения первичного ключа. Синтаксис:- ALTER TABLE tableName DROP PRIMARY KEY CASCADE;

Синтаксис:-
ALTER TABLE tablename
DROP PRIMARY KEY CASCADE;

Пример:-

SQL> CREATE TABLE master1 (
       sno NUMBER(10) PRIMARY KEY
     );
Table created.

SQL> CREATE TABLE child1 (
       sno NUMBER(10)
       REFERENCES master1
     );
Table created.

SQL> ALTER TABLE master1
     DROP PRIMARY KEY;
ERROR:- ORA-02273: this unique/primary key 
is referenced by some foreign keys

SQL> ALTER TABLE master1
     DROP PRIMARY KEY CASCADE;
Table altered.

Удаление ограничения по имени ограничения

В базе данных Oracle всякий раз, когда мы создаем ограничение, сервер Oracle автоматически генерирует уникальный идентификационный номер для однозначной идентификации ограничения в формате SYS_Cn, это также называется предопределенным именем ограничения.

Используя предопределенное имя ограничения, мы можем удалить ограничения UNIQUE, PRIMARY KEY, FOREIGN KEY и CHECK (но не ограничение NOT NULL).

SQL> CREATE TABLE test (
       sno NUMBER(10) UNIQUE,
       name VARCHAR2(10) UNIQUE
     );
Table created.

SQL> INSERT INTO test VALUES(1, 'abc');
1 row created.

SQL> INSERT INTO test VALUES(1, 'xyz');
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0014205) violated

SQL> INSERT INTO test VALUES(2, 'abc');
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0014206) violated

Здесь SYS_C0014205 и SYS_C0014206 — это сгенерированный Oracle уникальный идентификационный номер ограничения, примененного к столбцам sno и name тестовой таблицы. Эту информацию можно получить из user_cons_columns.

SQL> DESC user_cons_columns;
 Name              Null?    Type
 ----------------- -------- --------------
 OWNER             NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME   NOT NULL VARCHAR2(30)
 TABLE_NAME        NOT NULL VARCHAR2(30)
 COLUMN_NAME                VARCHAR2(4000)
 POSITION                   NUMBER

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST';

COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
SNO             SYS_C0014205
NAME            SYS_C0014206

Обратите внимание, что в приведенном выше запросе имя таблицы TEST указано заглавными буквами. Имя таблицы Oracle, имена столбцов нечувствительны к регистру, но записи таблицы чувствительны к регистру, и здесь TEST — это записьuser_cons_columns.

Чтобы снять ограничение, мы можем использовать эти имена ограничений:

SQL> ALTER TABLE test 
     DROP CONSTRAINTS SYS_C0014205;
Table altered.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST';
COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
NAME            SYS_C0014206

Если ограничение включено на уровне таблицы, то предопределенное имя ограничения будет таким же, как у этих столбцов.

SQL> CREATE TABLE test1 (
       sno NUMBER(10),
       name VARCHAR2(10),
       address VARCHAR2(10),
       UNIQUE(sno, name)
     );
Table created.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST1';
COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
SNO             SYS_C0014207
NAME            SYS_C0014207

SQL> ALTER TABLE test1
     DROP CONSTRAINTS SYS_C0014207;
Table altered.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST1';
no rows selected

Присвоение ограничениям определяемого пользователем имени

При удалении ограничений предопределенное имя ограничения может создать путаницу для других, и просто увидев предопределенное имя ограничения, мы не сможем определить, какое ограничение включено в этом столбце. Вместо предопределенного имени ограничения мы также можем назначить свое собственное имя, используя ключевое слово CONSTRAINT, используя следующий синтаксис. Это называется именем ограничения, определенным пользователем.

Синтаксис:-
CONSTRAINT user-definedname constriantType

Пример:-

SQL> CREATE TABLE test2 (
       sno NUMBER(10) CONSTRAINT unique_sno UNIQUE,
       name VARCHAR2(10)
     );
Table created.

SQL> ALTER TABLE test2
     ADD CONSTRAINT unique_name UNIQUE(name);
Table altered.

SQL> ALTER TABLE test2
     ADD address VARCHAR2(10) 
     CONSTRAINT unique_address UNIQUE;
Table altered.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'test2';
COLUMN_NAME    CONSTRAINT_NAME
-------------- --------------
SNO            UNIQUE_SNO
NAME           UNIQUE_NAME
ADDRESS        UNIQUE_ADDRESS

Теперь мы можем снять эти ограничения, используя тот же синтаксис:ALTER TABLE tablename DROP CONSTRAINT constraintName;

Примечание. Имя ограничения, определяемого пользователем, должно быть уникальным в пределах пользователя, в противном случае Oracle выдаст ошибку: ORA-02264: имя уже используется существующим ограничением.

SQL> CREATE TABLE test3 (
       sno number(10) 
       CONSTRAINT unique_sno UNIQUE
     );
ORA-02264: name already used by an existing constraint

Если вам понравился этот пост, поделитесь им с друзьями. Хотите поделиться дополнительной информацией по теме, обсуждаемой выше, или вы нашли что-то неправильное? Дайте нам знать в комментариях. Спасибо!

Также узнайте,

• GROUP BY пункт
• Имея пункт
• Упорядочить по пункту