double arrow

Создание и использование умолчаний, ограничений и правил с помощью T-SQL

Умолчания, ограничения и правила - это необязательные атрибуты, которые мож­но определять по колонкам и таблицам базы данных. У молчания (зна­чения по умолчанию) - это значения, которые заносятся в определенную колонку. Ограничения (constraints) используются как способ идентифицирования допустимых значений для колонки (чтобы откло­нять недопустимые значения), а также как средство обеспечения целостности дан­ных в таблицах базы данных и между связанными таблицами. Ограничение только по одной колонке называется ограничением на значение (колонки):оно ограничивает значения только этой колонки. Ограничение, которое влияет на несколько колонок, называется ссылочным ограничением:в этом случае комбинация значений для колонок, указанных в данном ограничении, должна отвечать требованиям этого ограни­чения. Имеется пять типов ограничений: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY и CHECK.

Null-значение (null value) - это неизвестное значение, для которого применяется обозначение NULL. Null-значение в колонке обычно означает, что для данной строки этой колонки нет данных, потому что значение неизвестно, либо не имеет смысла, либо не задано или будет задано в будущем. Null-значения - это не пустые значения и не значения числа 0, их настоящие значения неизвестны (unknown), поэтому никакие два null-значения не являются равными.

Свойство IDENTITY. Когда вы создаете таблицу, вы можете задать одну из колонок как идентифицирующую колонку (identity column), добавив к определению колонки свойство IDENTITY. Если колонка создается со свойством IDENTITY, то SQL Server автоматически генерирует для этой колонки значение строки, рассчитываемое по начальному значе­нию и значению приращения. Начальное значениеявляется значением идентификации для первой строки, вставленной в таблицу. Приращение - это величина, на которую SQL Server увеличивает значение идентификации для последовательно вводимых строк. Каждый раз при вводе стро­ки SQL Server присваивает текущее значение идентификации элементу данных в колонке идентификации, вводимому в новую строку. Следующая введенная строка получит значение идентификации, большее, чем текущее максимальное значение идентификации на величину приращения. Идентифицирующие колонки обычно применяются в ограничениях первичного ключа в таблицах, благодаря которым возможна уникальная идентификация строк. Например, если вы зададите IDENTITY(1, 10), то значение идентифицирующей колонки для первой введенной строки будет равно 1, для второй строки будет равно 10, для третьей строки - 20, и т.д. Если начальное значение или приращение не задать, то для них будут применяться значения по умолчанию, равные 1 и 1. Идентифицирующие ко­лонки не могут содержать значения по умолчанию и для них не разрешено приме­нение null-значений. В каждой из таблиц может иметься только одна идентифици­рующая колонка.

По умолчанию, непосредственный ввод данных в идентифицирующие колонки невозможен и они не могут быть изменены. Если вы хотите повторить ввод удален­ной строки и хотите сохранить старое идентифицирующее значение этой строки, то вы можете преодолеть стандартные настройки, применив такой оператор:

SET IDENTITY INSERT имя.таблицы ON

При помощи этого оператора можно вставить строку и назначить нужное вам зна­чение идентифицирующей колонки. Закончив ввод строки, нужно отменить воз­можность вставки в идентифицирующую колонку при помощи такого оператора:

SET IDENTITYINSERT имя_таблицы OFF

После этого, SQL Server, в качестве начального значения, применяемого при добавлении следующих строк, возьмет самое большое значение из данной колонки.

Создание умолчания для колонки с помощью оператора CREATE TABLE является предпочтительным, стандартным методом. Следующий оператор создает в базе дан­ных MyDB таблицу, содержащую умолчания для обеих колонок, - columnA (типа char) и columnB (типа int):

CREATE TABLE MyTable

(columnA char(15) NULL DEFAULT "шт",

columnB int NULL DEFAULT 0)

Значение по умолчанию «шт» для колонки columnA совместимо с типом данных char этой колонки, и значение по умолчанию 0 для колонки columnB совместимо с типом данных int. Если при встав­ке новой строки в таблицу не указывается конкретное значение для одной или обеих колонок, то используется соответствующее значение по умолчанию. Поэтому един­ственным способом присваивания этим колонкам значения NULL является явная вставка NULL. Null-значения допустимы, поскольку для обеих колонок указан атрибут NULL. Если бы колонки были определены как NOT NULL, то вы не могли бы выполнять явную вставку значения NULL.

