Как сделать значение по умолчанию в sql
Обновлено: 05.07.2024
Значения по умолчанию определяются для столбцов таблицы или для определенных пользователем типов данных для того, чтобы это значение записывалось в таблицу автоматически в том случае, если явно не указано никакого другого значения. Пользователь может также присоединить правило к полю таблицы или к типу данных для того, чтобы ограничить данные, которые могут быть использованы в этом поле или типе.
В этой главе рассматриваются следующие вопросы:
· Общий обзор правил и умолчаний;
· Как создавать и удалять умолчания;
· Как использовать по умолчанию неопределенные значения;
· Как создавать и удалять правила;
· Как получить информацию о правилах и умолчаниях.
Что такое правила и умолчания?
Значение по умолчанию - это значение, которое SQL Сервер вставляет в некоторое поле, если пользователь не указал явно никакого значения для этого поля. В системах управления базами данных правило указывает, какого вида данные допускаются для столбца таблицы или типа данных определенного пользователем. Правила и умолчания используются для достижения целостности данных в базе.
В системах управления реляционными базами данных каждый элемент базы, например, определенный столбец в определенной строке, должен иметь какое-либо значение, даже если это неопределенное значение NULL. Как уже говорилось в главе 7 “ Создание баз данных и таблиц”, в некоторые столбцы нельзя записывать неопределенное значение. В такие столбцы пользователь должен ввести определенное значение, либо по умолчанию это должен сделать SQL Сервер.
Значения по умолчанию, определенные пользователем, позволяют SQL Серверу автоматически записывать в поле некоторое значение, если для этого поля явно не было указано никакого значения, независимо от того допускает ли это поле неопределенные значения или нет. Например, можно определить по умолчанию значение “. ” или значение “заполнить позже”.
Правила обеспечивают поддержку целостности данных в том отношении, в каком ее нельзя обеспечить простым разделением данных на типы. Правила могут быть связаны с определенным столбцом таблицы, или с группой столбцов, или с типом данных пользователя.
Каждый раз, когда пользователь записывает в столбец таблицы данные с помощью операторов insert и update, SQL сервер проверяет, соответствуют ли они правилу, которое связано с этим столбцом. Данные, которые были записаны в табличный столбец до введения и присоединения к нему правила, не проверяются на соответствие этому правилу.
Замечание: Можно присоединить к столбцу данных числового типа правило для данных символьного типа, хотя это и не имеет смысла. Правила проверяются во время исполнения операторов insert и update, а не во время их создания.
В данной главе объясняется, как создавать правила и умолчания с помощью команд create default (создать умолчание) и create rule (создать правило), а также как связывать правила и умолчания к столбцу или к типу данных пользователя с помощью системных процедур sp_bindefault (присоединить умолчание), sp_bindrule (присоединить правило) , sp_unbindefault (отсоединить умолчание), sp_unbindrule (отсоединить правило).
Сравнение правил и умолчаний с условиями целостности
Вместо создания правил и умолчаний, можно сразу в операторе создания таблицы create table указать конструкцию default (умолчание) и опцию check (проверка), которые выполняют аналогичную роль. Однако, эти правила и умолчания будут действовать только для одной таблицы и не могут быть связаны со столбцами других таблиц или типами данных пользователя. Подробная информация об условиях сохранения целостности данных в отдельной таблице содержится в главе 7 “Создание баз данных и таблиц”.
Создание умолчаний
Умолчания могут создаваться и удаляться в любой момент, до или после введения данных в таблицу. Значения по умолчанию создаются командой create default (создать умолчание), а удаляются с командой drop default (удалить умолчание).
Умолчание может быть связано с определенным столбцом таблицы, с несколькими столбцами или со всеми столбцами таблиц базы данных, имеющими заданный пользователем тип данных. Для связывания умолчания со столбцом или типом данных используется системная процедура sp_bindefault (присоединить умолчание). Для отсоединения умолчания применяется процедура sp_unbindefault (отсоединить умолчание).
Ниже приведены некоторые условия, которые нужно проверить при создании и присоединении умолчаний:
· Необходимо убедиться, что размер данных в столбце достаточен для значения, задаваемого по умолчанию. Например, в столбце типа char(2) не допускается хранение 17-байтовой строки “Nobody knows yet”;
· Нужно внимательно относиться к определению значений по умолчанию, заданных для всего типа данных, и их переопределению для отдельных столбцов этого типа. Если вначале со столбцом связывается тип данных, определенный пользователем и имеющий свое значение по умолчанию, а затем для этого столбца указывается другое значение по умолчанию, то последнее значение заменит первое только в указанном столбце. Первое значение будет действовать по умолчанию во всех других столбцах, имеющих этот тип. Однако, если для столбца было определено другое значение, отличное от типового, то изменение типового значения уже не будет влиять на этот столбец. Этот вопрос будет более подробно обсуждаться в данной главе;
· Следует избегать конфликтов между умолчаниями и правилами. Нужно убедиться, что значение по умолчанию соответствует правилу, в противном случае, умолчание будет исключаться;
Синтаксис команды create default
Синтаксис команды create default выглядит следующим образом:
create default [имя_владельца.]название_умолчания
Названия умолчаний должны соответствовать правилам, установленным для идентификаторов. Умолчания можно создавать только в текущей базе данных.
Внутри одной базы данных названия умолчаний должны быть уникальными у каждого пользователя. Например, нельзя одному пользователю создать два умолчания с названием phonedflt. Однако, любой (пользователь) - “гость” может создать умолчание phonedflt, даже если умолчание с таким названием уже существует в этой базе данных, поскольку имена пользователей добавляются к названиям умолчаний, что и позволяет различать их.
Здесь будет показано, как создать значение по умолчанию “Oakland”, которое использовалось в столбце city таблицы friends_etc (создание этой таблицы рассматривалось в главе 7 “Создание баз данных и таблиц”) и которое возможно будет полезно для других столбцов или типов данных. Во время работы с этим примером можно использовать название любого другого города, которое имеет смысл для того контингента людей, сведения о которых планируется хранить в таблице. Для создания этого умолчания нужно выполнить команду:
create default citydflt
После слова as можно задать любую константу. Символьные константы и даты должны быть заключены в кавычки; для денежных, целых и числовых констант с плавающей точкой кавычки не обязательны. Двоичным данным должен предшествовать символ 0х, а денежным данным - символ доллара ($). Значение по умолчанию должно соответствовать типу данных, установленному для столбца. Например, нельзя использовать в качестве значения по умолчанию строку “none” для столбца числового типа, а число 0 (ноль) можно.
Часто значения по умолчанию создаются при создании самой таблицы. Однако, если во время одного сеанса работы нужно ввести большое количество строк с одинаковыми значениями в одном или нескольких столбцах, то удобнее было бы создать умолчание, которое присоединяется на время одного сеанса работы.
Присоединение умолчаний
После того, как умолчание было создано, следует использовать системную процедуру sp_bindefault (присоединить умолчание) для присоединения этого умолчания к столбцу или типу данных, определенному пользователем.
create default phonedflt as “UNKNOWN”
Здесь определено значение по умолчанию. Теперь необходимо присоединить это значение к соответствующему столбцу или к типу данных пользователя с помощью системной процедуры sp_bindefault.
sp_bindefault phonedflt, “authors.phone”
Это значение по умолчанию будет записываться в таблицу authors (авторы) только в том случае, если для столбца phone (телефон) не указано никакого значения. Таким образом, отсутствие данных в столбце не обязательно приводит к появлению в нем неопределенного значения.
Замечание: Чтобы в столбец по умолчанию записалось определенное значение необходимо выполнить команду insert или update со списком столбцов, в котором нет этого столбца.
Умолчание действует только для новых строк. Оно не оказывает обратного действия на уже существующие строки. Конечно, умолчание вступает в силу только тогда, когда не было указано никакого другого значения. Если пользователь указал другое значение, в том числе и неопределенное значение NULL, то умолчание не будет действовать.
Ниже показано, как присоединить умолчание citydflt к столбцу city таблицы friends_etc:
sp_bindefault citydflt, “friends_etc.city”
Заметим, что название таблицы и столбца заключаются в кавычки, поскольку они разделены знаком пунктуации, которым в данном случае является точка.
Замечание: Умолчания нельзя присоединить к системным типам данным, поскольку неясно какие значения потребуются различным пользователям. Также не допускается присоединение умолчаний к столбцам типа timestamp (моменты времени), поскольку SQL Сервер автоматически записывает значения в эти столбцы. Можно связывать умолчания со столбцами типа IDENTITY (счетчик) или с типами данных, определенными пользователем и имеющими свойство IDENTITY, но такие умолчания игнорируются. Каждый раз когда вводится новая строка без указания значения для счетчика, SQL Сервер записывает в этот столбец значение, которое на единицу больше предыдущего.
Если создать специальный тип данных для всех столбцов city каждой таблицы базы данных, то можно присоединить умолчание citydflt к этому типу данных и быть уверенным в том, что значение по умолчанию “Oakland” будет заменять значения в тех столбцах, где это необходимо. Например, если пользовательский тип данных называется citytype, то присоединить к этому типу данных умолчание citydflt можно следующим образом:
sp_bindefault citydflt, citytype
Параметр futureonly (только в будущем) может быть использован при связывании умолчания с пользовательским типом данных. Этот параметр предотвращает изменение существующих значений в столбцах этого типа, записанных по умолчанию, на новое значение. Параметр futureonly никогда не используется при связывании умолчания со столбцом таблицы. В следующем примере показано, как создать и присоединить новое значение по умолчанию “Berkeley” к типу данных citytype таким образом, чтобы оно действовало только для вновь создаваемых столбцов. Предыдущее значение по умолчанию “Oakland” будет по-прежнему находиться в уже существующих столбцах типа citytype.
create default newcitydflt as “Berkeley”
sp_bindefault newcitydflt, citytype, futureonly
Если большинство людей, чью имена находятся в таблице, живут в местности с одинаковым почтовым кодом, то можно ввести соответствующее значение по умолчанию, чтобы уменьшить время, необходимое для ввода данных. Ниже приведен соответствующий пример умолчания для жителей округа Oakland (Окленд) вместе с его присоединением:
create default zipdflt as “94609”
sp_bindefault zipdflt, “friends_etc.postalcode”
Полный синтаксис вызова системной процедуры sp_bindefault имеет следующий вид:
sp_bindefault название_умолчания, название_объекта [, futureonly]
Название_умолчания это название умолчания, указанное при его создании процедурой create default. Название_объекта это название столбца таблицы или пользовательского типа данных, с которым связывается данное умолчание. Если это название не имеет вида таблица.столбец, то оно воспринимается как название типа данных.
Для всех столбцов, имеющих заданный пользовательский тип данных, будет действовать умолчание, связанное с этим типом, если не выполняется одно из следующих условий:
· Используется третий необязательный параметр futureonly, который предотвращает изменение значений в уже существующих столбцах этого типа; или
· Изменено значение по умолчанию для конкретного столбца таблицы. В этом случае действует измененное умолчание.
Умолчания, связанные со столбцами, всегда имеют преимущество перед умолчаниями, связанными с типами данных. Присоединение значения по умолчанию к столбцу заменяет действие значения по умолчанию, связанного с типом данных этого столбца, но присоединение умолчаний к типу данных не заменяет действие значения по умолчанию, связанного со столбцом, имеющим этот тип данных. В следующей таблице отражено относительное старшинство операций присоединения значений по умолчанию к табличным столбцам и типам данных пользователя:
Таблица 12-1: Предшествование умолчаний
Прежнее умолчание связано с:
Новое умолчание связано с:
Пользовательским типом
Табличным столбцом
Пользовательским типом
Новое значение заменяет прежнее.
Нет изменений. Действует прежнее.
Табличным столбцом
Новое значение заменяет прежнее.
Новое значение заменяет прежнее.
Существующие столбцы пользовательского типа наследуют новые умолчания для этого типа, если для них специально не было определено другого значения по умолчанию или не был указан третий параметр futureonly. Новые столбцы, имеющие этот тип данных, всегда наследуют значение по умолчанию для этого типа.
Например, предположим, что пользователь создал таблицу foes со столбцом city, который имеет пользовательский тип citytype. Сначала тип citytype не имел значений по умолчанию. После создания умолчания citydflt, оно автоматически связывается со столбцом foes.city. Затем пользователь связывает другое умолчание newcitydflt с типом citytype. Хотя foes.city является столбцом типа citytype, новое умолчание не будет с ним связано, поскольку значение по умолчание для этого столбца было предварительно изменено.
Замечание: Значение по умолчанию нельзя связывать и использовать в одном и том же пакете. Процедура sp_bindefault не должна быть в том же пакете, где расположены операторы модификации данных, использующие по умолчанию это значение.
Отсоединение умолчаний
Отсоединение значения по умолчанию означает разрыв его связи с определенным столбцом или типом данных. Отсоединенное умолчание по-прежнему будет хранится в базе данных и может быть использовано в дальнейшем. Для удаления связи между умолчанием и столбцом или типом данных, используется системная процедура sp_unbindefault (отсоединить умолчание).
В следующем примере показано, как отсоединить значение по умолчанию от столбца city таблицы friends_etc:
execute sp_unbindefault “friends_etc.city”
После выполнения этой процедуры умолчание все еще будет существовать, однако оно уже не будет действовать для столбца city, поскольку оно было отсоединено от этого столбца.
Для отсоединения умолчания от пользовательского типа данных citytype нужно выполнить следующую команду:
Полный синтаксис вызова системной процедуры sp_unbindefault имееи следующий вид:
sp_unbindefault название_объекта [, futureonly ]
Если параметр название_объекта имеет вид отличный от таблица.столбец, то SQL Сервер интерпретирует его как название пользовательского типа данных. Если отсоединить умолчание от пользовательского типа данных, то оно будет действительно отсоединено от всех столбцов этого типа, за исключением следующих:
· Указан второй параметр futureonly (только в будущем), который предотвращает отсоединение умолчания от уже существующих столбцов этого типа, или
· Было указано новое значение по умолчанию для данного столбца.
Ниже приведен пример, илюстрирующий описанные выше условия:
1. Создан пользовательский тип nm;
2. Тип nm указан в операторах создания таблиц friends_etc (друзья) и enemies (враги), при определении столбцов friends_etc.pname, friends_etc.sname, и enemies.nickname;
3. Создано умолчание nmdflt и присоединено к типу nm;
4. Создано новое умолчание nastydflt и связано со столбцом enemies.nickname;
5. Если теперь отсоединить умолчание nmdflt от пользовательского типа nm, то отсоединенными будут только столбцы friends.pname и friends.sname. Поскольку исходное умолчание для столбца enemies.nickname было изменено, то отсоединение на него не повлияет, несмотря на то, что этот столбец имеет тип nm.
Удаление умолчаний
В следующем примере показано, как удалить умолчание citydflt из базы:
drop default citydflt
Полный синтаксис команды drop default выглядит следующим образом:
drop default [владелец.]название _умолчания
Умолчание может быть удалено только его владельцем.
Воздействие умолчаний на значение NULL
Следующая таблица показывает соотношение между существованием умолчания и определением столбца с опциями NULL или NOT NULL. Результаты указаны в таблице:
Мне нужно изменить значение по умолчанию для полей таблицы sql с 'none' на 'null', где типы данных - это char varchar longtext и т. Д. И я хочу сохранить значение по умолчанию, если оно не 'none'. И есть более 500 таблиц, которые мне нужно изменить. Есть ли способ сделать это? И я должен использовать PL? Есть ли другие способы сделать это? Спасибо заранее.
2 ответа
Сначала я выбрал все таблицы из базы данных, используя этот запрос:
И выбрал все столбцы, используя цикл:
Затем я запустил код: и это сработало. Спасибо всем, кто пытался мне помочь.
Я предполагаю, что вы хотите обновить none до NULL , а столбец имеет тип varchar
Вам также необходимо обновить схему таблицы, установить значение по умолчанию как NULL, используя изменение.
Приведу пример указания или изменения значения по умолчанию в MySQL столбце.
Посмотрим список таблиц в базе данных:
Посмотрим структуру интересующей нас таблицы:
Допустим колонка activate имеет тип date и значение по умолчанию 0000-00-00, а мы хотим сделать 3000-01-01, то выполним sql запрос:
Можно также удалить значение по умолчанию:
Или вернуть как было:
В строгом режиме MySQL может не дать установить значение 0000-00-00, поэтому можно временно отключить строгий режим:
У меня есть таблица, которая собирает формы, отправленные с нашего веб-сайта, но по какой-то причине, когда они создали таблицу, они не поместили метку времени в таблицу. Я хочу, чтобы он вводил точную дату и время, когда была введена запись.
Я знаю, что где-то там, но я не могу найти способ установить значение по умолчанию (как в Access, вы используете getNow() или Now() ), но я не знаю, где его поставить.
Для изменения существующего столбца в существующей таблице:
Ответ @steph - TheQ предполагает, что вы изменяете существующий столбец, что, как мне кажется, не так? Смотрите мой ответ для кода, чтобы добавить новый столбец с этим ограничением по умолчанию.
@TheQ - Отлично, я попробую. Вы были правы, у меня уже есть поле, но я хотел применить к нему исправление, а не создавать новое. Спасибо за все ваши ответы!
Если вы хотите использовать метку времени UTC вместо местного времени, вы можете использовать GETUTCDATE() вместо GETDATE()
Это также можно сделать через графический интерфейс SSMS.
- Поместите вашу таблицу в дизайн-представление (щелкните правой кнопкой мыши по таблице в проводнике объектов->Дизайн )
- Добавьте столбец в таблицу (или щелкните столбец, который вы хотите обновить, если он уже существует)
- В свойствах столбца введите значение (getdate()) по умолчанию или поле привязки, как показано ниже
В представлении SQL Server Design я попробовал, он говорит, что значение должно соответствовать одному из элементов в списке! так что я сделал это на задней стороне
В этой таблице в SQL Server укажите значение по умолчанию для этого столбца CURRENT_TIMESTAMP . Тип данных этого столбца может быть datetime или datetime2.
@Amine, TIMESTAMP - это двоичное значение, обычно использующее 6-8 байтов. Строковое представление - это просто представление, точно так же, как база данных не хранит целые числа в виде строк. (Если вы не поместите время в столбец VARCHAR, и горе тем, кто пытается.)
Отмечу, что CURRENT_TIMESTAMP возвращает datetime значение в местном часовом поясе компьютера. Этого следует избегать. Вместо этого используйте, SYSUTCDATETIME который возвращает datetime2 в UTC.
ALTER TABLE YourTable ADD CONSTRAINT DF_YourTable DEFAULT GETDATE() FOR YourColumn
При добавлении значений даты и времени по умолчанию в столбец всегда следует помнить о часовых поясах.
Скажем, например, что это datetime значение предназначено для указания того, когда участник присоединился к веб-сайту, и вы хотите, чтобы оно отображалось обратно пользователю, GETDATE() даст вам время сервера, поэтому может показывать расхождения, если пользователь находится в другой локали сервера.
Если вы планируете иметь дело с международными пользователями, в некоторых случаях лучше использовать GETUTCDATE () , которая:
Возвращает текущую системную метку базы данных в качестве значения даты и времени. Смещение часового пояса базы данных не включено. Это значение представляет текущее время UTC (Всемирное координированное время). Это значение получено из операционной системы компьютера, на котором работает экземпляр SQL Server.
При получении значений интерфейсное приложение / веб-сайт должны преобразовать это значение из времени UTC в языковой стандарт / язык пользователя, запрашивающего его.
неверный синтаксис рядом с ключевым словом 'SET'.
что я делаю не так?
Это будет работать в SQL Server:
не может использовать alter столбец для этого, используйте add вместо
выполнить команду:
скопируйте имя CONSTRAINT .
падение DEFAULT CONSTRAINT :
выполните команду ниже:
решение Hoodaticus было идеальным, спасибо, но мне также нужно было его повторно запустить и найти этот способ проверить, было ли это сделано.
- на момент создания таблицы
- изменить существующий столбец в существующую таблицу.
- во время создания таблицы / создания нового столбца.
- изменить существующий столбец к существующей таблице
- перечислите все существующие ограничения значений по умолчанию для столбцов.
- отбросьте существующее ограничение по умолчанию для столбца.
- добавить новое ограничение, значение по умолчанию для этого столбца:
просто найдено 3 простых шага, чтобы изменить уже существующий столбец, который был null до
как ответ Юка с проверкой, чтобы позволить скрипту выполняться более одного раза без ошибок. (меньше кода / пользовательских строк, чем при использовании information_schema.колонки)
если ограничение уже существует с именем по умолчанию:
Читайте также: