Как работать с ограничениями в SQL
Разрабатывая БД SQL, вы можете определять, какие данные можно добавлять в определенные столбцы таблицы, а какие – нет. Это возможно благодаря ограничениям. Если вы применили ограничение к столбцу или таблице, любые попытки добавить в них данные, которые не соответствуют этому правилу, завершатся неудачно.
Различные реализации SQL имеют свои уникальные способы работы с ограничениями. В данном руководстве мы разберем популярный синтаксис, который используют многие СУБД для управления ограничениями.
Примечание: Примеры выполнены на БД MySQL.
Требования
Чтобы следовать этому руководству, вам понадобится компьютер с какой-либо реляционной системой управления базами данных (РСУБД), которая использует SQL.
Инструкции и примеры в этом руководстве были проверены в рамках следующей среды:
- Сервер Ubuntu 20.04 с настроенным пользователем sudo и брандмауэром UFW (подобная настройка описана в этом руководстве).
- Копия MySQL на этом сервере. Установка и защита MySQL описаны в мануале Установка MySQL в Ubuntu 20.04.
Примечание: Обратите внимание, что многие СУБД используют собственные уникальные реализации SQL. Команды, описанные в этом руководстве, будут работать в большинстве СУБД, однако в некоторых ситуациях синтаксис или вывод могут отличаться (если вы используете не MySQL, а другую СУБД).
Также будет полезно иметь общее представление о том, что такое ограничения SQL и как они работают.
Кроме того, вам понадобится база данных, на которой вы сможете попрактиковаться в создании таблиц с ограничениями. Если у вас нет такой тестовой базы данных, мы поможем вам создать ее в первом разделе.
Подключение и настройка тестовой базы данных
Если ваша система базы данных SQL работает на удаленном сервере, подключитесь к серверу по SSH с локального компьютера:
Затем откройте командную строку сервера MySQL (заменив 8host именем вашего пользователя MySQL).
mysql -u 8host -p
Создайте базу данных limitedsDB:
CREATE DATABASE constraintsDB;
Если база данных была создана успешно, вы получите такой результат:
Чтобы выбрать базу данных ConstraintsDB в качестве текущей, выполните следующую команду USE:
Теперь у вас есть все, чтобы следовать данному руководству. Давайте поговорим о том, как создавать ограничения и управлять ими в SQL.
Создание таблиц с ограничениями
Обычно ограничения определяются во время создания таблицы. Следующий синтаксис CREATE TABLE создает таблицу по имени employeeInfo с тремя столбцами: empId, empName и empPhoneNum. Оператор также применяет ограничение UNIQUE к столбцу empId. Данное ограничение предотвратит внесение одинаковых значений в этот столбец:
CREATE TABLE employeeInfo (
empId int UNIQUE,
empName varchar(30),
empPhoneNum int
);
Этот оператор определяет ограничение UNIQUE сразу после столбца empId – значит, ограничение применяется только к этому столбцу. Если вы попытаетесь добавить в эту таблицу какие-либо данные, СУБД проверит текущее содержимое только столбца empId и убедится, что все новые значения, которые вы хотите в него добавить, действительно уникальны. Это называется ограничением уровня столбца.
Применить ограничение также можно вне определений столбцов. В следующем примере мы попробуем создать таблицу racersInfo с тремя столбцами: racerId, racerName и finish. После определений столбцов идет ограничение CHECK, которое применяется к столбцу finish. Благодаря этому ограничению мы можем быть уверены, что значения в данном столбце больше или равны 1 (поскольку здесь отражается порядок, в котором финишировали гонщики, и, очевидно, финишировать нулевым или минус первым невозможно):
CREATE TABLE racersInfo (
racerId int,
finish int,
racerName varchar(30),
CHECK (finish > 0)
);
Поскольку в данном случае ограничение указано за пределами определения конкретного столбца, в круглых скобках мы должны указать имена столбцов, к которым хотим применить ограничение. Ограничение, которое задано вне определения одного столбца, называется ограничением уровня таблицы. Ограничения уровня столбца применяются исключительно к отдельным столбцам, а ограничения уровня таблицы могут применяться к нескольким столбцам (или ссылаться на них).
Именование ограничений
Всякий раз, когда вы определяете ограничение, СУБД автоматически генерирует для него имя. Это имя используется для ссылки на ограничение в сообщениях об ошибках и в командах, используемых для управления ограничениями.
Однако иногда администраторам баз данных удобнее самостоятельно выбрать имя для ограничения. Автоматически сгенерированные имена, как правило, не являются описательными. Выбирая имя самостоятельно, вы оставляете себе подсказку о том, для чего нужно данное ограничение.
Чтобы присвоить ограничению имя, укажите перед типом ограничения ключевое слово CONSTRAINT, за которым укажите любое имя. В примере ниже мы возвращаемся к таблице racersInfo. Переименуем таблицу в newRacersInfo и присвоим ограничению CHECK имя noNegativeFinish:
CREATE TABLE newRacersInfo (
racerId int,
finish int,
racerName varchar(30),
CONSTRAINT noNegativeFinish
CHECK (finish >= 1)
);
Примечание: Если вы не укажете имя для ограничения или забудете его позже, вы, скорее всего, сможете найти это имя, сверившись с информационными схемами вашей СУБД. Многие современные СУБД и клиенты даже предоставляют сокращение для отображения внутренних операторов CREATE. Вот ссылки на официальную документацию по этому сокращению для MySQL и PostgreSQL:
-
включает оператор SHOW CREATE TABLE, который возвращает весь оператор CREATE TABLE, создавший именованную таблицу:
SHOW CREATE TABLE имя_таблицы;
- Клиент PostgreSQL psql имеет ряд параметров, которые можно использовать для отображения информации о заданной таблице. Параметр \d возвращает метаданные именованной таблицы:
Управление ограничениями
В MySQL вы можете добавлять ограничения к существующим таблицам, а также удалять их с помощью команды ALTER TABLE.
Например, следующая команда добавляет ограничение UNIQUE в столбец empName в созданной ранее таблице employeeInfo:
ALTER TABLE employeeInfo ADD UNIQUE (empName);
При добавлении ограничения в существующую таблицу вы также можете использовать ключевое слово CONSTRAINT, чтобы указать имя нужного ограничения. В следующем примере в столбец racerId ранее созданной таблицы racersInfo добавляется ограничение UNIQUE по имени uID:
ALTER TABLE racersInfo ADD CONSTRAINT uID UNIQUE (racerId);
Если перед добавлением какого-либо ограничения вы вставили в таблицу данные, конфликтующие с новым ограничением, оператор ALTER TABLE выдаст ошибку.
Чтобы удалить ограничение, используйте синтаксис DROP CONSTRAINT, за которым укажите имя ограничения, которое хотите удалить. Следующая команда удаляет ограничение racersPK, созданное предыдущей командой:
ALTER TABLE racersInfo DROP CONSTRAINT uID;
Заключение
В этом руководстве вы узнали, как добавлять и удалять ограничения для столбцов и таблиц SQL. Продемонстрированные здесь команды должны работать в большинстве реляционных баз данных, однако имейте в виду, что каждая база данных SQL использует свою собственную уникальную реализацию языка. Для получения более полного и точного описания каждой команды и полного набора их параметров вам следует обратиться к официальной документации вашей СУБД.
ALTER TABLE — изменение таблицы в SQL
Команда ALTER TABLE применяется в SQL при добавлении, удалении либо модификации колонки в существующей таблице. В этой статье будет рассмотрен синтаксис и примеры использования ALTER TABLE на примере MS SQL Server.
SQL-оператор ALTER TABLE способен менять определение таблицы несколькими способами: • добавлением/переопределением/удалением столбца (column); • модифицированием характеристик памяти; • включением, выключением либо удалением ограничения целостности.
При этом пользователю нужно обладать системной привилегией ALTER ANY TABLE либо таблица должна находиться в схеме пользователя.
Меняя типы данных существующих columns либо добавляя их в БД-таблицу, следует соблюдать некоторые условия. Принято, что увеличение есть хорошо, а уменьшение — не очень. Существует ряд допустимых увеличений: • добавляем новые столбцы в таблицу; • увеличиваем размер столбца CHAR либо VARCHAR2; • увеличиваем размер столбца NUMBER.
Нередко перед внесением изменений следует удостовериться, что в соответствующих columns все значения — это NULL-значения. Если выполняется операция над столбцами, которые содержат данные, следует найти либо создать область временного хранения данных. Можно создать таблицу посредством CREATE TABLE AS SELECT, где извлекаются данные из первичного ключа и изменяемых columns. Существует ряд допустимых изменений: • уменьшаем размер столбца NUMBER (лишь при наличии пустого column для всех строк); • уменьшаем размер столбца CHAR либо VARCHAR2 (лишь при наличии пустого column для всех строк); • меняем тип данных столбца (аналогично, что и в первых двух пунктах).
При добавлении column с ограничением NOT NULL, администратор баз данных либо разработчик обязан учесть некоторые обстоятельства. Вначале следует создать столбец без ограничения, потом ввести значения во все строки. Далее, когда значения column будут уже не NULL, к нему можно будет применить ограничение NOT NULL. Но если column с ограничением NOT NULL хочет добавить юзер, то вернётся сообщение об ошибке, судя по которому таблица должна быть либо пустой, либо содержать в столбце значения для каждой имеющейся строки (после наложения на column NOT NULL-ограничения, в нём не смогут присутствовать значения NULL ни в одной из имеющихся строк).
Синтаксис ALTER TABLE на примере MS SQL Server
Рассмотрим общий формальный синтаксис на примере SQL Server от Microsoft:
Итак, используя SQL-оператор ALTER TABLE, мы сможем выполнить разные сценарии изменения таблицы. Далее будут рассмотрены некоторые из этих сценариев.
Добавляем новый столбец
Для примера добавим новый column Address в таблицу Customers:
В примере выше столбец Address имеет тип NVARCHAR, плюс для него определён NULL-атрибут. Если же в таблице уже существуют данные, команда ALTER TABLE не выполнится. Однако если надо добавить столбец, который не должен принимать NULL-значения, можно установить значение по умолчанию, используя атрибут DEFAULT:
Тогда, если в таблице существуют данные, для них для column Address добавится значение «Неизвестно».
Удаляем столбец
Теперь можно удалить column Address:
Меняем тип
Продолжим манипуляции с таблицей Customers: теперь давайте поменяем тип данных столбца FirstName на NVARCHAR(200).
Добавляем ограничения CHECK
Если добавлять ограничения, SQL Server автоматически проверит существующие данные на предмет их соответствия добавляемым ограничениям. В случае несоответствия, они не добавятся. Давайте ограничим Age по возрасту.
При наличии в таблице строк со значениями, которые не соответствуют ограничению, sql-команда не выполнится. Если надо избежать проверки и добавить ограничение всё равно, используют выражение WITH NOCHECK:
По дефолту применяется значение WITH CHECK, проверяющее на соответствие ограничениям.
Добавляем внешний ключ
Представим, что изначально в базу данных будут добавлены 2 таблицы, которые между собой не связаны:
Теперь добавим к столбцу CustomerId ограничение внешнего ключа (таблица Orders):
Добавляем первичный ключ
Применяя определенную выше таблицу Orders, можно добавить к ней для столбца Id первичный ключ:
Добавляем ограничения с именами
Добавляя ограничения, можно указать имя для них — для этого пригодится оператор CONSTRAINT (имя прописывается после него):
Удаляем ограничения
Чтобы удалить ограничения, следует знать их имя. Если с этим проблема, имя всегда можно определить с помощью SQL Server Management Studio:
Следует раскрыть в подузле Keys узел таблиц, где находятся названия ограничений для внешних ключей (названия начинаются с «FK»). Обнаружить все ограничения DEFAULT (названия начинаются с «DF») и CHECK («СК») можно в подузле Constraints.
Из скриншота видно, что в данной ситуации имя ограничения внешнего ключа (таблица Orders) имеет название «FK_Orders_To_Customers». Здесь для удаления внешнего подойдёт такое выражение:
Хотите знать про SQL Server больше? Добро пожаловать на курс «MS SQL Server Developer» в OTUS! Также вас может заинтересовать общий курс по работе с реляционными и нереляционными БД:
Как удалить ограничение в sql
Возможно, в какой-то момент мы захотим изменить уже имеющуюся таблицу. Например, добавить или удалить столбцы, изменить тип столбцов, добавить или удалить ограничения. То есть потребуется изменить определение таблицы. Для изменения таблиц используется выражение ALTER TABLE .
Общий формальный синтаксис команды выглядит следующим образом:
Таким образом, с помощью ALTER TABLE мы можем провернуть самые различные сценарии изменения таблицы. Рассмотрим некоторые из них.
Добавление нового столбца
Добавим в таблицу Customers новый столбец Address:
В данном случае столбец Address имеет тип NVARCHAR и для него определен атрибут NULL. Но что если нам надо добавить столбец, который не должен принимать значения NULL? Если в таблице есть данные, то следующая команда не будет выполнена:
Поэтому в данном случае решение состоит в установке значения по умолчанию через атрибут DEFAULT:
В этом случае, если в таблице уже есть данные, то для них для столбца Address будет добавлено значение «Неизвестно».
Удаление столбца
Удалим столбец Address из таблицы Customers:
Изменение типа столбца
Изменим в таблице Customers тип данных у столбца FirstName на NVARCHAR(200) :
Добавление ограничения CHECK
При добавлении ограничений SQL Server автоматически проверяет имеющиеся данные на соответствие добавляемым ограничениям. Если данные не соответствуют ограничениям, то такие ограничения не будут добавлены. Например, установим для столбца Age в таблице Customers ограничение Age > 21.
Если в таблице есть строки, в которых в столбце Age есть значения, несоответствующие этому ограничению, то sql-команда завершится с ошибкой. Чтобы избежать подобной проверки на соответствие и все таки добавить ограничение, несмотря на наличие несоответствующих ему данных, используется выражение WITH NOCHECK :
По умолчанию используется значение WITH CHECK , которое проверяет на соответствие ограничениям.
Добавление внешнего ключа
Пусть изначально в базе данных будут добавлены две таблицы, никак не связанные:
Добавим ограничение внешнего ключа к столбцу CustomerId таблицы Orders:
Добавление первичного ключа
Используя выше определенную таблицу Orders, добавим к ней первичный ключ для столбца Id:
Добавление ограничений с именами
При добавлении ограничений мы можем указать для них имя, используя оператор CONSTRAINT , после которого указывается имя ограничения:
Удаление ограничений
Для удаления ограничений необходимо знать их имя. Если мы точно не знаем имя ограничения, то его можно узнать через SQL Server Management Studio:
Раскрыв узел таблиц в подузле Keys можно увидеть названия ограничений первичного и внешних ключей. Названия ограничений внешних ключей начинаются с «FK». А в подузле Constraints можно найти все ограничения CHECK и DEFAULT. Названия ограничений CHECK начинаются с «CK», а ограничений DEFAULT — с «DF».
Например, как видно на скриншоте в моем случае имя ограничения внешнего ключа в таблице Orders называется «FK_Orders_To_Customers». Поэтому для удаления внешнего ключа я могу использовать следующее выражение:
How to drop a unique constraint from table column?
I have a table ‘users’ with ‘login’ column defined as:
Now I want to remove this unique constraint/index using SQL script. I found its name UQ_users_7D78A4E7 in my local database but I suppose it has a different name on another database.
What is the best way to drop this unique constraint? Or at least any.
16 Answers 16
if earlier constraints_name is not provided, it will have some default constraint_name, in pgAdmin 4 (pSql), try violating the constraint and you can see the constraint_name being violated in the error received, most probably same must be the case with other platforms or there are some articles available over web where constraint_name is extracted from certain tables where they are stored, not sure about this though. P.S : Can take reference from comments also
SKINDER, your code does not use column name. Correct script is:
This works mostly.
To drop a UNIQUE constraint, you don’t need the name of the constraint, just the list of columns that are included in the constraint.
The syntax would be:
You can use following script :
Use this SQL command to drop a unique constraint:
This statement works for me
I have stopped on the script like below (as I have only one non-clustered unique index in this table):
Has anyone comments if this solution is acceptable? Any pros and cons?
Expand to database name >> expand to table >> expand to keys >> copy the name of key then execute the below command:
Here UQ__test__3213E83EB607700F is the name of unique key which was created on a particular column on test table.
I would like to refer a previous question, Because I have faced same problem and solved by this solution. First of all a constraint is always built with a Hash value in it’s name. So problem is this HASH is varies in different Machine or Database. For example DF__Companies__IsGlo__6AB17FE4 here 6AB17FE4 is the hash value(8 bit). So I am referring a single script which will be fruitful to all
It will drop your default constraint. However if you want to create it again you can simply try this
Finally, just simply run a DROP command to drop the column.
I had the same problem. I’m using DB2. What I have done is a bit not too professional solution, but it works in every DBMS: