Как создать первичный ключ в эксель
Поле определяют как ключевое - если это поле содержит уникальные значения (коды, порядковые или инвентарные номера).
Первичный ключ – это одно или несколько полей (столбцов), комбинация значений которых однозначно определяет каждую запись в таблице, т.е. обеспечивает уникальность записей в таблице и препятствует вводу повторяющихся данных. Первичный ключ не допускает значений Null и всегда должен иметь уникальный индекс. Первичный ключ используется для связывания таблицы с внешними ключами в других таблицах.
Внешний (вторичный) ключ - это одно или несколько полей (столбцов) в таблице, содержащих ссылку на поле или поля первичного ключа в другой таблице. Внешний ключ определяет способ объединения таблиц.
Из двух логически связанных таблиц одну называют таблицей первичного ключа или главной таблицей, а другую таблицей вторичного (внешнего) ключа или подчиненной таблицей.
СУБД позволяют сопоставить родственные записи из обеих таблиц и совместно вывести их в форме, отчете или запросе.
Типы первичного ключа: ключевые поля счетчика (счетчик), простой ключ и составной ключ.
Счетчик -является наиболее простым способом создания ключевого поля. Создается автоматически после подтверждения сохранения таблицы, в которой явно не было указано ключевое поле. Однозначно идентифицирует каждую запись в таблице. Имя поля присваивается автоматически - Код,тип данных - Счетчик. Это означает, что каждый раз при создании новой записи значение счетчика будет увеличиваться на 1. В результате у каждой записи формируется свой номер, который и является первичным ключом.
Простой ключ -состоит только из одного поля, которое содержит уникальные значения, такие как коды или инвентарные номера. В качестве ключа можно определить любое поле, содержащее данные, если это поле не содержит повторяющиеся значения или значения Null.
Составной ключ -состоит из нескольких полей, значения которых в совокупности являются уникальными, создается, когда нет возможности выбрать одно поле без повторов и возможных пустых значений. Все поля составного ключа должны располагаться последовательно друг за другом. Чаще всего такая ситуация возникает для таблицы, используемой для связывания двух таблиц связью многие - ко - многим.
Необходимо еще раз отметить, что в поле первичного ключа должны быть только уникальные значения в каждой строке таблицы, т.е. совпадение не допускается, а в поле вторичного или внешнего ключа совпадение значений в строках таблицы допускается.
Если возникают затруднения с выбором подходящего типа первичного ключа, то в качестве ключа целесообразно выбрать поле счетчика.
Ключевые поля целесообразно располагать вначале таблицы, поскольку автоматически производится сортировка записей по ключу.
Т.к. ключевое поле не может содержать повторяющиеся или пустые значения. Если устранить повторы путем изменения значений невозможно, то следует, либо добавить в таблицу поле счетчика и сделать его ключевым, либо определить составной ключ.
Для составного ключа существенным может оказаться порядок образующих ключ полей. Сортировка записей осуществляется в соответствии с порядком ключевых полей в окне Конструктора таблицы. Если необходимо указать другой порядок сортировки без изменения порядка ключевых полей, то сначала нужно определить ключ, а затем нажать кнопку Индексы на панели инструментов Конструктор таблиц. Затем в появившемся окне Индексы нужно указать другой порядок полей для индекса с именем Ключевое поле.
Первичный ключ может быть определён только в режиме Конструктора таблиц:
1. Выделите поле, которое должно стать полем первичного ключа;
2. Вкладка Работа с таблицами → вкладка Конструктор → группа Сервис → кнопка Ключевое поле
Задание 1.3
Проанализировать данные и определить структуру базы данных. Создать файл базы данных Отель.mdb.Сформировать таблицу под именем Гостиница с помощью конструктора таблиц и заполнить данными.
Самостоятельная работа Задания 1.4 - 1.5
Задание 1.4
Создать файл базы данных Страна.mdb. С помощью Конструктора таблиц подготовить таблицу Государства. Таблица содержит поля: Название, Площадь, Население, Язык, Религия и заполнить данными.
Название | Площадь, км 2 | Население | Язык | Религии |
Австралия | английский | римско-католическая | ||
Австрия | немецкий | римско-католическая | ||
Болгария | болгарский | православная | ||
Бутан | дзонг-кэ | буддизм | ||
Венгрия | венгерский | римско-католическая | ||
Гамбия | английский | ислам | ||
Дания | датский | лютеранская | ||
Италия | итальянский | римско-католическая | ||
Йемен | арабский | ислам | ||
Кувейт | арабский | ислам | ||
Лаос | лао | буддизм | ||
Ливия | арабский | ислам | ||
Лихтенштейн | немецкий | римско-католическая | ||
Люксембург | люксембургский | римско-католическая | ||
Мексика | испанский | римско-католическая | ||
Норвегия | норвежский | лютеранская | ||
Польша | польский | римско-католическая | ||
Судан | арабский | ислам | ||
Турция | турецкий | ислам | ||
Франция | французский | римско-католическая | ||
Швеция | шведский | лютеранская | ||
Ямайка | английский | протестантская |
Задание 1.5
Создать файл базы данных Академик.mdb. С помощью Конструктора таблиц подготовить таблицу Члены-корреспонденты Академии наук Беларуси. Заполнить таблицу, включив поля: ФИО, Дата рождения, Специализация, Пол, Год присвоения звания.
ФИО | Дата рождения | Специализация | Пол | Год присвоения звания |
Александрович А.И. | 22.01.1906 | поэт | м | |
Амбросов А.Л. | 16.06.1912 | фитопатолог-вирусолог | м | |
Аринчин М.И. | 28.02.1914 | физиолог | м | |
Бабосов Е.М. | 23.02.1931 | философ | м | |
Бирич Т.В. | 10.01.1905 | офтальмолог | ж | |
Бокуть Б.В. | 27.10.1926 | физик | м | |
Бондарчик В.К. | 01.08.1920 | этнограф | м | |
Будыка С.Х. | 17.03.1909 | гидролог | м | |
Гуринович Г.П. | 26.04.1933 | физик | м | |
Иванов А.П. | 29.12.1929 | физик | м | |
Каменская Н.В. | 10.01.1914 | историк | ж | |
Комаров В.С. | 29.01.1923 | химик | м | |
Кулаковская Т.Н. | 17.02.1919 | агрохимик-почвовед | ж | |
Мацкевич Ю.Ф. | 27.07.1911 | языковед | ж | |
Пилипович В.А. | 05.01.1931 | физик | м | |
Сикорский В.М. | 10.10.1923 | историк | м | |
Старобинец Г.Л. | 14.05.1910 | химик | м | |
Судник М.Р. | 08.11.1910 | языковед | м | |
Ткачев В.Д. | 19.02.1939 | физик | м | |
Хотылева Л.В. | 12.03.1928 | генетик | ж | |
Шабуня К.И. | 28.10.1912 | историк | м | |
Широканов Д.И. | 20.05.1929 | философ | м |
Свойства полей Столбец подстановки
Приложение 2. Свойства поля
Задание 1.6
Создать файл базы данных Погода.mdb, который содержит данные о погоде в июле. С помощью конструктора подготовить таблицу Погода. Заполнить таблицу данными, включив поля: Число, Температура, Осадки.
Число | Температура, °С | Осадки |
нет | ||
дождь | ||
град | ||
… | … | … |
Ввести ограничения для значений в полях Температураи Осадки. В поле Температуразначения могут изменяться в диапазоне от 0 до +50 градусов Цельсия, а в поле Осадки возможны три варианта: дождь, град или нет.
- С помощью конструктора создать структуру базы данных.
Самостоятельно Задание 1.7.
Задание 1.7
Создать файл базы данных Тест.mdbоб успеваемости группы учеников школы. Подготовить таблицу Тест, которая содержит информацию: Фамилия, Имя, Пол, Возраст, Оценка по математике. Ввести ограничения в поля Возрасти Оценка. В поле Возрастзначения могут изменяться в промежутке от 5 до 18, ав поле Оценка возможны варианты от 1 до 10. В таблицу внести данные о 5-6 учениках.
Первичный ключ — это поле или набор полей со значениями, которые являются уникальными для всей таблицы. Значения ключа могут использоваться для обозначения всех записей, при этом каждая запись имеет отдельное значение ключа. Каждая таблица может содержать только один первичный ключ. Access может автоматически создавать поле первичного ключа при создании таблицы. Вы также можете самостоятельно указать поля, которые нужно использовать в качестве первичного ключа. В этой статье объясняется, как и зачем использовать первичные ключи.
Чтобы задать первичный ключ таблицы, откройте таблицу в режиме конструктора. Выберите нужное поле (или поля), а затем на ленте щелкните Ключевое поле.
Примечание: Эта статья относится только к классическим базам данных Access. В веб-приложениях Access и веб-базах данных первичный ключ для новых таблиц назначается автоматически. Несмотря на то что автоматические первичные ключи можно менять, делать это не рекомендуется.
В этой статье
Общие сведения о первичных ключах в Access
Используя поля первичных ключей, Access быстро связывает данные из нескольких таблиц и объединяет их понятным образом. Вы можете добавить поля первичных ключей в другие таблицы, чтобы ссылаться на таблицу, которая является источником первичного ключа. В других таблицах поля называются внешними ключами. Например, поле "ИД клиента" в таблице "Клиенты" также может отображаться в таблице "Заказы". В таблице "Клиенты" оно является первичным ключом. В таблице "Заказы" оно называется внешним ключом. Проще говоря, внешний ключ — это первичный ключ другой таблицы. Дополнительные сведения см. в статье Основные сведения о создании баз данных.
1. Первичный ключ
При переносе существующих данных в базу данных в них уже может существовать поле, которое можно использовать как первичный ключ. Часто в роли первичного ключа таблицы выступает уникальный идентификационный номер, например порядковый или инвентарный номер или код. Например, в таблице "Клиенты" для каждого клиента может быть указан уникальный код клиента. Поле кода клиента является первичным ключом.
Для первичного ключа автоматически создается индекс, ускоряющий выполнение запросов и операций. Кроме того, приложение Access проверяет наличие и уникальность значений в поле первичного ключа.
При создании таблицы в режиме таблицы Access автоматически создает первичный ключ с именем "Код" и типом данных "Счетчик".
Создание приемлемого первичного ключа
Чтобы правильно выбрать первичный ключ, следует учитывать несколько характеристик.
Ключ должен однозначно определять каждую строку.
В нем не должно быть пустых или отсутствующих значений — он всегда содержит значение.
Ключ крайне редко изменяется (в идеале — никогда).
Если не удается определить приемлемый ключ, создайте для него поле с типом данных "Счетчик". Поле "Счетчик" заполняется автоматически созданными значениями при первом сохранении каждой записи. Таким образом, поле "Счетчик" соответствует всем трем характеристикам приемлемого первичного ключа. Дополнительные сведения о добавлении поля "Счетчик" см. в статье Добавление поля счетчика в качестве первичного ключа.
Поле с типом данных "Счетчик" является хорошим первичным ключом.
Примеры неудачных первичных ключей
Любое поле, не имеющее одной или нескольких характеристик подходящего первичного ключа, не следует выбирать в качестве первичного ключа. Ниже представлено несколько примеров полей, которые не годятся на роль первичного ключа в таблице "Контакты", и пояснения, почему их не следует использовать.
Первичный ключ можно определить в SQL Server с помощью SQL Server Management Studio или Transact-SQL. Создание первичного ключа автоматически приводит к созданию соответствующего уникального кластеризованного индекса (или некластеризованного при наличии такого указания).
Перед началом
Ограничения
В таблице возможно наличие только одного ограничения по первичному ключу.
Все столбцы с ограничением PRIMARY KEY должны иметь признак NOT NULL. Если допустимость значения NULL не указана, то для всех столбцов c ограничением PRIMARY KEY устанавливается признак NOT NULL.
Безопасность
Разрешения
Создание новой таблицы с первичным ключом требует разрешения CREATE TABLE в базе данных и разрешения ALTER на схему, в которой создается таблица.
Создание первичного ключа в существующей таблице требует разрешения ALTER на таблицу.
Использование среды SQL Server Management Studio
Создание первичного ключа
- В обозревателе объектов щелкните правой кнопкой мыши таблицу, в которую необходимо добавить ограничение уникальности, и выберите Конструктор.
- В Конструкторе таблицщелкните селектор строк для столбца базы данных, который необходимо определить в качестве первичного ключа. Чтобы выделить несколько столбцов, нажмите и удерживайте клавишу CTRL и щелкните селекторы строк для остальных столбцов.
- Щелкните правой кнопкой мыши средство выбора строк столбца и выберите команду Задать первичный ключ.
Ключевой столбец-источник идентифицируется символом первичного ключа в соответствующем селекторе строк.
Если первичный ключ состоит более чем из одного столбца, то в одном столбце могут встречаться дублирующиеся значения, но все сочетания значений изо всех столбцов первичного ключа должны быть уникальными.
При определении составного ключа порядок столбцов в первичном ключе совпадает с порядком столбцов, показанным в таблице. Однако после создания первичного ключа порядок столбцов можно изменить. Дополнительные сведения см. в разделе Изменение первичных ключей.
Использование Transact-SQL
Создание первичного ключа в существующей таблице
В следующем примере создается первичный ключ для столбца TransactionID в базе данных AdventureWorks.
Создание первичного ключа в новой таблице
В следующем примере создается таблица и определяется первичный ключ для столбца TransactionID в базе данных AdventureWorks.
Создание первичного ключа с кластеризованным индексом в новой таблице
В следующем примере создается таблица и определяется первичный ключ для столбца CustomerID и кластеризованного индекса для TransactionID в базе данных AdventureWorks.
Каждая таблица должна содержать первичный ключ — одно или несколько полей, содержимое которых уникально для каждой записи. Например, поле ID_doc— это первичный ключ в таблице DoctorTab, т.е. каждая запись в этой таблице имеет свой уникальный номер врача. Наличие неповторяющегося первичного ключа в каждой записи (как поле ID_doc в таблице DoctorTab) позволяет рассматривать любые две запи-си обособленно.
Первичные ключи создаются для гарантирования уникальности каждой записи. Создавая таблицы, Access всегда предлагает задать первичный ключ с типом данных Счетчик (AutoNumber). Значение такого поля будет автоматически увеличиваться на единицу при добав-лении новой записи. В Access для обеспечения целостности данных нельзя использовать в таблицах поля с типом данных Счетчик (AutoNumber). Поэтому для первичного ключа важно задать иной тип данных, например Текстовый или Числовой.
Так, для заполнения текстового поля идентификатора используется одно из правил генерации уникального значения. Правила могут очень простыми. Например, можно использовать конкатенацию некоего символьного выражения и порядкового номера (А001, А002; В001, В002 и т.д.), а также сложное выражение, вычисленное на основе информации из нескольких полей таблицы.
Обычно в базах данных есть несколько связанных таблиц. Связы-вая таблицы, следует связывать первичный ключ одной таблицы с полем такой же структуры и типа другой. Если поле связи во второй таблице не является первичным ключом (обычно так и бывает), его называют внешним ключом.
Поле первичного ключа в Access не только выполняет роль связующего поля между двумя таблицами, но и предоставляет другие преимущества.
• Поле первичного ключа является индексом, который значительно ускоряет выполнение запросов, поиск и сортировку.
• При вводе новых записей вы должны ввести значение в поле (или поля) первичного ключа; Access не позволит вам оставить это поле незаполненным и проследит, чтобы вы ввели только допустимые значения для текущей записи.
• При добавлении новых записей Access проверяет, не дублируются ли поля первичного ключа, что обеспечивает целостность данных.
• По умолчанию Access сортирует данные по первичному ключу.
Для создания первичного ключа нужно выделить нужное поле, щелк-нуть на кнопке Ключевое поле (Primary Key) панели инструментов. Если необходимо создать первичный ключ, состоящий из нескольких полей (составной первичный ключ), то следует выделить все эти поля (удерживая нажатой клавишу Ctrl>) и щелкнуть на кнопке Ключевое поле (Primary Key).
При определении составного первичного ключа важен порядок выбора полей. Поэтом следует проверить правильность порядка наз-начения ключевых полей с помощью диалогового окна Индексы (Indexes), вызываемого после щелчка на кнопке Индексы (Indexes) панели инструментов. На рисунке 1 показан первичный ключ, состоящий из двух полей таблица VisitTab .
Диалоговое окно Индексы (рис. 2) открывается (при условии, что таблица открыта в режиме конструктора) с помощью выбора из главного меню Access команды Вид =>Индексы (View => Indexes) или щелчка на кнопке Индексы (Indexes) (кнопка с изображением серии параллельных
линий и молнии, расположенная в середине панели инструментов, справа от кнопки Ключевое поле).
Рис.1. Создание первичного ключа
В окне Индексы отображены все индексы таблицы, включая первичный и внешние ключи, а также другие индексы, служащие для ускорения сортировки и поиска по определенным полям.
Рис.2. Диалоговое окноИндексы
Обратите внимание, что тип индекса PrimaryKey отображен только возле поля Id_doc, хотя графический символ отображается для обоих полей, составляющих первичный ключ.
Порядок расположения этих полей критичен, если вы измените его на обратный, сделав поле Id_pat первой частью первичного ключа, это приведет к некорректной работе — может сложиться ситуация, когда невозможно будет создать уникальную запись в таблице.
Если предполагается частое выполнение одновременной сортировки или поиска в нескольких полях, можно создать для этих полей составной индекс. Его создание осуществляется с помощью окна Индексы(см. рис. 2) . Для этого необходимо:
1. Открыть таблицу в режиме Конструктора.
2. На панели инструментов нажать кнопку Индексы.
3. В первой пустой строке поля Индексввести имя индекса.
4. В поле Имя полянажать на стрелку и выбрать первое поле, для которого необходимо создать индекс.
5. В следующей строке поля Имя поляуказать второе индексируемое поле. (Для данной строки поле Индексдолжно оставаться пустым.) Повторить эту операцию для всех полей, которые необходимо включить в индекс. В индексе может быть использовано до 10 полей.
По умолчанию устанавливается порядок сортировки По возрастанию. Для сортировки данных полей по убыванию в поле Порядок сортировки надо указать значение По убыванию.
Диалоговое окно Индексыиспользуется также для просмотра, изменения и удаления существующих индексов. Изменить можно:
- название индекса в поле Индекс;
- поле таблицы, соответствующее данному индексу, выбрав новое поле из списка в поле Имя поля;
- порядок сортировки в поле Порядок сортировки;
- свойства данного индекса в нижней части окна (см. рис. 2):
- Ключевое полеопределяет, является ли индексированное поле ключевым;
- Уникальный индексопределяет, должно ли быть каждое значение в этом поле уникальным;
- Пропуск пустых полей определяет, включаются или не включаются в индекс записи с пустым (Null) значением данного поля.
Например, если в одном и том же запросе часто задаются условия для полей Имя врача и Фамилия врача, то для этих двух полей имеет смысл создать составной индекс.
При сортировке таблицы по составному индексу Microsoft Access сначала выполняет сортировку по первому полю, определенному для данного индекса. Если в первом поле содержатся записи с повторяющимися значениями, то выполняется сортировка по второму полю, определенному для данного индекса, и так далее.
Создание связей между таблицами
Для пояснения возможных отношений между объектами БД обратимся к уже рассмотренной базе данных о врачах и пациентах. Таблица DoctorTab содержит информацию о врачах, таблица PatientTab- информация о пациентах. Связь между ними можно осуществить с помощью таблицы VisistTab (информация о посещении пациентами врачей). Например, вы можете получить информацию о том, кто и когда посещал какого-либо врача. Каждая запись в таблицах идентифицирует один объект группы (врач, пациент и прием). Отно-шение между объектами определяет отношение между таблицами. Предполагая, что один врач может принять несколько пациентов, а один пациент может посетить несколько врачей. Таким образом, между пациентом и его посещениями врача существует отношение один-ко-многим, такая же связь существует между врачом и его приемами. Связь таблиц осуществляется на основании данных в совпадающих полях ID_doc и ID_pat.
Access поддерживает четыре типа отношений между таблицами: один-к-одному, один-ко-многим, много-к-одному, много-ко-многим.
Связи на уровне таблиц можно создать с помощью окна Схема данных (Relationships). В случае необходимости связи между таблицами можно разорвать или изменить. Для обычного ввода данных и вывода отчетов связи на уровне таблиц очень удобны в использовании.
В Access 2002 встроено мощное средство установки связей. Оно позво-ляет добавлять таблицы, использовать метод перетащить и опустить для связи таблиц, легко определять тип связи и устанавливать любые параметры целостности данных между таблицами.
Связи начинают устанавливать в окне базы данных. При акти-визированном окне базы данных, выберите команду Сервис => Схема данных (Tools => Relationships) или щелкните на кнопке Схема данных (Relationships) панели инструментов. При этом появится окно Схема данных (Relationships), которое позволяет добавлять таблицы и создавать связи между ними (рис. 3).
Рис. 3. ОкноСхема данных
Обратите внимание, что с ним связана новая панель инструментов, которая имеет две кнопки, характерные для окна Схема данных — Отобразить прямые связи (Show Direct Relationships) и Отобразить все связи (Show All Relationships). Изначально окно Схема данных (Relationships) не содержит таблиц.
Добавлять таблицы в окно можно следующими способами:
• Использованием диалогового окна Добавление таблицы (Show Table), которое открывается автоматически, если окно Схема данных (Relationships) для базы данных открывается впервые.
• Нажатием кнопки Отобразить таблицу (Show Table) панели инструментов.
• С помощью команды Связи => Добавить таблицу (Relationships => Show Table).
• Находясь в окне Схема данных (Relationships), щелкните правой кнопкой мыши и выберите из контекстного меню команду Добавить таблицу (Show Table).
Для вызова окна Схема данных (Relationships) и добавления в него таблиц выполните следующие действия.
1. Щелкните на кнопке Схема данных (Relationships) панели инструментов, и Access откроет диалоговое окно Добавление таблицы (Show Table).
2. Выберите все таблицы (DoctorTab, VisitTab, PatTab).После этого
щелкните на кнопке Добавить (Add).3. Щелкните на кнопке Закрыть (Close) диалогового окна добавление таблицы (Show Table). Экран будет выглядеть так, как на рисунке 3. Каждая таблица представлена в окне Схема данных списком своих полей, в качестве заголовков этих списков используются имена
таблиц. Никаких связей между таблицами нет, но теперь вы можете их установить.
После добавления нужного количества таблиц в окно Схема данных (Relationships) можно создать связи между таблицами. Для этого просто выберите общее поле в одной из таблиц и перетащите его в общее поле той таблицы, которую вы хотите связать с первой (рис. 4).
Рис.4. Отображение связей между таблицами в окне Схема данных
Если вы ошиблись при выборе поля для связи, просто пере-местите символ поля в пустую область окна, где символ изменится на международный символ Нет -- 0. Как только отобразится этот символ, отпустите кнопку мыши, и процесс создания связи прекратится.
Access автоматически определяет тип отношения в связи между двумя таблицами, основываясь на данных в записях этих таблиц. Тип отношения отображается в нижней части диалогового окна Изменение связей (рис. 5).
Примечание:
Во всех статьях текущей категории уроков по SQL используются примеры и задачи, основанные на учебной базе данных.Приступая к изучению данного материала, рекомендуется ознакомиться с описанием учебной БД.
Каждая строка в таблицах реляционных баз данных должна отвечать требованию уникальности. Некоторые поля могут повторяться от записи к записи, но сочетание всех полей строки встречается только единожды. Если не соблюдать это правило, то можно ошибочно отнести свойства одного объекта к другому.
Допустим, существует таблица с перечнем студентов:
На примере видно, что 2 и 4 строки содержать ФИО студентов, являющихся полными тезками, которые учатся в одной группе. Такая ситуация маловероятна, но возможна. Если один из этих студентов не сдаст экзамены и его отчислят, то по ошибке можно отчислить другого, который не имеет проблем с успеваемостью.
Чтобы исключить подобные ошибки, потребуется добавить дополнительное свойство, которое потенциально может служить идентификатором: паспортные данные, номер личного дела и т.п.
На приведенном в качестве примера изображении, столбец «№ дела» однозначно определяет запись и называется первичным ключом. Он является простым, так как состоит из одного столбца.
В учебной базе данных имеется таблица «Сотрудники_Линии», в которой для каждого оператора определены подключенные телефонные линии.
Ни один из столбцов не может являться простым первичным ключом, потому что может повторяться (сотруднику подключается несколько линии, и одна линия подключаются разным сотрудникам). В таком случае первичным ключом служит пара столбцов – «Сотрудник» и «Линия». Телефонную линию нельзя подключить несколько раз одному и тому же оператору, что соответствует уникальности записей.
Первичный ключ, состоящий из нескольких полей, называется составным ключом.
Читайте также: