Sql запрос создания таблицы примеры. Как создать базу данных в среде SQL Server
Создание и удаление таблиц в ms sql server
Все данные в БД sql server хранятся в таблицах.
Таблицы состоят из колонок, объединяющих значения одного типа, и строк — записей в таблице. В одной БД может быть до 2 миллиардов таблиц, в таблице — 1024 колонки, в одной строке (записи) — 8060 байтов.
sql server поддерживает следующие типы данных:
Тип данных Обозначение Размер, байт
Бинарные данные binary
varbinary[(n)] 1-8000
Символы char[(n)]
varchar[(n)] 1-8000
(до 8000 символов)
Символы unicode nchar[(n)]
nvarchar[(n)] 1-8000
(до 4000 символов)
Дата и время datetime 8
smalldatetime 4
Точные числа decimal[(p[,s])]
numeric[(p[,s])] 5-17
Приблизительные числа float[(n)] real 4-8 4
Глобальный идентификатор uniqueidentifier 16
Целые числа int smallint, tinyint 4 2, 1
Денежки money, smallmoney 8, 4
Специальные bit, cursor,
sysname, timestamp 1, 0-8
Текст и изображение text, image 0-2 Гб
Текст unicode ntext 0-2 Гб
Таблицы можно создавать с помощью оператора create table языка transact-sql, а также с помощью enterprise manager. Рассмотрим сначала как это делается с помощью transact-sql.
Содание таблиц с помощью create table
Для создания таблиц применяется оператор create table.
Вот как выглядит упрощенный синтаксис этого оператора:
create table table_name
(column_name data_type
[,…n])
Например:
create table member (member_no int not null, lastname char(50) not null, firstname char(50) not null, photo image null)
Этим оператором создается таблица member, состоящая из четырех колонок:
member_no — имеет тип int, значения null не допускаются
lastname — имеет тип char(50) — 50 символов, значения null не допускаются
firstname — аналогично lastname
photo — имеет тип image (изображение), допускается значение null
Примечание
null — специальное обозначение того, что элемент данных не имеет значения. В описании типа колонки указывается, что элементы данных могут быть неинициализированы. При указании not null — "пустые" значения не допускаются. Если при вставке записи пропустить значение для такой колонки, вставка не произойдет, и sql server сгенерирует ошибку.
Попробуйте выполнить эту команду. Запустите query analyzer. Соединитесь с Вашим сервером. Из списка БД выберите sqlstep. Скопируйте в окно команд команду создания таблицы и выполните ее. (Если не забыли, надо нажать f5 или ctrl-e).
Чтобы точно удостовериться, в том, что таблица была создана, наберите команду:
sp_help member
Выделите ее (как в обычном редакторе) и снова нажмите f5. В окно результатов будет выведена информация о таблице member.
На заметку!
sp_help — системная процедура, которая возвращает информацию об объектах БД (таблицах, хранимых процедурах и пр.).
Формат вызова таков:
sp_help <имя таблицы>
Удалить таблицу проще простого. Там же, в запросчике (так у нас называют query analyzer), наберите:
drop table member
Выделите эту строку и нажмите f5. Таблица будет удалена, о чем Вас и уведомят. В нашем случае эта процедура проста. На самом деле в большой БД просто так удалить таблицу не получится, поскольку она будет связана с другими таблицами, и для удаления потребуется эти связи оборвать. Как это сделать см. следующие шаги.
Как создать таблицу с помощью sql server enterprise manager
Раскройте последовательно: sql server group, <Ваш sql server>, databases. Выберите БД (sqlstepbystep, я думаю:), нажмите правую кнопку мыши и выберите в контекстном меню пункт "new", а затем пункт "table…". Первым делом Вас спросят имя таблицы. Введите его и нажмите enter. На экране появится окно, в котором можно вводить:
имена колонок, тип, длину, размерность, точность (эти три колонки блокируются в зависимости от типа), флаг разрешения null, значение по умолчанию. Последние три колонки, пока не представляют интереса.
Введите названия колонок, их тип и длину также как в примере выше. Нажмите на иконку с дискетой для сохранения таблицы и можете закрыть окно. Раскройте вашу БД, щелкните на категории "tables" и в списке таблиц увидите только что введенную таблицу. Для ее удаления выделите ее в списке, нажмите правую кнопку мыши и в контекстном меню выберите "delete". Таблица будет удалена.
Мы изучили как создаются и удалются таблицы. Следующий наш шаг — создание полноценной БД, на примере которой мы будем изучать:
что такое реляционная целостность БД и как она обеспечивается в sql server
как модифицировать данные в таблицах (операторы insert, update, delete, select)
как использовать хранимые процедуры и триггеры
Хорошо Плохо
Последнее обновление: 09.07.2017
Для создания таблиц применяется команда CREATE TABLE . С этой командой можно использовать ряд операторов, которые определяют столбцы таблицы и их атрибуты. И кроме того, можно использовать ряд операторов, которые определяют свойства таблицы в целом. Одна база данных может содержать до 2 миллиардов таблиц.
Общий синтаксис создания таблицы выглядит следующим образом:
CREATE TABLE название_таблицы (название_столбца1 тип_данных атрибуты_столбца1, название_столбца2 тип_данных атрибуты_столбца2, ................................................ название_столбцаN тип_данных атрибуты_столбцаN, атрибуты_таблицы)
После команды CREATE TABLE идет название создаваемой таблицы. Имя таблицы выполняет роль ее идентификатора в базе данных, поэтому оно должно быть уникальным. Имя должно иметь длину не больше 128 символов. Имя может состоять из алфавитно-цифровых символов, а также символов $ и знака подчеркивания. Причем первым символом должна быть буква или знак подчеркивания.
Имя объекта не может включать пробелы и не может представлять одно из ключевых слов языка Transact-SQL. Если идентификатор все же содержит пробельные символы, то его следует заключать в кавычки. Если необходимо в качестве имени использовать ключевые слова, то эти слова помещаются в квадратные скобки.
Примеры корректных идентификаторов:
Users tags$345 users_accounts "users accounts"
После имени таблицы в скобках указываются параметры всех столбцов и в самом конце атрибуты, которые относятся ко всей таблице. Атрибуты столбцов и атрибуты таблицы являются необязательными компонентами, и их можно не указывать.
В самом просто виде команда CREATE TABLE должна содержать как минимум имя таблицы, имена и типы столбцов.
Таблица может содержать от 1 до 1024 столбцов. Каждый столбец должен иметь уникальное в рамках текущей таблицы имя, и ему должен быть назначен тип данных.
Например, определение простейшей таблицы Customers:
CREATE TABLE Customers (Id INT, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20))
В данном случае в таблице Customers определяются шесть столбцов: Id, FirstName, LastName, Age, Email, Phone. Первые два столбца представляют идентификатор клиента и его возраст и имеют тип INT , то есть будут хранить числовые значения. Следующие два столбца представляют имя и фамилию клиента и имеют тип NVARCHAR(20) , то есть представляют строку UNICODE длиной не более 20 символов. Последние два столбца Email и Phone представляют адрес электронной почты и телефон клиента и имеют тип VARCHAR(30/20) - они также хранят строку, но не в кодировке UNICODE.
Создание таблицы в SQL Management Studio
Создадим простую таблицу на сервере. Для этого откроем SQL Server Management Studio и нажмем правой кнопкой мыши на название сервера. В появившемся контекстном меню выберем пункт New Query .
Таблица создается в рамках текущей базы данных. Если мы запускаем окно редактора SQL как это сделано выше - из под названия сервера, то база данных по умолчанию не установлена. И для ее установки необходимо применить команду USE , после которой указывается имя базы данных. Поэтому введем в поле редактора SQL-команд следующие выражения:
USE usersdb; CREATE TABLE Customers (Id INT, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20));
То есть в базу данных добавляется таблица Customers, которая была рассмотрена ранее.
Также можно открыть редактор из под базы данных, также нажав на нее правой кнопкой мыши и выбрав New Query:
В этом случае в качестве текущей будет рассматриваться та база данных, из под которой был открыт редактор, и дополнительно ее устанавливать с помощью команды USE не потребуется.
Удаление таблиц
Для удаления таблиц используется команда DROP TABLE , которая имеет следующий синтаксис:
DROP TABLE table1 [, table2, ...]
Например, удаление таблицы Customers:
DROP TABLE Customers
Переименование таблицы
Для переименования таблиц применяется системная хранимая процедура "sp_rename". Например, переименование таблицы Users в UserAccounts в базе данных usersdb:
USE usersdb; EXEC sp_rename "Users", "UserAccounts";
87Параметр table_name - имя создаваемой базовой таблицы. Максимальное количество таблиц, которое может содержать одна база данных, ограничивается количеством объектов базы данных, число которых не может быть более 2 миллиардов, включая таблицы, представления, хранимые процедуры, триггеры и ограничения. В параметрах col_name1, col_name2, ... указываются имена столбцов таблицы, а в параметрах type1, type2, ... - типы данных соответствующих столбцов.
Имя объекта базы данных может обычно состоять из четырех частей, в форме:
]]object_name
Здесь object_name - это имя объекта базы данных, schema_name - имя схемы, к которой принадлежит объект, а server_name и db_name - имена сервера и базы данных, к которым принадлежит объект. Имена таблиц, сгруппированные с именем схемы, должны быть однозначными в рамках базы данных. Подобным образом имена столбцов должны быть однозначными в рамках таблицы.
Рассмотрим теперь ограничение, связанное с присутствием или отсутствием значений NULL в столбце. Если для столбца не указано, что значения NULL разрешены (NOT NULL), то данный столбец не может содержать значения NULL, и при попытке вставить такое значение система возвратит сообщение об ошибке.
Как уже упоминалось, объект базы данных (в данном случае таблица) всегда создается в схеме базы данных. Пользователь может создавать таблицы только в такой схеме, для которой у него есть полномочия на выполнение инструкции ALTER. Любой пользователь с ролью sysadmin, db_ddladmin или db_owner может создавать таблицы в любой схеме.
Создатель таблицы не обязательно должен быть ее владельцем. Это означает, что один пользователь может создавать таблицы, которые принадлежат другим пользователям. Подобным образом таблица, создаваемая с помощью инструкции CREATE TABLE, не обязательно должна принадлежать к текущей базе данных, если в префиксе имени таблицы указать другую (существующую) базу данных и имя схемы.
Схема, к которой принадлежит таблица, может иметь два возможных имени по умолчанию. Если таблица указывается без явного имени схемы, то система выполняет поиск имени таблицы в соответствующей схеме по умолчанию. Если имя объекта найти в схеме по умолчанию не удается, то система выполняет поиск в схеме dbo. Имена таблиц всегда следует указывать вместе с именем соответствующей схемы. Это позволит избежать возможных неопределенностей.
В примере ниже показано создание всех таблиц базы данных SampleDb. (База данных SampleDb должна быть установлена в качестве текущей базы данных.)
USE SampleDb; CREATE TABLE Department (Number CHAR (4) NOT NULL, DepartmentName NCHAR (40) NOT NULL, Location NCHAR (40) NULL); CREATE TABLE . ( CHAR (4) NOT NULL, NCHAR (15) NOT NULL, FLOAT (53) NULL); CREATE TABLE dbo.Employee (Id INT NOT NULL, FirstName NCHAR (20) NOT NULL, LastName NCHAR (20) NOT NULL, DepartamentNumber CHAR (4) NULL); CREATE TABLE dbo.Works_on (EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, Job NCHAR (15) NULL, EnterDate DATE NULL);
Кроме типа данных и наличия значения NULL, в спецификации столбца можно указать следующие параметры:
предложение DEFAULT;
свойство IDENTITY.
Предложение DEFAULT в спецификации столбца указывает значение столбца по умолчанию, т.е. когда в таблицу вставляется новая строка, ячейка этого столбца будет содержать указанное значение, которое останется в ячейке, если в нее не будет введено другое значение. В качестве значения по умолчанию можно использовать константу, например одну из системных функций, таких как, USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, CURRENT_TIMESTAMP и NULL.
Столбец идентификаторов, создаваемый указанием свойства IDENTITY , может иметь только целочисленные значения, которые системой присваиваются обычно неявно. Каждое следующее значение, вставляемое в такой столбец, вычисляется, увеличивая последнее, вставленное в этот столбец, значение. Поэтому определение столбца со свойством IDENTITY содержит (явно или неявно) начальное значение и шаг инкремента (такой столбец еще называют столбцом с автоинкрементом).
Ниже показан пример использования этих инструкций:
USE SampleDb; CREATE TABLE UserInfo (-- Для столбца Id будет использоваться автоинкремент IDENTITY(10,5), -- т.е. при вставке данных первому элементу будет присвоено -- значение 10, второму 15, третьему 20 и т.д. Id INT NOT NULL PRIMARY KEY IDENTITY (10,5), Login VARCHAR(40) NOT NULL, -- Для поля BirthDate будет указана дата по умолчанию -- (если это поле не задано явно при вставке данных) BirthDate DATETIME DEFAULT (-- По умолчанию -30 лет от текущей даты DATEADD(year, -30, GETDATE())))
Инструкция CREATE TABLE и ограничения декларативной целостности
Одной из самых важных особенностей, которую должна предоставлять СУБД, является способ обеспечения целостности данных. Ограничения, которые используются для проверки данных при их модификации или вставке, называются ограничениями для обеспечения целостности (integrity CONSTRAINTs) . Обеспечение целостности данных может осуществляться пользователем в прикладной программе или же системой управления базами данных. Наиболее важными преимуществами предоставления ограничений целостности системой управления базами данных являются следующие:
повышается надежность данных;
сокращается время на программирование;
упрощается техническое обслуживание.
Определение ограничений для обеспечения целостности посредством СУБД повышает надежность данных, поскольку устраняется возможность, что программист прикладного приложения может забыть реализовать их. Если ограничения целостности предоставляются прикладными программами, то все приложения, затрагиваемые этими ограничениями, должны содержать соответствующий код. Если код отсутствует хоть в одном приложении, то целостность данных будет поставлена под сомнение.
Если ограничения для обеспечения целостности не предоставляются системой управления базами данных, то их необходимо определить в каждой программе приложения, которая использует данные, включенные в это ограничение. В противоположность этому, если ограничения для обеспечения целостности предоставляются системой управления базами данных, то их требуется определить только один раз. Кроме этого, код для ограничений, предоставляемых приложениями, обычно более сложный, чем в случае таких же ограничений, предоставляемых СУБД.
Если ограничения для обеспечения целостности предоставляются СУБД, то в случае изменений ограничений, соответствующие изменения в коде необходимо реализовать только один раз - в системе управления базами данных. А если ограничения предоставляются приложениями, то модификацию для отражения изменений в ограничениях необходимо выполнить в каждом из этих приложений.
Системами управления базами данных предоставляются два типа ограничений для обеспечения целостности:
декларативные ограничения для обеспечения целостности;
процедурные ограничения для обеспечения целостности, реализуемые посредством триггеров.
Декларативные ограничения определяются с помощью инструкций языка DDL CREATE TABLE и ALTER TABLE. Эти ограничения могут быть уровня столбцов или уровня таблицы. Ограничения уровня столбцов определяются наряду с типом данных и другими свойствами столбца в объявлении столбца, тогда как ограничения уровня таблицы всегда определяются в конце инструкции CREATE TABLE или ALTER TABLE после определения всех столбцов.
Между ограничениями уровня столбцов и ограничениями уровня таблицы есть лишь одно различие: ограничения уровня столбцов можно применять только к одному столбцу, в то время как ограничения уровня таблицы могут охватывать больше, чем один столбец таблицы.
Каждому декларативному ограничению присваивается имя. Это имя может быть присвоено явно посредством использования опции CONSTRAINT в инструкции CREATE TABLE или ALTER TABLE. Если опция CONSTRAINT не указывается, то имя ограничению присваивается неявно компонентом Database Engine. Настоятельно рекомендуется использовать явные имена ограничений, поскольку это может значительно улучшить поиск этих ограничений.
Декларативные ограничения можно сгруппировать в следующие категории:
предложение DEFAULT;
предложение UNIQUE;
предложение PRIMARY KEY;
предложение CHECK;
ссылочная целостность и предложение FOREIGN KEY.
Использование предложения DEFAULT для определения ограничения по умолчанию было показано ранее. Все другие ограничения рассматриваются в последующих разделах.
Предложение UNIQUE
Иногда несколько столбцов или группа столбцов таблицы имеет уникальные значения, что позволяет использовать их в качестве первичного ключа. Столбцы или группы столбцов, которые можно использовать в качестве первичного ключа, называются потенциальными ключами (candidate key) . Каждый потенциальный ключ определяется, используя предложение UNIQUE в инструкции CREATE TABLE или ALTER TABLE. Синтаксис предложения UNIQUE следующий:
Опция CONSTRAINT в предложении UNIQUE присваивает явное имя потенциальному ключу. Опция CLUSTERED или NONCLUSTERED связана с тем обстоятельством, что компонент Database Engine создает индекс для каждого потенциального ключа таблицы. Этот индекс может быть кластеризованным, когда физический порядок строк определяется посредством индексированного порядка значений столбца. Если порядок строк не указывается, индекс является некластеризованным. По умолчанию применяется опция NONCLUSTERED. Параметр col_name1 обозначает имя столбца, который создает потенциальный ключ. (Потенциальный ключ может иметь до 16 столбцов.)
Применение предложения UNIQUE показано в примере ниже. (Прежде чем выполнять этот пример, в базе данных SampleDb нужно удалить таблицу Projects, используя для этого инструкцию DROP TABLE Projects.)
USE SampleDb; CREATE TABLE Projects (Number CHAR(4) DEFAULT "p1", ProjectName NCHAR (15) NOT NULL, Budget FLOAT (53) NULL, CONSTRAINT unique_number UNIQUE (Number));
Каждое значение столбца Number таблицы Projects является уникальным, включая значение NULL. (Точно так же, как и для любого другого значения с ограничением UNIQUE, если значения NULL разрешены для соответствующего столбца, этот столбец может содержать не более одной строки со значением NULL.) Попытка вставить в столбец Number уже имеющееся в нем значение будет неуспешной, т.к. система не примет его. Явное имя ограничения, определяемого в примере - unique_number.
Предложение PRIMARY KEY
Первичным ключом таблицы является столбец или группа столбцов, значения которого разные в каждой строке. Каждый первичный ключ определяется, используя предложение PRIMARY KEY в инструкции CREATE TABLE или ALTER TABLE. Синтаксис предложения PRIMARY KEY следующий:
Все параметры предложения PRIMARY KEY имеют такие же значения, как и соответствующие одноименные параметры предложения UNIQUE. Но в отличие от столбца UNIQUE, столбец PRIMARY KEY не разрешает значений NULL и имеет значение по умолчанию CLUSTERED.
В примере ниже показано объявление первичного ключа для таблицы Employee базы данных SampleDb. Прежде чем выполнять этот пример, в базе данных SampleDb нужно удалить таблицу Employee, используя для этого инструкцию DROP TABLE Employee.
USE SampleDb; CREATE TABLE Employee (Id INT NOT NULL, FirstName NCHAR (20) NOT NULL, LastName NCHAR (20) NOT NULL, DepartamentNumber CHAR (4) NULL, CONSTRAINT primary_id PRIMARY KEY (Id));
В результате выполнения этого кода снова создается таблица Employee, в которой определен первичный ключ. Первичный ключ таблицы определяется посредством декларативного ограничения для обеспечения целостности с именем primary_id. Это ограничение для обеспечения целостности является ограничением уровня таблицы, поскольку оно указывается после определения всех столбцов таблицы Employee.
Следующий пример эквивалентен предыдущему, за исключением того, что первичный ключ таблицы Employee определяется как ограничение уровня столбца.
USE SampleDb; DROP TABLE Employee; CREATE TABLE Employee (Id INT NOT NULL CONSTRAINT primary_id PRIMARY KEY, FirstName NCHAR (20) NOT NULL, LastName NCHAR (20) NOT NULL, DepartamentNumber CHAR (4) NULL);
В примере предложение PRIMARY KEY принадлежит к объявлению соответствующего столбца, наряду с объявлением его типа данных и свойства содержать значения NULL. По этой причине это ограничение называется ограничением на уровне столбца .
Предложение CHECK
Проверочное ограничение (CHECK CONSTRAINT) определяет условия для вставляемых в столбец данных. Каждая вставляемая в таблицу строка или каждое значение, которым обновляется значение столбца, должно отвечать этим условиям. Проверочные ограничения устанавливаются посредством предложения CHECK , определяемого в инструкции CREATE TABLE или ALTER TABLE. Синтаксис предложения CHECK следующий:
Параметр expression должен иметь логическое значение (true или false) и может ссылаться на любые столбцы в текущей таблице (или только на текущий столбец, если определен как ограничение уровня столбца), но не на другие таблицы. Предложение CHECK не применяется принудительно при репликации данных, если присутствует параметр NOT FOR REPLICATION. (При репликации база данных, или ее часть, хранится в нескольких местах. С помощью репликации можно повысить уровень доступности данных.)
В примере ниже показано применение предложения CHECK:
USE SampleDb; CREATE TABLE Customer (CustomerId INTEGER NOT NULL, CustomerRole VARCHAR(100) NULL, CHECK (CustomerRole IN ("admin", "moderator", "user")));
Создаваемая в примере таблица Customer включает столбец CustomerRole, содержащий соответствующее проверочное ограничение. При вставке нового значения, отличающегося от значений в наборе ("admin", "moderator", "user"), или при попытке изменения существующего значения на значение, отличающегося от этих значений, система управления базой данных возвращает сообщение об ошибке.
Предложение FOREIGN KEY
Внешний ключ (foreign key) - это столбец (или группа столбцов таблицы), содержащий значения, совпадающие со значениями первичного ключа в этой же или другой таблице. Внешний ключ определяется с помощью предложения FOREIGN KEY в комбинации с предложением REFERENCES . Синтаксис предложения FOREIGN KEY следующий:
[ ({col_name1} ,...)] REFERENCES table_name ({col_name2},...) Соглашения по синтаксису
Предложение FOREIGN KEY явно определяет все столбцы, входящие во внешний ключ. В предложении REFERENCES указывается имя таблицы, содержащей столбцы, создающие соответствующий первичный ключ. Количество столбцов и их тип данных в предложении FOREIGN KEY должны совпадать с количеством соответствующих столбцов и их типом данных в предложении REFERENCES (и, конечно же, они должны совпадать с количеством столбцов и типами данных в первичном ключе таблицы, на которую они ссылаются).
USE SampleDb; CREATE TABLE Works_on (EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, Job NCHAR (15) NULL, EnterDate DATE NULL, CONSTRAINT primary_works PRIMARY KEY (EmpId, ProjectNumber), CONSTRAINT foreign_employee FOREIGN KEY (EmpId) REFERENCES Employee (Id), CONSTRAINT foreign_project FOREIGN KEY (ProjectNumber) REFERENCES Projects (Number));
Таблица Works_on в этом примере задается с тремя декларативными ограничениями для обеспечения целостности: primary_works, foreign_employee и foreign_project. Эти ограничения являются ограничением уровня таблицы, где первое указывает первичный ключ, а второе и третье - внешний ключ таблицы Works_on. Кроме этого, внешние ключи определяют таблицы Employee и Projects, как ссылочные таблицы, а их столбцы Id и Number, как соответствующий первичный ключ столбца с таким же именем в таблице Works_on.
Предложение FOREIGN KEY можно пропустить, если внешний ключ определяется, как ограничение уровня таблицы, поскольку столбец, к которому применяется ограничение, является неявным "списком" столбцов внешнего ключа, и ключевого слова REFERENCES достаточно для указания того, какого типа является это ограничение. Таблица может содержать самое большее 63 ограничения FOREIGN KEY.
Определение внешних ключей в таблицах базы данных налагает определение другого важного ограничения для обеспечения целостности: ссылочной целостности.
Ссылочная целостность (referential integrity) обеспечивает выполнение правил для вставок и обновлений таблиц, содержащих внешний ключ и соответствующее ограничение первичного ключа. Пример выше имеет два таких ограничения: foreign_employe и foreign_project. Предложение REFERENCES в примере определяет таблицы Employee и Projects в качестве ссылочных (родительских) таблиц.
Если для двух таблиц указана ссылочная целостность, модифицирование значений в первичном ключе и соответствующем внешнем ключе будет не всегда возможным. В последующих разделах рассматривается, когда это возможно, а когда нет.
Модификация значений внешнего или первичного ключа может создавать проблемы в четырех случаях. Все эти случаи будут продемонстрированы с использованием базы данных SampleDb. В первых двух случаях затрагиваются модификации ссылающейся таблицы, а в последних двух - родительской.
Возможные проблемы со ссылочной целостностью - случай 1
Вставка новой строки в таблицу Works_on с номером сотрудника 11111. Соответствующая инструкция Transact-SQL выглядит таким образом:
USE SampleDb; INSERT INTO Works_on VALUES (11111, "p1", "qwe", GETDATE())
При вставке новой строки в дочернюю таблицу Works_on используется новый номер сотрудника EmpId, для которого нет совпадающего сотрудника (и номера) в родительской таблице Employee. Если для обеих таблиц определена ссылочная целостность, как это сделано ранее, то компонент Database Engine не допустит вставки новой строки с таким номером EmpId.
Возможные проблемы со ссылочной целостностью - случай 2
Изменение номера сотрудника 9502 во всех строка таблицы Works_on на номер 11111. Соответствующая инструкция Transact-SQL выглядит таким образом:
В данном случае существующее значение внешнего ключа в ссылающейся таблице Works_on заменяется новым значением, для которого нет совпадающего значения в родительской таблице Employee. Если для обеих таблиц определена ссылочная целостность, то система управления базой данных не допустит модификацию строки с таким номером EmpId в таблице Works_on.
Возможные проблемы со ссылочной целостностью - случай 3
Замена значения 9502 номера сотрудника Id на значение 22222 в таблице Employee. Соответствующая инструкция Transact-SQL будет выглядеть таким образом:
В данном случае предпринимается попытка заменить существующее значение 9502 номера сотрудника Id значением 22222 только в родительской таблице Employee, не меняя соответствующие значения Id в ссылающейся таблице Works_on. Система не разрешает выполнения этой операции. Ссылочная целостность не допускает существования в ссылающейся таблице (таблице, для которой предложением FOREIGN KEY определен внешний ключ) таких значений, для которых в родительской таблице (таблице, для которой предложением PRIMARY KEY определен первичный ключ) не существует соответствующего значения. В противном случае такие строки в ссылающейся таблице были бы "сиротами". Если бы описанная выше модификация таблицы Employee была разрешена, тогда строки в таблице Works_on со значением Id равным 9502 были бы сиротами. Поэтому система и не разрешает выполнения такой модификации.
Возможные проблемы со ссылочной целостностью - случай 4
Удаление строки в таблице Employee со значением Id равным 9502.
Этот случай похожий на случай 3. В случае выполнения этой операции, из таблицы Employee была бы удалена строка со значением Id, для которого существуют совпадающие значения в ссылающейся (дочерней) таблице Works_on.
Опции ON DELETE и ON UPDATE
Компонент Database Engine на попытку удаления и модифицирования первичного ключа может реагировать по-разному. Если попытаться обновить значения внешнего ключа, то все эти обновления будут несогласованы с соответствующим первичным ключом, база данных откажется выполнять эти обновления и выведет сообщение об ошибке.
Но при попытке внести обновления в значения первичного ключа, вызывающие несогласованность в соответствующем внешнем ключе, система базы данных может реагировать достаточно гибко. В целом, существует четыре опции, определяющих то, как система базы данных может реагировать:
NO ACTIONМодифицируются (обновляются или удаляются) только те значения в родительской таблице, для которых нет соответствующих значений во внешнем ключе дочерней (ссылающейся) таблицы.
CASCADEРазрешается модификация (обновление или удаление) любых значений в родительской таблице. При обновлении значения первичного ключа в родительской таблице или при удалении всей строки, содержащей данное значение, в дочерней (ссылающейся) таблице обновляются (т.е. удаляются) все строки с соответствующими значениями внешнего ключа.
SET NULLРазрешается модификация (обновление или удаление) любых значений в родительской таблице. Если обновление значения в родительской таблице вызывает несогласованность в дочерней таблице, система базы данных присваивает внешнему ключу всех соответствующих строк в дочерней таблице значение NULL. То же самое происходит и в случае удаления строки в родительской таблице, вызывающего несогласованность в дочерней таблице. Таким образом, все несогласованности данных пропускаются.
SET DEFAULTАналогично опции SET NULL, но с одним исключением: всем внешним ключам, соответствующим модифицируемому первичному ключу, присваивается значение по умолчанию. Само собой разумеется, что после модификации первичный ключ родительской таблицы все равно должен содержать значение по умолчанию.
В языке Transact-SQL поддерживаются первые две из этих опций. Использование опций ON DELETE и ON UPDATE показано в примере ниже:
USE SampleDb; CREATE TABLE Works_on (EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, Job NCHAR (15) NULL, EnterDate DATE NULL, CONSTRAINT primary_works PRIMARY KEY (EmpId, ProjectNumber), CONSTRAINT foreign_employee FOREIGN KEY (EmpId) REFERENCES Employee (Id) ON DELETE CASCADE, CONSTRAINT foreign_project FOREIGN KEY (ProjectNumber) REFERENCES Projects (Number) ON UPDATE CASCADE);
В этом примере создается таблица Works_on с использованием опций ON DELETE CASCADE и ON UPDATE CASCADE. Если таблицу Works_on загрузить значениями, каждое удаление строки в таблице Employee будет вызывать каскадное удаление всех строк в таблице Works_on, которые имеют значения внешнего ключа, соответствующие значениям первичного ключа строк, удаляемых в таблице Employee. Подобным образом каждое обновление значения столбца Number таблицы Project будет вызывать такое же обновление всех соответствующих значений столбца ProjectNumber таблицы Works_on.
Язык SQL используется не только для обработки информации, но и предназначена для выполнения всех операций с базами данных и таблицами, включая также создание таблиц и работа с ними. Существует два способа создания таблиц: 1) большинство СУБД обладают визуальным интерфейсом для интерактивного создания таблиц и управление ими; 2) таблицами можно манипулировать, используя операторы SQL. Стоит отметить, что, когда вы используете интерактивный инструментарий СУБД, на самом деле вся работа выполняется операторами SQL, т.е. интерфейс сам создает эти команды незаметно для пользователя (это подобно на запись макроса в Excel, когда макрорекодер записывает ваши действия и преобразует их в команды VBA).
1. Создание таблиц
Для создания таблиц программным способом используют оператор CREATE TABLE . Для этого нужно указать следующие данные:
- - имя таблицы, которое указывается после ключевого слова CREATE TABLE
- - имена и определения столбцов таблицы, отделены запятыми
- - в некоторых СУБД также требуется, чтобы было указано местоположение таблицы.
Давайте создадим новую таблицу и назовем ее Customers :
CREATE TABLE
Customers (
ID CHAR(10)
NOT NULL Primary key
,
Custom_name CHAR(25)
NOT NULL,
Custom_address CHAR(25)
NULL,
Custom_city CHAR(25)
NULL,
Custom_Country CHAR(25)
NULL,
ArcDate CHAR(25)
NOT NULL, DEFAULT
NOWO)
Так мы сначала указываем название новой таблицы, затем в скобках перечисляем столбцы, которие будем создавать, причем их названия не могут повторяться в пределах одной таблицы. После названий столбцов указывается тип данных для каждого поля (CHAR (10) ), затем отмечаем может ли поле содержать пустые значения (NULL или NOT NULL ), а также нужно указать поле, которое будет первичным ключом (Primary key ).
Язык SQL также позволяет определять для каждого поля значение по умолчанию, то есть, если пользователь не укажет значение определенного поля - оно будет автоматически проставлено СУБД. Значение по умолчанию определяется ключевым словом DEFAULT при определении столбцов оператором CREATE TABLE .
2. Обновление таблиц
Для того, чтобы изменить таблицу в SQL используется оператор ALTER TABLE . При использовании данного оператора необходимо ввести следующую информацию:
- - имя таблицы, которую мы хотим изменить
- - перечень изменений, которые мы хотим сделать.
Для примера давайте добавим новую колонку в таблицу Sellers , в которой будем указывать телефон реализатора:
ALTER TABLE Sellers ADD Phone CHAR (20)
Кроме добавления столбцов, мы можем их удалять. Давайте теперь удалим поле Phone . Для этого пропишем следующий запрос:
ALTER TABLE Sellers DROP COLUMN Phone
3. Удаление таблиц
Удаление таблиц осуществляется с помощью оператора DROP TABLE . Чтобы удалить таблицу Sellers_new , мы можем прописать следующий запрос:
DROP TABLE Sellers_new
Во многих СУБД применяются правила, предотвращающие удаление таблиц, которые являются уже связаны с другими таблицами. Если эти правила действуют и вы удаляете такую таблицу, то СУБД блокирует операцию удаления до тех пор, пока не будет удалена связь. Такие меры предотвращают случайное удаление нужных таблиц.
Работа с базами данных непосредственно связана с изменением таблиц и содержащихся в них данных. Но перед началом проведения действий таблицы необходимо создать. Для автоматизации этого процесса существует специальная функция SQL - "CREATE TABLE".
Первым делом!
Перед тем как разбираться с процессом создания таблиц с помощью команды MS SQL "CREATE TABLE", стоит остановиться на том, что надо знать перед началом использования функции.
Прежде всего, необходимо придумать имя таблице - оно должно быть уникальным, в сравнении с другими, находящимися в базе данных, и следовать нескольким правилам. Имя должно начинаться с буквы (a-z), после чего могут следовать любые буквы, цифры и знак подчеркивания, при этом полученная фраза не должна быть зарезервированным словом. Длина названия таблицы не должна превышать 18 символов.
Определившись с именем, следует разработать структуру: придумать названия столбцам, продумать используемый в них тип данных и какие поля должны быть обязательно заполнены. Здесь же стоит сразу определить поля внешних и первичных ключей, а также возможные ограничения для значений данных.
Остальные нюансы таблицы можно достаточно легко подкорректировать, поэтому на этапе создания таблицы они могут быть продуманы не до конца.
Синтаксис
Разработав структуру таблицы, можно переходить к её созданию. Сделать это достаточно просто, воспользовавшись "CREATE TABLE". В ней пользователю требуется указать придуманные ранее имя таблицы и список столбцов, указав для каждого из них тип и имя. Синтаксис функции выглядит следующим образом:
CREATE TABLE table_name
({column_name datatype …| table_constraint}
[,{column_name datatype …| table_constraint}]…)
Аргументы, используемые в конструкции функции, означают следующее:
- table_name - имя таблицы
- column_name - имя столбца
- datatype - тип данных, используемый в данном поле
- DEFAULT - выражение, используемое в столбце по умолчанию.
Также возможно использование ещё двух аргументов функции:
- colum_constraint - параметры столбца
- table_constraint - параметры таблицы
В них пользователь может указать требуемые для работы ограничения или условия заполнения таблицы.
Особенности создания таблиц
При написании запроса с функцией иногда необходимо установить правила заполнения полей. Для этого необходимо добавить специальные атрибуты функции, определяющие тот или иной набор условий.
Для того чтобы определить, может ли в ячейке находиться пустое значение, после указания имени и типа столбца следует прописать одно из ключевых слов: NULL (могут быть пустые значения) или NOT NULL (поле должно быть заполнено).
При создании таблицы в большинстве случаев требуется унифицировать каждую запись, чтобы избежать наличия двух одинаковых. Для этого чаще всего используют нумерацию строк. И, чтобы не требовать от пользователя знания последнего номера, имеющегося в таблице, в функции "CREATE TABLE" достаточно указать столбец первичного ключа, написав ключевое слово "Primary key" после соответствующего поля. Чаще всего именно по первичному ключу и происходит соединение таблиц между собой.
Для обеспечения сцепки с Primary key используется свойство внешнего ключа "FOREIGN KEY". Указав для столбца данное свойство, можно обеспечить, что в данном поле будет содержаться значение, совпадающее с одним из тех, что находятся в столбце первичного ключа этой же или другой таблицы. Таким образом можно обеспечить соответствие данных.
Чтобы обеспечить проверку на соответствие некоторому заданному набору или определению, следует воспользоваться атрибутом CHECK. Он прописывается последним в списке аргументов функции и в качестве личного параметра имеет некоторое логическое выражение. С его помощью можно ограничить список возможных значений, например, использование в поле таблицы "Пол" только буквы "М" и "Ж".
Помимо представленных, функция имеет ещё множество специфических атрибутов, однако они используются на практике гораздо реже.
Примеры
Чтобы полноценно понять принцип работы функции, стоит рассмотреть на практике, как работает CREATE TABLE (SQL). Пример, приведенный ниже, создает таблицу, представленную на рисунке:
CREATE TABLE Custom
(ID CHAR(10) NOT NULL Primary key,
Custom_name CHAR(20),
Custom_address CHAR(30),
Custom_city CHAR(20),
Custom_Country CHAR(20),
ArcDate CHAR(20))
Как можно заметить, параметр возможного отсутствия значения в ячейке (NULL) можно опускать, так как он используется по умолчанию.