1

Тема: MS Sql. Поясніть за зовнішні ключі.

Привіт. От є табличка Books, і табличка Books_Genre. В першій табличці лише два стовбчика -id та name,  а в другій аж три, id, book_id та genre. Як зробити так, щоб в полі book_id могли бути лише ті значення, котрі вже існують в полі id першої таблички? Я шось там кльоцаю, кльоцаю, але нічого не зрозуміло =(

2

Re: MS Sql. Поясніть за зовнішні ключі.

Дурнувата система. У вас як, жанри будуть на книги посилатися? Тобто кожного жанру може бути тільки одна книга?
І не кльоцайте, пишіть запити.

3

Re: MS Sql. Поясніть за зовнішні ключі.

koala написав:

Дурнувата система. У вас як, жанри будуть на книги посилатися? Тобто кожного жанру може бути тільки одна книга?
І не кльоцайте, пишіть запити.

да ні, там імена книг будуть унікальні, а жанри ні. І кожній книзі може відповідати один жанр з цілої купи жанрів.

4

Re: MS Sql. Поясніть за зовнішні ключі.

Ще раз, проясніть: скільки жанрів може бути в однієї книги і скільки може бути книг одного жанру? Наразі, здається, ви написали, що може бути кілька жанрів з однією назвою (жанри неунікальні), а кожній книзі відповідатиме один жанр, я правильно процитував? Тоді вам потрібна одна табличка "книги", де жанр був би ще одним полем, типу підзаголовка.

5

Re: MS Sql. Поясніть за зовнішні ключі.

koala написав:

Ще раз, проясніть: скільки жанрів може бути в однієї книги і скільки може бути книг одного жанру? Наразі, здається, ви написали, що може бути кілька жанрів з однією назвою (жанри неунікальні), а кожній книзі відповідатиме один жанр, я правильно процитував? Тоді вам потрібна одна табличка "книги", де жанр був би ще одним полем, типу підзаголовка.

в однієї книги може бути один жанр, а книг одного жанру може бути багацько. Я просто хтів зробити по крутому, типу є табличка лише з жанрами, і якщо ми змінюємо назву якогось жанру в цій табличці, то його назва змінюється й у всіх інших табличках, де цей жанр використовується.

6

Re: MS Sql. Поясніть за зовнішні ключі.

Таке зробити можливо, навіть усі так і роблять, але розрізняйте SQL базу і MS Access (Excel). Ви хочете на базу покласти непритаманні їй функції. Отже - пишіть запити як усі люди :)

7

Re: MS Sql. Поясніть за зовнішні ключі.

TRYCUKI_V_KROVI написав:

в однієї книги може бути один жанр, а книг одного жанру може бути багацько. Я просто хтів зробити по крутому, типу є табличка лише з жанрами, і якщо ми змінюємо назву якогось жанру в цій табличці, то його назва змінюється й у всіх інших табличках, де цей жанр використовується.

Тобто все зовсім навпаки. І це не "по-крутому", це по-людськи зветься.
Отже, кільком книгам відповідає один жанр. Співвідношення багато до одного. Значить, кожна книга посилається на свій жанр, кілька книг можуть посилатися на один і той самий жанр. Можуть існувати жанри, на які не посилаються, але книг без посилань на жанр не буває (чи все ж буває)? Тоді поле-зовнішній ключ має бути в таблиці "книги", а в таблиці "жанри" жодних посилань не треба.
Тепер давайте свій код, покажу, що виправити.

8 Востаннє редагувалося koala (25.08.2013 19:15:14)

Re: MS Sql. Поясніть за зовнішні ключі.

Накидав ось таке, питання, зауваження і виправлення вітаються.

Програмісти вже давно зіткнулися з необхідністю відокремлювати код і дані (наприклад, було б вкрай нелогічно тримати в коді окремі функції для, скажімо, виводу імен окремих людей чи параметрів окремих персонажів комп'ютерної гри).
Дані, що винесені з коду до якогось зовнішнього джерела – скажімо, файлу – зручно тримати у вигляді таблиці (чи кількох таблиць). Однотипні дані знаходяться на одних і тих самих місцях в різних рядках таблиці, хочемо перейти до інших даних – просто змінюємо рядок стандартною операцією. Все просто і зрозуміло.
Але виявилося, що цього не досить. Таблиці працюють, доки не змінюється програміст. Приходить інший програміст зі своїм уявленням про те, як це має працювати – таблиці падають (повертають хибні дані), навіть якщо нічого забороненого новий програміст ніби-то і не робив... і особливо – коли робив, скажімо, ліз переписувати процедуру сортування, «заточену» під конкретні дані.
Простий приклад невдалої таблиці: є база електронних адрес співробітників – проста табличка
П.І.Б   Адреса
Шевченко  shev@company.com.ua
Терещенко t@ukr.net
Шевченко ya_sheva@meta.ua
Таблиця завантажується програмою, яка робить корпоративну розсилку, тому доводиться робити по рядку для кожної адреси. Співробітниця Шевченко змінила прізвище на Кравченко. Новий оператор не помітив, що в таблиці є два рядки з цим прізвищем, і змінив прізвище тільки в одному місці. За місяць компанія надсилала співробітників у відрядження за кордон, і секретарка взяла список прізвищ з цієї таблиці, але не з того рядка. В результаті людина не змогла поїхати, бо документи були на інше прізвище.
Таким чином, виникла потреба, по-перше, в стандартифікованих засобах обробки таблиць, а по-друге, в домовленостях, як ці таблиці мають бути влаштовані, щоб не створювати двозначностей. Так виникли СУБД і реляційна модель баз даних.
Що таке СУБД? Це програма, що надає стандартифікований інтерфейс доступу до даних – скажімо, через мову запитів SQL. СУБД ховає  від програміста (інкапсулює) всю конкретику – де саме зберігаються дані, в якій формі, як вони відсортовані, всі додаткові структури, що прискорюють роботу з даними (кеші, індекси) і т.д. Програміст бачить тільки спосіб отримати рядки з таблиці, не більше (звісно, диявол тут, як завжди, в деталях, але в цілому десь так воно є).
Що таке реляційна модель? Це спосіб опису таблиць даних, який виключає (чи, принаймні, значно зменшує ймовірність) двозначностей в цих таблицях. В цілому ідея була така: якщо хтось пише програму з дотриманням вимог реляційної моделі, то в нього таблиці не заваляться і не заваляться у його наступника , навіть якщо він не вкладатиметься в реляційну модель (тут я виходжу з припущення, що наступник – не ідіот і не ламав базу свідомо). Між двома «нереляційниками» можливий обвал; між реляційником і нереляційником – майже виключений. Не хочете, щоб на вас повісили провину в обвалі бази – пишіть реляційні бази, все просто.
В чому полягає реляційна модель? Дані представляються у вигляді таблиць. Таблиці формально звуться відношеннями (англ. relation, звідси і назва). Таблиці мають задовольняти певним вимогам (перебувати в нормальних формах). Таких форм наразі введено вісім, і перша вимога кожної – щоб відношення перебувало в попередній. Таким чином, якщо база відповідає вимогам третьої нормальної форми (3НФ), то вона відповідає і 1НФ, і 2НФ. На практиці треба дуже сильно постаратися чи займатися чимось вкрай незвичайним, щоб примудритися порушити верхні форми, так що головне – це перші 2-3.
Перша нормальна форма (1НФ) вимагає, щоб:
-    Кожна змінна в таблиці була атомарною (тобто, якщо програма працюватиме з іменами і прізвищами людей окремо, то не може бути поля «ПІБ», мають бути два окремих поля «прізвище» і «ім’я»; якщо в співробітника може бути кілька телефонів, то кожен телефон має бути винесений в окремий рядок і т.д.).
-    Не має значення послідовність рядків (як не сортуй таблицю, база має працювати).
-    Немає однакових рядків. Це досягається введенням ключа – унікальної комбінації полів, за якими можна однозначно визначити рядок; наприклад, ключем може бути поле «ПІБ», пара полів «ПІБ» на «дата народження», якщо є підозра, що в базі можуть бути повні тезки, чи введенням окремого поля «номер людини».
2НФ вимагає, щоб:
-    Таблиці були в 1НФ;
-    в таблицях не було залежностей між неключовим полем і частиною ключа. Якщо частина ключа щось визначає – робіть окрему таблицю, а в початковій робіть посилання на неї  (зовнішній ключ).
3НФ вимагає, щоб:
-    2НФ;
-     в таблицях не було транзитивних залежностей між полями. Якщо одне поле залежить від іншого – виносьте їх в окрему таблицю, навіть якщо вони не ключові.
Приклади дивіться у вікіпедії.
А якщо дуже коротко, то щоб база працювала, треба, щоб кожна сутність була в окремій таблиці, в кожній таблиці був ключ, а кожна комірка містила одне значення, і будь-які дані розміщувалися в таблицях один раз. І все працюватиме довго і щасливо.

9

Re: MS Sql. Поясніть за зовнішні ключі.

то все дуже круто, я потім почитаю, як прийду з церюльні. Я то все роблю в sql, а не access. Я не любаю access і всякі там екселі взагалі. Ну от ви, koala, радите зробити той ключ в таблиці Book. А можна ж розбити то на дві таблиці? В одній таблиці id і ім'я книги, а в іншій id книги і її жанр? Ну то просто щоб більше таблиць було хД

10

Re: MS Sql. Поясніть за зовнішні ключі.

TRYCUKI_V_KROVI написав:

то все дуже круто, я потім почитаю, як прийду з церюльні. Я то все роблю в sql, а не access. Я не любаю access і всякі там екселі взагалі. Ну от ви, koala, радите зробити той ключ в таблиці Book. А можна ж розбити то на дві таблиці? В одній таблиці id і ім'я книги, а в іншій id книги і її жанр? Ну то просто щоб більше таблиць було хД

Я давав різні поради для різних запропонованих вами структур. Остання порада була зробити дві таблиці, бо ви востаннє дали інший опис предметої області. Ви питаєте про якусь іншу структуру? Яку саме?

11

Re: MS Sql. Поясніть за зовнішні ключі.

koala написав:
TRYCUKI_V_KROVI написав:

то все дуже круто, я потім почитаю, як прийду з церюльні. Я то все роблю в sql, а не access. Я не любаю access і всякі там екселі взагалі. Ну от ви, koala, радите зробити той ключ в таблиці Book. А можна ж розбити то на дві таблиці? В одній таблиці id і ім'я книги, а в іншій id книги і її жанр? Ну то просто щоб більше таблиць було хД

Я давав різні поради для різних запропонованих вами структур. Остання порада була зробити дві таблиці, бо ви востаннє дали інший опис предметої області. Ви питаєте про якусь іншу структуру? Яку саме?

да нє, про ту саму. добре добре, зроблю в дві таблиці  :-X

12

Re: MS Sql. Поясніть за зовнішні ключі.

ALTER TABLE  Books_Genre
ADD CONSTRAINT B_ID
FOREIGN KEY (Book_id) 
REFERENCES Books(id)

Після додавання ключа мілкосервер сам пильнуватиме коректність даних у book_id, і спроба INSERT з чимось лівим викличе фейл.

Що ж до структури: згодний з koala щодо нелогічності таких конектів. Куди пристойнішим виглядатиме схема з третьою таблицею:

Books:
   - Id PK
   - name

Genres:
   - Id PK
   - Name

BookGenreTags:
   - Id (autoincrement) PK
   - Book_Id (reference to Books.Id) FK
   - Genre_Id (reference to Genres.Id) FK

Суть у тому, що книжка може фактично належати до кількох жанрів, а певний жанр містити бібліотеку з декількох книжок, себто зв'язки "багато-до-багатьох".

13

Re: MS Sql. Поясніть за зовнішні ключі.

які ще конекти? ви такі смішні, koala каже - використовуй дві таблиці! А  барташ каже - згоден з коалою, буде гарніше з трьома таблицями.

14

Re: MS Sql. Поясніть за зовнішні ключі.

А ви читайте уважніше - і не буде смішно. Барташ погодився зі мною з питання зв'язки (посилання на книгу в таблиці "жанри" - дурня). А дві чи три таблиці - залежить від типу зв'язку (1:n чи n:n). Визначитеся, який зв'язок - тоді буде конкретна порада, як робити.