Ограничение PRIMARY KEY используется, чтобы задать первичный ключтаблицы, представляемый колонкой или набором колонок, уникальным образом идентифицирующих строку таблицы. Поскольку первичный ключ идентифицирует строку, соответствующая колонка никогда не содержит значения NULL. Если вы определяете ограничение PRIMARY KEY по набору коло­нок, это ограничение указывает, что комбинация значений этих колонок должна быть уникальной для каждой строки. Ограничение PRIMARY KEY не допускает дублированных значений. Если ограничение PRIMARY KEY присва­ивается колонке или набору колонок, то по этой колонке или колонкам первичного ключа автоматически создается уникальный индекс. Таблица может иметь только одно ограничение PRIMARY KEY. Колонка с атри­бутом IDENTITY хорошо подходит для первичного ключа. Следующий оператор T-SQL представляет один из способов задания колонки SSN как первичного ключа, когда вы определяете таблицу.

CREATE TABLE customer

(first_name char(20) NOT NULL,

midinit char(1) NULL,

last_name char(20) NOT NULL,

SSN char(11) PRIMARY KEY,

Cust_phone char(10) NULL)

Используя альтернативный способ, вы можете присвоить имя этому ограниче­нию, добавив ключевое слово CONSTRAINT. Чтобы присвоить имя PK_SSN ваше­му ограничению PRIMARY KEY, используйте следующий оператор:

CREATE TABLE customer

(first_name char(20) NOT NULL,

midinit char(1) NULL,

last_name char(20) NOT NULL,

SSN char(11) CONSTRAINT PK_SSN PRIMARY KEY,

cust_phone char(10) NULL)

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

CREATE TABLE customer

(first_name char(20) NOT NULL,

midinit char(1) NULL,

last_name char(20) NOT NULL,

cust_phone char(10) NULL,

CONSTRAINT PK_SSN PRIMARY KEY (SSN))

Ограничение UNIQUE обеспечивает, что в колонке или наборе колонок не будут допускаться дублированные значения; иными словами, обеспечивается уникаль­ность значений в этой колонке или наборе колонок. Для поддержки этой уникаль­ности SQL Server создает по умолчанию уникальный индекс по колонке или колонкам, указанным в ограничении UNIQUE. Ограничение UNIQUE можно использовать для любой колонки, которая не яв­ляется частью ограничения PRIMARY KEY. Ограничение UNIQUE можно использовать для колонок, в которых разрешены null-значения, в то время как ограничения PRIMARY KEY нельзя использовать для таких колонок. На колонку с ограничением UNIQUE мо­жет ссылаться ограничение FOREIGN KEY. По одной таблице можно задавать несколько ограничений UNIQUE, пока общее число индексов для этой таблицы не превышает 250 индексов.

Чтобы создать ограничение UNIQUE по таблице с помощью T-SQL, используй­те оператор CREATE TABLE. Например, следующий оператор создает таблицу customer (покупатель) с ограничением UNIQUE по колонке SSN в виде индекса:

CREATE TABLE customer

(first_name char(20) NOT NULL,

midinit char(1) NULL,

last_name char(20) NOT NULL,

SSN char(11) NOT NULL UNIQUE CLUSTERED,

cust_phone char(10) NULL)

Ограничение CHECK используется, чтобы ограничить множество допустимых для колонки значений определенными значениями. Значения, которые используются при вставке в колонку или обновлении колонки, проверяются на истинность (зна­чение TRUE) указанного в ограничении булева условия поиска. Например, если бы нам нужно было ограничить диапазон возможных значений, допустимых для ко­лонки price (цена) таблицы items, величинами от $0,01 до $500,00, то мы использо­вали бы следующий оператор:

CREATE TABLE items

(itemname char(15) NOT NULL,

itemid smallint NOT NULL IDENTITY (1,1),

price smallmoney NULL,

item_desc varchar(30) NOT NULL DEFAULT "none",

CONSTRAINT PK_ itemid PRIMARY KEY (itemid),

CONSTRAINT CK_price CHECK (price >=0.01 AND price <= 500.00))

Создание и модифицирование ограничений с помощью Management Studio

Чтобы создать таблицу, раскройте в ле­вой панели Management Studio папку сервера и папку базы данных, щелкните правой кнопкой мыши на папке Tables (Таблицы) и затем выберите из контекстного меню пункт New Table (Создать таблицу). Чтобы появилось окно Design Table для какой-либо существующей таблицы, сначала щелкните на папке Tables, щелкните правой кнопкой мыши на имени этой таблицы в правой панели и затем выберите из кон­текстного меню пункт Design Table.

Чтобы указать, можно или нельзя использовать null-значения в какой-либо колон­ке, просто установите или сбросьте соответствующий флажок в колонке Allow Nulls (Разрешить null-значения) окна Design Table. Вы можете задать этот параметр при создании таблицы или при ее модифицировании.

Чтобы создать или модифицировать ограничение UNIQUE с помощью Management Studio, выполните следующие шаги:

1. В панели инструментов окна Design Table щелкните на кнопке Indexes/Keys.

  1. Появится следующее окно, позволяющее создавать, редактировать и удалять ограничения, связанные с первичными и уникальными ключами

Выберите имена колонок, которые хо­тите включить в ограничение и определите для него свойства.

Вы можете задать ограничение PRIMARY KEY по одной колонке или по несколь­ким колонкам. Эта колонка или колонки должны уникальным образом идентифи­цировать каждую строку таблицы. Чтобы задать ограничение PRIMARY KEY, вы­полните следующие шаги:

  1. В окне Design Table выберите колонку, щелкнув на одной из ячеек в ее строке. (Вы можете выбрать несколько колонок, удерживая клавишу Ctrl и щелкая на серых ячейках слева от имен колонок.)

  1. Щелкните правой кнопкой мыши на одной из выбранных колонок и выберите из контекстного меню пункт Set Primary Key (Задать первичный ключ). Слева от колонок, которые вы задали для первичного ключа, появится изображение не­большого ключа.
  2. Если вам нужно переместить ограничение PRIMARY KEY в другую колонку, просто задайте эту новую колонку как первичный ключ. От вас не требуется уда­лить сначала явным образом исходный первичный ключ - SQL Server удалит и снова создаст для вас индекс PRIMARY KEY. Вы можете также модифицировать индекс PRIMARY KEY в окне Properties. Ваши измене­ния начнут действовать после того, как вы сохраните вашу работу, щелкнув на кнопке Save в панели инструментов.

Чтобы создать ограничение CHECK с помощью окна Design Table, откройте это окно для таблицы, с которой хотите работать, и выполните следующие шаги.

1. Щелкните правой кнопкой мыши на окне Design Table и выберите из контекст­ного меню пункт Properties, чтобы появилось окно Properties. Щелкните на вкладке Check Constraints (Ограничения Check и щелкните на кнопке New для таблицы.

2. Далее введите выражение, которое хотите использовать для проверки данных, которые будут вводиться или обновляться.

3. Обратите внимание на три флажка внизу этого окна. Установка флажка Check existing data on creation означает, что существующие данные таблицы будут проверяться на соответствие ограничению CHECK и если они не согласуются, то ограничение не будет создано. Установка флажка Enforce constraint for replication (Проверять ограничение для репликации) означает, что данное ограничение бу­дет проверяться при репликации данных. Установка флажка Enforce constraint for INSERTS and UPDATEs просто означает, что ограничение CHECK будет активизировано. Если не установить этот флажок, то данное ограничение будет создано, но оно не будет активизировано, т.е. не будет оказывать никакого влияния.

4. Щелкните на кнопке Close и затем щелкните на кнопке Save, чтобы сохранить новое ограничение. Чтобы модифицировать ограничение CHECK, используйте вкладку Check Constraint для изменения имени (Constraint name), выражения (Constraint expression) и флажков.

Вы можете также использовать вкладку Check Constraints для удаления ограни­чения CHECK, выбрав в списке Selected Constraint (Выбранное ограничение) имя ограничения, которое хотите удалить, и щелкнув на кнопке Delete.

Лабораторная работа рассчитана на 3 часа аудиторных занятий и состоит в изучении теоретического материала и получении практических навыков по определению таблиц базы данных, созданию правил, ограничений, умолчаний, пользовательских типов данных, индексов. Сдача лабораторной работы заключается в ответе на контрольные вопросы и демонстрации индивидуального задания на ПК.

Содержание отчета:

  1. Название и цель работы
  2. Индивидуальное задание
  3. Скрипты на создание пользовательского типа данных и таблиц БД, в соответствии с индивидуальным заданием

Контрольные вопросы

  1. Перечень поддерживаемых типов данных?
  2. Пользовательский тип данных. Назначение и способы создания?
  3. Способы определения таблиц?
  4. Назначение умолчаний, правил и ограничений?
  5. Перечень поддерживаемых умолчаний, ограничений, правил?
  6. Способы назначений умолчаний, ограничений, правил
  7. Что представляют собой Null-значение?
  8. Свойство колонки IDENTITY?

Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями:  



Сейчас читают про: