Боби 8. Тарҳрезии ҷадвалҳо

Таваҷҷуҳи зиёд ба тартиб ва ҷузъиёт метавонад хуб бошад. Вақте саросема аз хона мебароед, дидани калидҳо дар ҳамон ҷое, ки ҳамеша мегузоред, хотирҷамъатон мекунад. Дар тарҳрезии пойгоҳи додаҳо низ ҳамин тавр аст. Вақте бояд аз миёни даҳҳо ҷадвал ва миллионҳо сатр як пораи маълумоти арзишмандро пайдо кунед, қадри чунин диққат ба ҷузъиётро мефаҳмед. Агар додаҳо дар маҷмӯи ҷадвалҳои дуруст танзимшуда ва фаҳмо номгузоришуда ҷойгир бошанд, таҳлили онҳо хеле осонтар мешавад.

Дар ин боб мавзуъҳои боби 7-ро идома дода, усулҳои беҳтарини ташкил ва тезонидани кори пойгоҳҳои додаҳои SQL-ро меомӯзем. Ин усулҳо ҳам барои пойгоҳҳои сохтаи худатон ва ҳам барои онҳое, ки барои таҳлил ба шумо супурда мешаванд, муфиданд.

Мо тарҳрезии ҷадвалҳоро амиқтар баррасӣ мекунем: қоидаҳо ва анъанаҳои номгузорӣ, роҳҳои нигоҳ доштани якпорчагии додаҳо ва илова кардани индексҳо барои тезонидани дархостҳо.

Риояи қоидаҳои номгузорӣ

Забонҳои барномасозӣ одатан услубҳои хоси худро доранд. Ҳатто гурӯҳҳои гуногуни барномасозони SQL барои номгузории ҷадвалҳо, сутунҳо ва объектҳои дигар — ки онҳоро шиносаҳо — identifiers меноманд — қоидаҳои гуногунро меписанданд.

Баъзеҳо услуби camelCase-ро дӯст медоранд, масалан:

berrySmoothie  

Дар ин услуб калимаҳо ба ҳам часпида навишта мешаванд ва ҳарфи аввали ҳамаи калимаҳо, ғайр аз калимаи якум, калон аст.

Услуби PascalCase монанд аст, вале ҳарфи аввали калимаи якум низ калон навишта мешавад:

BerrySmoothie  

Дар услуби snake_case ҳамаи калимаҳо бо ҳарфҳои хурд навишта шуда, бо аломати зерхат аз ҳам ҷудо мешаванд:

berry_smoothie  

Ҳар яке аз ин услубҳо ҷонибдорони худро дорад. Баъзе афзалиятҳо ба низоми мушаххаси пойгоҳи додаҳо ё забони барномасозӣ вобастаанд. Масалан, Microsoft дар ҳуҷҷатҳои пойгоҳи додаҳои SQL Server аз PascalCase истифода мебарад.

Дар ин китоб, бо сабабҳои вобаста ба PostgreSQL, ки каме баъд шарҳ медиҳам, аз snake_case истифода мекунем. Масалан:

districts_pop_2020  

Новобаста аз он ки кадом услубро меписандед ё маҷбуред истифода баред, муҳим аст онро пайваста риоя кунед. Санҷед, ки ташкилоти шумо дастури услуб дорад ё не. Агар надошта бошад, метавонед дар таҳияи он ҳамкорӣ кунед ва баъдан онро дақиқ риоя намоед.

Омехтани услубҳо ё умуман риоя накардани ягон қоида одатан боиси бесарусомонӣ мешавад. Масалан, тасаввур кунед ба пойгоҳи додаҳо пайваст шуда, чунин ҷадвалҳоро мебинед:

Customers  
customers  
custBackup  
customer_analysis  
customer_test2  
customer_testMarch2012  
customeranalysis  

Албатта, саволҳо пайдо мешаванд. Пеш аз ҳама: кадом ҷадвал воқеан додаҳои ҷории муштариёнро нигоҳ медорад?

Номгузории бетартиб ва набудани низом ба дигарон фаҳмидани додаҳои шуморо душвор мекунад. Барои худатон ҳам баъдтар аз ҳамон ҷойе, ки корро қатъ карда будед, идома додан мушкил мешавад.

Биёед нуктаҳои муҳими номгузории шиносаҳо ва тавсияҳои амалиро баррасӣ кунем.

Бо нохунак нигоҳ доштани ҳарфҳои калон ва хурд

PostgreSQL шиносаҳои бидуни нохунакро ба ҳарфҳои хурд табдил медиҳад. Барои нигоҳ доштани ҳарфҳои калон бояд шиносаро дар нохунакҳои дугона ҷойгир кунед.

Ин ду фармонро бинед:

CREATE TABLE customers (  
    customer_id text  
    -- қисми боқимонда нишон дода нашудааст  
);

CREATE TABLE Customers (  
    customer_id text  
    -- қисми боқимонда нишон дода нашудааст  
);  

Агар онҳоро пайдарпай иҷро кунед, фармони аввал ҷадвали customers-ро месозад. Фармони дуюм ба ҷойи сохтани ҷадвали ҷудогонаи Customers хато медиҳад:

relation "customers" already exists  

Яъне ҷадвали customers аллакай вуҷуд дорад.

Азбаски шиносаро дар нохунак нагирифтаед, PostgreSQL номҳои customers ва Customers-ро яксон мешуморад.

Барои нигоҳ доштани ҳарфи калон ва сохтани ҷадвали ҷудогона бо номи Customers, бояд чунин нависед:

CREATE TABLE "Customers" (  
    customer_id serial  
    -- қисми боқимонда нишон дода нашудааст  
);  

Аммо баъдан барои дархост аз Customers, на аз customers, бояд номи онро дар SELECT низ дар нохунак гиред:

SELECT * FROM "Customers";  

Ҳамеша дар хотир нигоҳ доштани ин талабот метавонад нороҳаткунанда бошад ва эҳтимоли иштибоҳро зиёд кунад. Бовар ҳосил кунед, ки номи ҷадвалҳо фаҳмо буда, аз номи ҷадвалҳои дигари пойгоҳи додаҳо ба таври равшан фарқ мекунад.

Мушкилоти истифодаи нохунак дар шиносаҳо

Дар нохунак гирифтани шиносаҳо имкон медиҳад аломатҳоеро истифода баред, ки дар ҳолати одӣ иҷозат дода намешаванд, аз ҷумла фосила.

Ин ба баъзеҳо писанд меояд, вале камбудиҳо низ дорад. Масалан, дар пойгоҳи додаҳои барқарорсозии ҷангал шояд хоҳед сутунеро чунин номгузорӣ кунед:

"trees planted"  

Аммо баъдан ҳар корбар бояд дар ҳар муроҷиат ба ин сутун номи онро дар нохунак гирад. Агар нохунакҳо фаромӯш шаванд, пойгоҳи додаҳо trees ва planted-ро ҳамчун ҷузъҳои ҷудогона тафсир карда, метавонад хабар диҳад, ки сутуни trees вуҷуд надорад.

Варианти хонданбобтар ва боэътимодтар истифодаи snake_case аст:

trees_planted  

Нохунакҳо ҳамчунин имкон медиҳанд калимаҳои калидии захирашудаи SQL-ро ҳамчун ном истифода баред. Инҳо калимаҳое мебошанд, ки дар SQL маънои махсус доранд. Шумо аллакай бо чанде аз онҳо шинос ҳастед:

TABLE  
WHERE  
SELECT  

Аксари таҳиягарони пойгоҳи додаҳо истифодаи чунин калимаҳоро ҳамчун шиноса намеписанданд. Ин ҳадди ақал боиси нофаҳмӣ мешавад. Дар ҳолати бадтар, агар баъдтар нохунакҳоро фаромӯш кунед, пойгоҳи додаҳо калимаро ҳамчун қисми фармон мефаҳмад ва хато медиҳад.

Эзоҳ: Рӯйхати калимаҳои калидии PostgreSQL дар ҳуҷҷатҳои расмӣ оварда шудааст. Илова бар ин, бисёр муҳаррирҳои код ва абзорҳои пойгоҳи додаҳо, аз ҷумла pgAdmin, калимаҳои калидиро бо ранги махсус нишон медиҳанд.

Тавсияҳо барои номгузории шиносаҳо

Бо дарназардошти душвориҳои иловагии нохунакгузорӣ ва мушкилоти эҳтимолии он, беҳтар аст номҳои шиносаҳо сода, бидуни нохунак ва бо услуби ягона бошанд.

Тавсияҳои ман чунинанд:

• Аз snake_case истифода баред. Мисоли trees_planted нишон медиҳад, ки ин услуб ҳам хонданбоб ва ҳам боэътимод аст. Он дар тамоми ҳуҷҷатҳои расмии PostgreSQL истифода мешавад ва фаҳмидани номҳои чандкалимагиро осон мекунад. Масалан, маънои video_on_demand аз videoondemand зудтар фаҳмида мешавад.
• Номҳои фаҳмо интихоб кунед ва аз ихтисораҳои норавшан худдорӣ намоед. Агар пойгоҳи додаҳои сафарҳоро месозед, arrival_time — вақти расидан — аз arv_tm фаҳмотар аст.
• Номи ҷадвалҳоро дар шакли ҷамъ нависед. Ҷадвалҳо сатрҳо доранд ва ҳар сатр як намунаи объектро ифода мекунад. Аз ин рӯ, номҳое ба монанди teachers, vehicles ва departments муносибанд. Баъзан ман истисно мекунам: масалан, барои нигоҳ доштани номи файли CSV-и воридшуда ҳамон номро ба ҷадвал медиҳам, махсусан ҳангоми воридоти якдафъаина.
• Ба дарозии ном диққат диҳед. Ҳадди дарозии шиноса аз низоми пойгоҳи додаҳо вобаста аст. Дар стандарти SQL он 128 аломат аст, PostgreSQL ҳадди 63 дорад ва низомҳои кӯҳнаи Oracle ҳадди 30 доранд. Агар код дар низоми дигар низ истифода шавад, номҳои кӯтоҳтарро интихоб кунед.
• Ҳангоми нусхабардории ҷадвал номеро интихоб кунед, ки идоракунии минбаъдаро осон кунад. Як усул илова кардани сана бо шакли _YYYY_MM_DD аст, масалан vehicle_parts_2021_04_08. Бартарии дигар ин аст, ки чунин номҳо аз рӯйи сана дуруст ҷобаҷо мешаванд.

Назорати қиматҳои сутунҳо бо маҳдудиятҳо

Бо истифода аз маҳдудиятҳо — constraints метавонед назорати бештаре бар додаҳои қабулшавандаи сутун дошта бошед.

Навъи додаҳои сутун ба таври умумӣ муайян мекунад, ки он чӣ гуна додаҳоро қабул мекунад: масалан, ададҳои бутун ё матн. Маҳдудиятҳои иловагӣ имкон медиҳанд қиматҳои қабулшавандаро бо қоидаҳо ва санҷишҳои мантиқӣ дақиқтар муайян кунем.

Бо маҳдудиятҳо метавон аз ҳолати «додаҳои бад ворид мешаванд — натиҷаҳои бад мебароянд» пешгирӣ кард. Ин ҳолат вақте рух медиҳад, ки додаҳои бесифат ба таҳлили нодуруст ё нопурра оварда мерасонанд.

Маҳдудиятҳои хуб тарҳрезишуда ба нигоҳ доштани сифати додаҳо ва якпорчагии робитаҳои байни ҷадвалҳо кумак мекунанд.

Дар боби 7 бо калидҳои асосӣ ва хориҷӣ шинос шудед. Инҳо ду навъи хеле маъмули маҳдудиятанд. SQL навъҳои зеринро низ дорад:

Маҳдудиятҳоро бо ду роҳ илова кардан мумкин аст: ҳамчун маҳдудияти сутун ё маҳдудияти ҷадвал.

Маҳдудияти сутун танҳо ба ҳамон сутун дахл дорад. Онро дар фармони CREATE TABLE ҳамроҳи номи сутун ва навъи додаҳояш эълон мекунем. Ҳангоми тағйир ёфтани қимати сутун маҳдудият санҷида мешавад.

Бо маҳдудияти ҷадвал метавон шартҳоеро муайян кард, ки ба як ё чанд сутун дахл доранд. Онро дар CREATE TABLE, пас аз муайян кардани ҳамаи сутунҳо, менависем. Ҳангоми тағйир ёфтани сатри ҷадвал шарт санҷида мешавад.

Биёед ин маҳдудиятҳо, синтаксис ва фоидаи онҳоро дар тарҳрезии ҷадвалҳо омӯзем.

Калидҳои асосӣ: табиӣ ва сунъӣ

Тавре дар боби 7 омӯхтем, калиди асосӣ як сутун ё маҷмӯи сутунҳоест, ки қиматҳояшон ҳар сатри ҷадвалро ба таври ягона муайян мекунанд.

Калиди асосӣ маҳдудиятест, ки ду қоидаро ҷорӣ мекунад:

• Қиматҳо бояд барои ҳар сатр беназир бошанд.
• Ҳеҷ яке аз сутунҳои калид набояд қимати NULL дошта бошад.

Дар ҷадвали маҳсулоти анбор калиди асосӣ метавонад сутуни кодҳои беназири маҳсулот бошад.

Дар мисолҳои одии бахши «Алоқаманд кардани ҷадвалҳо тавассути сутунҳои калидӣ»-и боби 7, калиди асосӣ аз як сутуни ID иборат буд. Мо ҳамчун корбар ба он ададҳои бутун ворид мекардем.

Аксар вақт худи додаҳо роҳи беҳтарро нишон медиҳанд ва ба мо кумак мекунанд, ки барои калиди асосӣ калиди табиӣ — natural key ё калиди сунъӣ — surrogate key-ро интихоб кунем.

Истифодаи сутунҳои мавҷуда ҳамчун калиди табиӣ

Калиди табиӣ аз як ё чанд сутуни мавҷудаи ҷадвал сохта мешавад, ки ба талаботи калиди асосӣ мувофиқанд: барои ҳар сатр беназиранд ва ҳеҷ гоҳ NULL нестанд.

Қиматҳои ин сутунҳо метавонанд тағйир ёбанд, ба шарте ки қимати нав маҳдудиятро вайрон накунад.

Масалан, рақами мушаххаскунандаи шаҳодатномаи ронандагӣ, ки идораи маҳаллии воситаҳои нақлиёт медиҳад, метавонад калиди табиӣ бошад.

Дар доираи як воҳиди маъмурӣ, масалан як иёлати ИМА, метавон интизор шуд, ки ҳамаи ронандагон рақами беназир мегиранд. Мо онро дар сутуни driver_id нигоҳ медорем.

Аммо агар пойгоҳи умумимиллии шаҳодатномаҳои ронандагиро созем, дигар чунин фарз кардан мумкин нест: чанд иёлат метавонанд мустақилона рақами якхела дода бошанд.

Дар ин ҳолат қиматҳои driver_id беназир намешаванд ва ин сутунро танҳо ҳамчун калиди табиӣ истифода бурда наметавонем.

Як роҳи ҳал сохтани калиди асосии таркибӣ аз driver_id ва сутуни номи иёлат аст. Таркиби онҳо барои ҳар сатр беназир мешавад.

Масалан, дар ин ду сатр таркиби driver_id ва st беназир аст:

Дар ин боб ҳар ду усулро меомӯзем. Ҳангоми кор бо додаҳо ба қиматҳое диққат диҳед, ки барои калиди табиӣ муносибанд. Рақами ҷузъ, рақами силсилавӣ ё ISBN-и китоб мисолҳои хуб мебошанд.

Илова кардани сутун барои калиди сунъӣ

Калиди сунъӣ як сутуни алоҳидаест, ки онро бо қиматҳои махсус барои муайян кардани сатрҳо пур мекунем. Онро метавон вақте истифода бурд, ки додаҳои ҷадвал барои сохтани калиди табиӣ муносиб нестанд.

Калиди сунъӣ метавонад рақами пайдарпайе бошад, ки пойгоҳи додаҳо худкор месозад. Мо аллакай инро бо навъи serial ва синтаксиси IDENTITY анҷом дода будем. Онҳо дар бахши «Ададҳои бутуни худкор афзоянда»-и боби 5 шарҳ дода шудаанд.

Ҷадвале, ки барои калиди сунъӣ адади бутуни худкортавлидшавандаро истифода мебарад, метавонад чунин бошад:

Баъзе таҳиягарон истифодаи UUID — шиносаи универсалии беназир-ро меписанданд. Он кодест аз 32 рақами шонздаҳӣ, ки ба гурӯҳҳо ҷудо шуда, байни гурӯҳҳо дефис гузошта мешавад.

UUID-ҳо аксар вақт барои муайян кардани сахтафзор ё нармафзор истифода мешаванд ва чунин менамоянд:

2911d8a8-6dea-4a46-af23-d64175a08237  

PostgreSQL навъи додаҳои UUID ва модулҳои uuid-ossp ва pgcrypto-ро барои кор бо UUID пешниҳод мекунад. Барои омӯзиши бештар метавонед аз ҳуҷҷатҳои PostgreSQL оид ба UUID оғоз кунед.

Эзоҳ: Ҳангоми интихоби UUID ҳамчун калиди сунъӣ ба андозаи он диққат диҳед. Он нисбат ба имконоте ба монанди bigint ҷойи бештар мегирад ва метавонад хароҷоти бештар дошта бошад.

Арзёбии бартарӣ ва камбудиҳои навъҳои калид

Барои истифодаи ҳар ду навъи калиди асосӣ далелҳои асоснок вуҷуд доранд, вале ҳар ду камбудиҳо низ доранд.

Ҳангоми интихоби калиди табиӣ инҳоро ба назар гиред:

• Додаҳо аллакай дар ҷадвал ҳастанд, бинобар ин барои сохтани калид сутуни нав лозим нест.
• Азбаски қимати калиди табиӣ маъно дорад, он метавонад эҳтиёҷ ба пайваст кардани ҷадвалҳоро кам кунад.
• Агар додаҳо тавре тағйир ёбанд, ки талаботи калид вайрон шавад — масалан, ногаҳон қиматҳои такрорӣ пайдо шаванд — бояд сохтори ҷадвалро тағйир диҳед.

Ҳангоми интихоби калиди сунъӣ инҳоро ба назар гиред:

• Азбаски калиди сунъӣ худ аз худ маънои соҳавӣ надорад ва аз додаҳои дигари ҷадвал мустақил аст, тағйироти ояндаи додаҳо шуморо бо сохтори калид маҳдуд намекунад.
• Қиматҳои калид бояд беназир бошанд.
• Илова кардани сутуни калиди сунъӣ ҷойи бештар талаб мекунад.

Дар шароити беҳтарин ҷадвал бояд як ё чанд сутуни муносиб барои калиди табиӣ дошта бошад. Масалан, коди беназири маҳсулот дар ҷадвали маҳсулот.

Аммо дар амал маҳдудиятҳо ҳамеша пеш меоянд. Дар ҷадвали кормандон ёфтани як сутун ё ҳатто маҷмӯи сутунҳое, ки барои ҳар сатр беназир бошанд, метавонад душвор бошад. Агар сохтори ҷадвалро аз нав баррасӣ карда натавонед, шояд истифодаи калиди сунъӣ зарур шавад.

Сохтани калиди асосии яксутуна

Биёед чанд мисоли калиди асосиро омӯзем.

Дар бахши «Фаҳмидани навъҳои JOIN»-и боби 7 барои ҷадвалҳои district_2020 ва district_2035 калидҳои асосӣ сохта будед. Онҳо дар асл калидҳои сунъӣ буданд: дар ҳар ду ҷадвал сутуни id сохтед ва бо сохтори зерин онро калиди асосӣ эълон кардед:

CONSTRAINT key_name PRIMARY KEY  

Маҳдудиятҳоро бо ду роҳ эълон кардан мумкин аст: дар сатҳи сутун ва дар сатҳи ҷадвал.

Дар намунаи 8-1 ҳар ду усулро барои ҷадвале месанҷем, ки ба мисоли шаҳодатномаҳои ронандагӣ монанд аст. Азбаски интизорем рақами шаҳодатнома ҳамеша беназир бошад, онро ҳамчун калиди табиӣ истифода мебарем.

CREATE TABLE natural_key_example (  
    license_id text CONSTRAINT license_key PRIMARY KEY,  
    first_name text,  
    last_name text  
);

DROP TABLE natural_key_example;

CREATE TABLE natural_key_example (  
    license_id text,  
    first_name text,  
    last_name text,  
    CONSTRAINT license_key PRIMARY KEY (license_id)  
);  

Намунаи 8-1: Эълони калиди табиии яксутуна ҳамчун калиди асосӣ.

Аввал ҷадвали natural_key_example-ро месозем. Барои сутуни license_id калимаи CONSTRAINT, номи маҳдудият ва PRIMARY KEY-ро менависем.

Ин синтаксис имкон медиҳад зуд фаҳмем, ки кадом сутун калиди асосӣ аст.

Метавонед CONSTRAINT ва номи маҳдудиятро нанависед ва танҳо чунин истифода баред:

license_id text PRIMARY KEY  

Дар ин ҳолат PostgreSQL худаш номи калиди асосиро месозад: номи ҷадвал ва пасванди _pkey.

Баъдан бо DROP TABLE ҷадвалро нест мекунем, то барои мисоли маҳдудияти сатҳи ҷадвал омода шавем.

Барои илова кардани маҳдудияти ҷадвал, CONSTRAINT-ро пас аз рӯйхати ҳамаи сутунҳо менависем ва номи сутуни калидиро дар қавс мегузорем. Дар ин ҷо низ метавон CONSTRAINT ва номи маҳдудиятро нанавишт.

Дар ҳар ду мисол license_id калиди асосӣ мешавад.

Агар калиди асосиро аз чанд сутун сохтан хоҳед, бояд синтаксиси маҳдудияти ҷадвалро истифода баред. Дар ин ҳолат номи сутунҳо дар қавс бо вергул ҷудо мешавад. Каме баъд инро меомӯзем.

Аввал мебинем, ки ду хусусияти калиди асосӣ — беназирӣ ва манъи NULL — чӣ гуна якпорчагии додаҳоро муҳофизат мекунанд.

Намунаи 8-2 ду фармони INSERT дорад:

INSERT INTO natural_key_example (license_id, first_name, last_name)  
VALUES ('T229901', 'Gem', 'Godfrey');

INSERT INTO natural_key_example (license_id, first_name, last_name)  
VALUES ('T229901', 'John', 'Mitchell');  

Намунаи 8-2: Мисоли вайрон кардани маҳдудияти калиди асосӣ.

Агар INSERT-и аввалро алоҳида иҷро кунед, сервер сатрро бе мушкил ба ҷадвал илова мекунад.

Ҳангоми иҷрои дуюм сервер чунин хато медиҳад:

ERROR:  duplicate key value violates unique constraint "license_key"  
DETAIL:  Key (license_id)=(T229901) already exists.  

Яъне қимати такрории калид маҳдудияти беназирии license_key-ро вайрон мекунад: қимати T229901 аллакай вуҷуд дорад.

Пеш аз илова кардани сатр сервер санҷид, ки оё чунин license_id дар ҷадвал ҳаст. Азбаски он вуҷуд дошт ва калиди асосӣ бояд барои ҳар сатр беназир бошад, сервер амалро рад кард.

Қоидаи идораи фарзии шаҳодатномаҳои ронандагӣ мегӯяд, ки ду ронанда набояд рақами якхела дошта бошанд. Санҷиш ва рад кардани додаҳои такрорӣ яке аз роҳҳои иҷрои ҳамин қоида дар пойгоҳи додаҳост.

Сохтани калиди асосии таркибӣ

Агар як сутун ба талаботи калиди асосӣ ҷавобгӯ набошад, метавон калиди асосии таркибӣ сохт.

Ҷадвале месозем, ки ҳозиршавии хонандагонро ба мактаб сабт мекунад. Таркиби сутунҳои student_id ва school_day ҳар сатрро беназир мекунад. Сутуни present нишон медиҳад, ки хонанда дар он рӯз ҳозир буд ё не.

Барои сохтани калиди асосии таркибӣ бояд маҳдудиятро дар сатҳи ҷадвал эълон кунед:

CREATE TABLE natural_key_composite_example (  
    student_id text,  
    school_day date,  
    present boolean,  
    CONSTRAINT student_key PRIMARY KEY (student_id, school_day)  
);  

Намунаи 8-3: Эълони калиди асосии таркибӣ ҳамчун калиди табиӣ.

Дар ин ҷо ба ҷойи як сутун ду сутунро нишон медиҳем. Шумора метавонад бештар ҳам бошад.

Барои санҷидани маҳдудият кӯшиш мекунем сатре илова кунем, ки таркиби student_id ва school_day-и он дар ҷадвал аллакай мавҷуд аст.

Фармонҳои намунаи 8-4-ро як-як иҷро кунед. Дар pgAdmin пеш аз пахш кардани Execute ҳар фармонро алоҳида интихоб намоед.

INSERT INTO natural_key_composite_example (student_id, school_day, present)  
VALUES (775, '2022-01-22', 'Y');

INSERT INTO natural_key_composite_example (student_id, school_day, present)  
VALUES (775, '2022-01-23', 'Y');

INSERT INTO natural_key_composite_example (student_id, school_day, present)  
VALUES (775, '2022-01-23', 'N');  

Намунаи 8-4: Мисоли вайрон кардани маҳдудияти калиди асосии таркибӣ.

Ду фармони аввал дуруст иҷро мешаванд, зеро таркиби қиматҳои сутунҳои калидӣ такрор намешавад.

Аммо фармони сеюм хато медиҳад, зеро таркиби student_id ва school_day-и он дар ҷадвал аллакай вуҷуд дорад:

ERROR:  duplicate key value violates unique constraint "student_key"  
DETAIL:  Key (student_id, school_day)=(775, 2022-01-23) already exists.  

Метавонед калиди таркибиро аз зиёда аз ду сутун созед. Ҳадди шумораи сутунҳо аз низоми пойгоҳи додаҳо вобаста аст.

Сохтани калиди сунъии худкор афзоянда

Тавре дар бахши «Ададҳои бутуни худкор афзоянда»-и боби 5 омӯхтед, дар PostgreSQL ду роҳи илова кардани қимати беназири худкор афзоянда ба сутун вуҷуд дорад.

Роҳи аввал истифодаи навъҳои хоси PostgreSQL аст:

smallserial  
serial  
bigserial  

Роҳи дуюм истифодаи синтаксиси IDENTITY мебошад. Азбаски он қисми стандарти ANSI SQL аст, дар мисолҳо аз он истифода мебарем.

IDENTITY-ро бо яке аз навъҳои адади бутун истифода баред:

smallint  
integer  
bigint  

Барои калиди асосӣ шояд хоҳед ҷойи дискро сарфа карда, integer-ро истифода баред. Он ададҳои то 2 147 483 647-ро қабул мекунад.

Аммо бисёр таҳиягарони пойгоҳи додаҳо шабона аз корбаре занг гирифтаанд, ки барномааш ногаҳон аз кор мондааст. Сабаб баъзан ин будааст, ки пойгоҳи додаҳо кӯшиш кардааст ададеро тавлид кунад, ки аз ҳадди навъи додаҳо як воҳид зиёд аст.

Аз ин рӯ, агар ҳатто эҳтимоли каме бошад, ки ҷадвал аз тақрибан 2,147 миллиард сатр зиёд мешавад, истифодаи bigint оқилона аст. Он ададҳои то тақрибан 9,2 × 10¹⁸-ро қабул мекунад.

Мисоли зеринро бинед:

CREATE TABLE surrogate_key_example (  
    order_number bigint GENERATED ALWAYS AS IDENTITY,  
    product_name text,  
    order_time timestamp with time zone,  
    CONSTRAINT order_number_key PRIMARY KEY (order_number)  
);

INSERT INTO surrogate_key_example (product_name, order_time)  
VALUES ('Beachball Polish', '2020-03-15 09:21-07'),  
       ('Wrinkle De-Atomizer', '2017-05-22 14:00-07'),  
       ('Flux Capacitor', '1985-10-26 01:18:00-07');

SELECT * FROM surrogate_key_example;  

Намунаи 8-5: Эълони сутуни bigint ҳамчун калиди сунъӣ бо IDENTITY.

Дар ин мисол сутуни order_number-ро бо навъи bigint ва синтаксиси IDENTITY худкор афзоянда карда, сипас онро калиди асосӣ муайян мекунем.

Ҳангоми ворид кардани додаҳо order_number-ро ба рӯйхати сутунҳо ва қиматҳо дохил намекунем. Пойгоҳи додаҳо барои ҳар сатри нав қиматро худкор аз пайдарпайӣ мегирад.

Барои дидани натиҷа иҷро кунед:

SELECT * FROM surrogate_key_example;  

Чунин рақамҳои худкор афзояндаи фармоишро дар расидҳои харидҳои ҳаррӯза мебинем. Акнун медонед, ки онҳо чӣ гуна сохта мешаванд.

Эзоҳ: Вақтҳои сутуни order_time дар натиҷаи шумо вобаста ба танзими минтақаи вақти PostgreSQL метавонанд фарқ кунанд. Ин мавзуъ дар боби 5 шарҳ дода шудааст.

Чанд ҷузъиёти муҳим ҳаст. Агар сатрро нест кунед, пойгоҳи додаҳо ҷойи холии рақами онро дар пайдарпайӣ пур намекунад ва қиматҳои мавҷудаи сутунро низ тағйир намедиҳад. Пайдарпайӣ одатан аз қимати қаблан тавлидшуда идома меёбад.

Мо синтаксиси зеринро истифода кардем:

GENERATED ALWAYS AS IDENTITY  

Тавре дар боби 5 шарҳ дода шуд, ин танзим ворид кардани қимати дастиро ба order_number манъ мекунад, магар он ки корбар онро махсусан бекор кунад. Одатан беҳтар аст чунин дахолат пешгирӣ шавад.

Фарз мекунем корбар ба сутуни order_number-и ҷадвали мавҷудаи мо қимати 4-ро дастӣ ворид кард. Воридоти дастӣ пайдарпайии IDENTITY-ро пеш намебарад: он танҳо ҳангоми тавлиди худкори қимат пеш меравад.

Аз ин рӯ, ҳангоми ворид кардани сатри навбатӣ пойгоҳи додаҳо низ кӯшиш мекунад 4-ро гузорад, зеро рақами навбатии пайдарпайӣ ҳамин аст. Натиҷа хато мешавад, чун қимати такрорӣ маҳдудияти калиди асосиро вайрон мекунад.

Бо вуҷуди ин, метавонед воридоти дастиро анҷом дода, сипас пайдарпайии IDENTITY-ро аз нав танзим кунед. Масалан, ин метавонад барои барқарор кардани сатри тасодуфан нестшуда лозим шавад.

Намунаи 8-6 илова кардани сатри дорои order_number = 4-ро нишон медиҳад:

INSERT INTO surrogate_key_example  
OVERRIDING SYSTEM VALUE  
VALUES (4, 'Chicken Coop', '2021-09-03 10:33-07');

ALTER TABLE surrogate_key_example  
ALTER COLUMN order_number RESTART WITH 5;

INSERT INTO surrogate_key_example (product_name, order_time)  
VALUES ('Aloe Plant', '2020-03-15 10:09-07');  

Намунаи 8-6: Аз нав оғоз кардани пайдарпайии IDENTITY.

Аввал INSERT-еро менависем, ки калимаҳои зеринро дорад:

OVERRIDING SYSTEM VALUE  

Сипас дар VALUES барои сутуни якум — order_number — адади 4-ро медиҳем. Ин иҷозат медиҳад маҳдудияти воридоти дастии IDENTITY барои ҳамин амал бекор шавад.

Мо 4-ро истифода кардем, вале метавонем ҳар адади дар сутун ҳанӯз мавҷуднабударо интихоб кунем.

Пас аз воридот бояд пайдарпайиро тавре танзим кунем, ки аз адади калонтар аз 4 оғоз шавад. Барои ин аз ALTER TABLE ... ALTER COLUMN бо RESTART WITH 5 истифода мебарем.

ALTER TABLE ҷадвалҳо ва сутунҳоро бо роҳҳои гуногун тағйир медиҳад. Ҳамаи имкониятҳои он дар ҳуҷҷатҳои PostgreSQL оварда шудаанд.

Дар ин ҷо он рақами оғози пайдарпайии IDENTITY-ро тағйир медиҳад. Аз ин рӯ, ҳангоми илова кардани сатри навбатӣ қимати order_number ба 5 баробар мешавад.

Ниҳоят, сатри навро бе нишон додани order_number илова мекунем.

Агар боз ҳамаи сатрҳоро интихоб кунед, натиҷа чунин мешавад:

Эҳтимол ин амалро зуд-зуд иҷро кардан нахоҳед, вале донистани он ҳангоми зарурат муфид аст.

Калидҳои хориҷӣ

Калидҳои хориҷиро барои барқарор кардани робитаи байни ҷадвалҳо истифода мебарем.

Калиди хориҷӣ як ё чанд сутунест, ки қиматҳояшон ба қиматҳои калиди асосӣ ё дигар калиди беназири ҷадвали истинодшаванда мувофиқат мекунанд.

Қиматҳои калиди хориҷӣ бояд дар калиди асосӣ ё калиди беназири ҷадвали манбаъ аллакай вуҷуд дошта бошанд. Дар акси ҳол, қимат рад мешавад.

Бо ин маҳдудият SQL якпорчагии истинодӣ — referential integrity-ро таъмин мекунад: додаҳои ҷадвалҳои алоқаманд набояд бе робита ё «ятим» монанд. Яъне сабте надошта бошем, ки ба сабти мавҷуднабуда ишора мекунад.

Намунаи 8-7 ду ҷадвали пойгоҳи додаҳои фарзиро барои сабти фаъолияти воситаҳои нақлиёт нишон медиҳад:

CREATE TABLE licenses (  
    license_id text,  
    first_name text,  
    last_name text,  
    CONSTRAINT licenses_key PRIMARY KEY (license_id)  
);

CREATE TABLE registrations (  
    registration_id text,  
    registration_date timestamp with time zone,  
    license_id text REFERENCES licenses (license_id),  
    CONSTRAINT registration_key PRIMARY KEY (registration_id, license_id)  
);

INSERT INTO licenses (license_id, first_name, last_name)  
VALUES ('T229901', 'Steve', 'Rothery');

INSERT INTO registrations (registration_id, registration_date, license_id)  
VALUES ('A203391', '2022-03-17', 'T229901');

INSERT INTO registrations (registration_id, registration_date, license_id)  
VALUES ('A75772', '2022-03-17', 'T000001');  

Намунаи 8-7: Мисоли калиди хориҷӣ.

Ҷадвали аввал — licenses — рақами беназири шаҳодатномаи ронандагӣ, яъне license_id-ро ҳамчун калиди табиии асосӣ истифода мебарад.

Ҷадвали дуюм — registrations — бақайдгирии воситаҳои нақлиётро сабт мекунад.

Як рақами шаҳодатнома метавонад бо чанд сабти бақайдгирии воситаи нақлиёт алоқаманд бошад, зеро як ронанда метавонад чанд воситаи нақлиётро ба қайд гирад. Ин робитаи як-ба-бисёр аст, ки дар боби 7 омӯхтем.

Дар SQL ин робитаро чунин ифода мекунем: дар ҷадвали registrations сутуни license_id-ро бо калимаи REFERENCES ва номи ҷадвалу сутуни манбаъ ҳамчун калиди хориҷӣ муайян мекунем:

license_id text REFERENCES licenses (license_id)  

Акнун ҳангоми ворид кардани сатр ба registrations, пойгоҳи додаҳо месанҷад, ки қимати license_id дар сутуни калиди асосии license_id-и ҷадвали licenses вуҷуд дорад ё не.

Агар вуҷуд надошта бошад, хато медиҳад. Ин муҳим аст: агар сабте дар registrations ба ягон сатри licenses мувофиқат накунад, бо дархост муайян карда наметавонем, ки воситаи нақлиётро кӣ ба қайд гирифтааст.

Барои санҷидани маҳдудият ду ҷадвалро созед ва фармонҳои INSERT-ро як-як иҷро кунед.

Фармони аввал ба licenses сатре бо license_id = 'T229901' илова мекунад.

Дуюм ба registrations сатре илова мекунад, ки калиди хориҷии он ҳамин қиматро дорад. То ин ҷо ҳама чиз дуруст аст.

Аммо фармони сеюм кӯшиш мекунад ба registrations қимати license_id-еро илова кунад, ки дар licenses нест. Аз ин рӯ, хато мегирем:

ERROR:  insert or update on table "registrations" violates foreign key constraint "registrations_license_id_fkey"  
DETAIL:  Key (license_id)=(T000001) is not present in table "licenses".  

Ин хато дар асл муфид аст: пойгоҳи додаҳо бо манъ кардани бақайдгирӣ барои соҳиби шаҳодатномаи мавҷуднабуда якпорчагии истинодиро нигоҳ медорад.

Аз ин чанд натиҷаи амалӣ бармеояд.

Аввал, тартиби ворид кардани додаҳо муҳим мешавад. То вақте сабти зарурӣ дар ҷадвали истинодшаванда вуҷуд надорад, наметавонем сабти вобастаи онро ба ҷадвали дорои калиди хориҷӣ илова кунем.

Дар ин мисол аввал бояд сабти шаҳодатномаи ронандагиро созем ва баъд сабти бақайдгирии воситаи нақлиёти ба он вобастаро илова кунем. Агар фикр кунед, эҳтимол идораи воқеии воситаҳои нақлиёт низ ҳамин тартибро риоя мекунад.

Дуюм, ҳангоми нест кардани додаҳо тартиб баръакс аст.

Барои нигоҳ доштани якпорчагии истинодӣ, маҳдудияти калиди хориҷӣ намегузорад сатри licenses-ро пеш аз нест кардани сатрҳои вобастаи registrations ҳазф кунем. Дар акси ҳол сабти «ятим» мемонад.

Бояд аввал сатри вобастаи registrations ва баъд сатри licenses-ро нест кунем.

Аммо ANSI SQL имкон медиҳад ин амалҳо бо ON DELETE CASCADE худкор иҷро шаванд.

Нест кардани худкори сабтҳои вобаста бо CASCADE

Барои он ки нест кардани як сатр аз licenses ҳамаи сатрҳои вобастаи онро дар registrations низ худкор нест кунад, ҳангоми муайян кардани калиди хориҷӣ ON DELETE CASCADE илова мекунем.

Фармони сохтани registrations аз намунаи 8-7-ро чунин тағйир медиҳем. Азбаски ҷадвал аллакай вуҷуд дорад, аввал онро нест мекунем:

DROP TABLE registrations;

CREATE TABLE registrations (  
    registration_id text,  
    registration_date date,  
    license_id text REFERENCES licenses (license_id) ON DELETE CASCADE,  
    CONSTRAINT registration_key PRIMARY KEY (registration_id, license_id)  
);  

Акнун нест кардани сатр аз licenses ҳамаи сатрҳои вобастаи онро дар registrations низ нест мекунад.

Инро бисанҷем. Бақайдгириро барои ронандаи T229901 боз илова мекунем ва баъд худи шаҳодатномаро бо фармони DELETE нест мекунем:

INSERT INTO registrations (registration_id, registration_date, license_id)  
VALUES ('A203391', '2022-03-17', 'T229901');

DELETE FROM licenses  
WHERE license_id = 'T229901';

SELECT * FROM registrations;  

Фармони DELETE FROM сатрҳоеро нест мекунад, ки ба шарти WHERE мувофиқанд. Мисли UPDATE, бе WHERE он ҳамаи сатрҳои ҷадвалро нест мекунад. PostgreSQL ҷавоб медиҳад DELETE 1 ва дархости охирин ягон сатр барнамегардонад: бақайдгирии вобаста ҳамроҳи шаҳодатнома худкор нест шуд.

Бо ин роҳ метавонем шаҳодатномаи ронандагиро бе он ки аввал ҳамаи бақайдгириҳои вобастаро дастӣ нест кунем, ҳазф намоем.

Ҳамзамон якпорчагии додаҳо нигоҳ дошта мешавад, зеро баъд аз нест кардани шаҳодатнома дар registrations сатрҳои бе соҳиб намемонанд.

Маҳдудияти CHECK

Маҳдудияти CHECK месанҷад, ки додаҳои ба сутун иловашаванда ба меъёрҳои пешбинишуда мувофиқанд ё не. Меъёрҳоро бо ифодаи мантиқӣ муайян мекунем. Агар шарт вайрон шавад, пойгоҳи додаҳо хато медиҳад.

CHECK хеле арзишманд аст, зеро метавонад аз ворид шудани додаҳои бемантиқ пешгирӣ кунад.

Масалан, шумораи умумии зарбаҳои муваффақи бозигари бейсбол набояд манфӣ бошад. Пас, ин додаҳоро бояд бо қиматҳои сифр ё калонтар маҳдуд кунем.

Ё дар аксари мактабҳое, ки низоми баҳогузории ҳарфӣ доранд, Z баҳои дурусти дарс нест — ҳарчанд баҳои базӯр гузаштаи ман аз алгебра гӯё ҳамин гуна буд! Аз ин рӯ, метавонем маҳдудияте гузорем, ки танҳо қиматҳои A то F-ро қабул кунад.

Мисли калиди асосӣ, CHECK-ро метавон дар сатҳи сутун ё ҷадвал муайян кард.

Барои маҳдудияти сутун онро пас аз номи сутун ва навъи додаҳо менависем:

CHECK (ифодаи_мантиқӣ)  

Барои маҳдудияти ҷадвал, пас аз муайян кардани ҳамаи сутунҳо чунин менависем:

CONSTRAINT constraint_name CHECK (ифодаи_мантиқӣ)  

Намунаи 8-8 ду маҳдудияти CHECK-ро дар ҷадвале нишон медиҳад, ки метавонад барои сабти нақши корбар ва музди меҳнати кормандон истифода шавад. Калиди асосӣ ва CHECK-ҳо бо синтаксиси маҳдудияти ҷадвал муайян шудаанд.

CREATE TABLE check_constraint_example (  
    user_id bigint GENERATED ALWAYS AS IDENTITY,  
    user_role text,  
    salary numeric(10,2),  
    CONSTRAINT user_id_key PRIMARY KEY (user_id),  
    CONSTRAINT check_role_in_list CHECK (user_role IN ('Admin', 'Staff')),  
    CONSTRAINT check_salary_not_below_zero CHECK (salary >= 0)  
);  

Намунаи 8-8: Мисолҳои маҳдудияти CHECK.

Ҷадвалро сохта, user_id-ро ҳамчун калиди асосии сунъии худкор афзоянда муайян мекунем.

CHECK-и аввал бо оператори IN месанҷад, ки қимати user_role яке аз ду сатри матнии пешакӣ муайяншуда бошад:

Admin  
Staff  

CHECK-и дуюм месанҷад, ки salary аз сифр калонтар ё ба он баробар бошад, зеро музди меҳнати манфӣ маъно надорад.

Ҳар ду санҷиш намунаи ифодаи мантиқӣ мебошанд. Агар натиҷаи санҷиш true бошад, шарт иҷро шудааст.

Эзоҳ: Таҳиягарон метавонанд дар бораи он ки санҷишҳо бояд дар пойгоҳи додаҳо, дар барномаи истифодабарандаи он — масалан низоми идоракунии кадрҳо — ё дар ҳар ду ҷой бошанд, назари гуногун дошта бошанд.

Як бартарии санҷишҳо дар худи пойгоҳи додаҳо ин аст, ки ҳатто ҳангоми тағйир ёфтани барнома, истифодаи пойгоҳ аз ҷониби барномаҳои нав ё дастрасии мустақими корбарон, якпорчагии додаҳо нигоҳ дошта мешавад.

Ҳангоми ворид ё нав кардани қиматҳо, пойгоҳи додаҳо онҳоро бо маҳдудият месанҷад. Агар қиматҳои яке аз сутунҳо шартро вайрон кунанд — ё маҳдудияти калиди асосӣ вайрон шавад — тағйирот рад мешавад.

Агар синтаксиси маҳдудияти ҷадвалро истифода барем, метавонем чанд санҷишро дар як CHECK якҷо кунем.

Фарз мекунем ҷадвали натиҷаҳои таҳсилии донишҷӯёнро дорем. Метавонем чунин маҳдудият илова кунем:

CONSTRAINT grad_check CHECK (credits >= 120 AND tuition = 'Paid')  

Дар ин ҷо ду санҷиши мантиқиро дар қавс гузошта, бо AND пайваст кардем. Барои иҷро шудани тамоми шарт ҳар ду бояд true бошанд: донишҷӯ на камтар аз 120 кредит дошта бошад ва маблағи таҳсил пардохт шуда бошад.

Ҳамчунин метавон қиматҳои сутунҳоро бо ҳам муқоиса кард. Масалан, агар сутунҳои нархи аслӣ ва нархи тахфифиро дошта бошем:

CONSTRAINT sale_check CHECK (sale_price < retail_price)  

Ифода месанҷад, ки нархи тахфифӣ аз нархи чакана пасттар бошад.

Эзоҳи тарҷумон: Дар PostgreSQL маҳдудияти CHECK танҳо натиҷаи false-ро рад мекунад; натиҷаи номаълуми вобаста ба NULL низ қабул мешавад. Барои ҳатман мавҷуд будани қимат, NOT NULL-ро ҳам илова кардан лозим аст.

Маҳдудияти UNIQUE

Бо UNIQUE низ метавон таъмин кард, ки қимати сутун дар ҳар сатр беназир бошад.

Агар ин ба вазифаи калиди асосӣ монанд намояд, дуруст аст. Аммо як фарқи муҳим вуҷуд дорад: калиди асосӣ NULL-ро қабул намекунад, дар ҳоле ки маҳдудияти одии UNIQUE метавонад дар як сутун чанд қимати NULL-ро иҷозат диҳад.

Ин вақте муфид аст, ки на ҳамеша қимат дорем, вале мехоҳем қиматҳои мавҷуда беназир бошанд.

Барои фаҳмидани фоидаи UNIQUE, ҷадвали маълумоти тамосро дар намунаи 8-9 бинед:

CREATE TABLE unique_constraint_example (  
    contact_id bigint GENERATED ALWAYS AS IDENTITY,  
    first_name text,  
    last_name text,  
    email text,  
    CONSTRAINT contact_id_key PRIMARY KEY (contact_id),  
    CONSTRAINT email_unique UNIQUE (email)  
);

INSERT INTO unique_constraint_example (first_name, last_name, email)  
VALUES ('Samantha', 'Lee', 'slee@example.org');

INSERT INTO unique_constraint_example (first_name, last_name, email)  
VALUES ('Betty', 'Diaz', 'bdiaz@example.org');

INSERT INTO unique_constraint_example (first_name, last_name, email)  
VALUES ('Sasha', 'Lee', 'slee@example.org');  

Намунаи 8-9: Мисоли маҳдудияти UNIQUE.

Дар ин ҷадвал contact_id калиди асосии сунъӣ аст ва ҳар сатрро ба таври ягона муайян мекунад.

Аммо сутуни email низ дорем, ки роҳи асосии тамос бо ҳар шахс аст. Интизорем суроғаҳои почтаи электронӣ беназир бошанд, вале онҳо метавонанд бо гузашти вақт тағйир ёбанд.

Аз ин рӯ, UNIQUE-ро истифода мебарем, то ҳангоми илова ё нав кардани суроғаи почтаи электронӣ қимати аллакай мавҷудбударо ворид накунем.

Агар суроғаи такрориро илова кунем, пойгоҳи додаҳо хато медиҳад:

ERROR:  duplicate key value violates unique constraint "email_unique"  
DETAIL:  Key (email)=(slee@example.org) already exists.  

Боз ҳам хато нишон медиҳад, ки пойгоҳи додаҳо барои муҳофизати додаҳои мо кор мекунад.

Маҳдудияти NOT NULL

Дар боби 7 бо NULL шинос шудед: нишондиҳандаи махсуси SQL барои додаи мавҷуднабуда ё қимати номаълум.

Медонем, ки қиматҳои калиди асосӣ наметавонанд NULL бошанд, зеро бояд ҳар сатри ҷадвалро ба таври ягона муайян кунанд.

Аммо ҳолатҳои дигар низ ҳастанд, ки намехоҳед сутун бе қимат монад. Масалан, дар ҷадвали хонандагони мактаб талаб кардани ном ва насаб барои ҳар сатр мантиқӣ аст.

Барои ҳатмӣ кардани қимати сутун, SQL маҳдудияти NOT NULL-ро медиҳад. Он қабули NULL-ро манъ мекунад.

CREATE TABLE not_null_example (  
    student_id bigint GENERATED ALWAYS AS IDENTITY,  
    first_name text NOT NULL,  
    last_name text NOT NULL,  
    CONSTRAINT student_id_key PRIMARY KEY (student_id)  
);  

Намунаи 8-10: Мисоли маҳдудияти NOT NULL.

Дар ин ҷо барои сутунҳои first_name ва last_name маҳдудияти NOT NULL муайян кардем, зеро эҳтимол дар ҷадвали маълумоти хонандагон ҳар ду маълумотро талаб мекунем.

Агар кӯшиш кунем сатре ворид карда, барои ин сутунҳо қимат надиҳем, пойгоҳи додаҳо аз вайрон шудани маҳдудият хабар медиҳад.

Диққат: NOT NULL танҳо NULL-ро манъ мекунад. Он сатри матнии холии '' ё матни иборат аз фосилаҳоро худ аз худ манъ намекунад.

Нест кардани маҳдудиятҳо ё илова кардани онҳо баъдтар

Бо ALTER TABLE метавонед маҳдудиятро аз ҷадвали мавҷуда нест кунед ё баъдтар маҳдудияти нав илова намоед.

Ин ҳамон фармонест, ки дар бахши «Сохтани калиди сунъии худкор афзоянда» барои аз нав танзим кардани пайдарпайии IDENTITY истифода кардем.

Барои нест кардани маҳдудияти калиди асосӣ, калиди хориҷӣ ё UNIQUE, чунин менависед:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;  

Барои нест кардани NOT NULL, амал ба сутун дахл дорад. Аз ин рӯ, ALTER COLUMN низ лозим аст:

ALTER TABLE table_name  
ALTER COLUMN column_name DROP NOT NULL;  

Биёед ин фармонҳоро барои тағйир додани ҷадвали навсохтаи not_null_example истифода барем:

ALTER TABLE not_null_example  
DROP CONSTRAINT student_id_key;

ALTER TABLE not_null_example  
ADD CONSTRAINT student_id_key PRIMARY KEY (student_id);

ALTER TABLE not_null_example  
ALTER COLUMN first_name DROP NOT NULL;

ALTER TABLE not_null_example  
ALTER COLUMN first_name SET NOT NULL;  

Намунаи 8-11: Нест кардан ва илова кардани калиди асосӣ ва маҳдудияти NOT NULL.

Фармонҳоро як-як иҷро кунед.

Ҳар дафъа тағйироти таърифи ҷадвалро дидан мумкин аст: дар psql бо фармони \d ва номи ҷадвал, дар pgAdmin бошад, номи ҷадвалро як бор пахш карда, варақаи SQL-ро интихоб намоед.

Аҳаммият диҳед, ки ҳар ду таърифи ҷадвалро нисбат ба коди навиштаи шумо муфассалтар нишон медиҳанд: маҳдудиятҳо ва индексҳо низ дар он ҷо намоёнанд.

Фармони аввал бо DROP CONSTRAINT калиди асосии student_id_key-ро нест мекунад.

Фармони дуюм бо ADD CONSTRAINT онро дубора илова мекунад. Барои илова кардани маҳдудият ба ҳар ҷадвали мавҷуда низ ҳамин сохторро истифода мебарем.

Эзоҳ: Ба ҷадвали мавҷуда танҳо вақте маҳдудият илова карда метавонед, ки додаҳои мавҷуда ба шартҳои он мувофиқ бошанд. Масалан, ба сутуне, ки қиматҳои такрорӣ ё NULL дорад, маҳдудияти калиди асосӣ гузошта наметавонед.

Дар фармони сеюм ALTER COLUMN ва DROP NOT NULL маҳдудияти NOT NULL-ро аз first_name мебардоранд.

Ниҳоят, SET NOT NULL онро дубора илова мекунад.

Тезонидани дархостҳо бо индексҳо

Ҳамон тавре ки нишондиҳандаи охири китоб ёфтани маълумотро осон мекунад, индекс низ метавонад дархостҳоро тезонад.

Индекс сохтори алоҳидаи додаҳост, ки пойгоҳи додаҳо онро идора мекунад. Онро барои як ё чанд сутуни ҷадвал месозем.

Пойгоҳи додаҳо ба ҷойи санҷидани ҳамаи сатрҳо барои ёфтани маълумот, метавонад индексро ҳамчун роҳи кӯтоҳ истифода барад.

Албатта, ин шарҳи содаи мавзуест, ки дар пойгоҳҳои додаҳои SQL мураккаб аст. Мо метавонистем чанд бобро ба тарзи кори индексҳо ва танзими суръати пойгоҳи додаҳо бахшем. Аммо дар ин ҷо дастурҳои умумӣ ва як мисоли хоси PostgreSQL-ро меорем, ки фоидаи индексҳоро нишон медиҳад.

Эзоҳ: Стандарти ANSI SQL синтаксиси сохтани индекс ва тарзи амалисозии онро муайян намекунад. Бо вуҷуди ин, ҳамаи низомҳои асосии пойгоҳи додаҳо, аз ҷумла Microsoft SQL Server, MySQL, Oracle ва SQLite, индексҳоро дастгирӣ мекунанд. Синтаксис ва рафтори онҳо ба он чи дар ин ҷо мебинем, монанд аст.

B-tree: индекси пешфарзи PostgreSQL

Шояд бехабар аз ин, шумо аллакай чанд индекс сохтаед.

Ҳар боре калиди асосӣ ё маҳдудияти UNIQUE илова мекунед, PostgreSQL — мисли аксари низомҳои пойгоҳи додаҳо — барои сутун ё сутунҳои он индекс месозад.

Индексҳо аз додаҳои худи ҷадвал ҷудо нигоҳ дошта мешаванд. Ҳангоми иҷрои дархост, агар лозим бошад, пойгоҳи додаҳо онҳоро худкор истифода мебарад. Ҳангоми илова, нест ё нав шудани сатрҳо индексҳо низ нав мешаванд.

Дар PostgreSQL навъи пешфарзи индекс B-tree аст. Он барои сутунҳои калиди асосӣ ё UNIQUE худкор сохта мешавад. Ҳамчунин ҳангоми истифодаи CREATE INDEX, агар навъи дигарро нишон надиҳед, ҳамин навъ сохта мешавад.

B-tree сохтори дарахтии мувозинатнок дорад. Ҳангоми ҷустуҷӯи қимат, ҷустуҷӯ аз қисми болоии дарахт оғоз шуда, аз шохаҳо мегузарад, то қимат ёфт шавад. Албатта, раванди воқеӣ аз ин шарҳи кӯтоҳ мураккабтар аст.

Индекси B-tree барои додаҳое муфид аст, ки онҳоро метавон тартиб дод ва бо операторҳои баробарӣ ё диапазон ҷустуҷӯ кард:

<  
<=  
=  
>=  
>  
BETWEEN  

Он барои баъзе ҷустуҷӯҳои LIKE низ истифода мешавад, агар намуна аз аломати умумии ҷустуҷӯ оғоз нашавад. Масалан:

WHERE chips LIKE 'Dorito%'  

PostgreSQL навъҳои дигари индексро низ дастгирӣ мекунад, аз ҷумла:

• GIN — Generalized Inverted Index: индекси умумии чаппашуда.
• GiST — Generalized Search Tree: дарахти умумии ҷустуҷӯ.

Ҳар кадом истифодаи хоси худро дорад. Онҳоро дар боби 12 муфассалтар баррасӣ мекунем.

Ҳоло мебинем, ки B-tree як дархости одии ҷустуҷӯиро чӣ гуна тез мекунад.

Барои ин машқ ҷадвали калон лозим аст: дар ҷадвали чандсатра ҳар дархост дар як лаҳза иҷро мешавад ва фоидаи индекс намоён намегардад. Ҷадвали addresses-ро бо як миллион суроғаи сохта пур мекунем. Ин корро бе ягон файл, бо функсияи generate_series()-и PostgreSQL анҷом медиҳем.

Коди намунаи 8-12-ро иҷро кунед:

CREATE TABLE addresses (  
    id integer CONSTRAINT addresses_key PRIMARY KEY,  
    street text,  
    house_number integer,  
    postcode text  
);

INSERT INTO addresses  
SELECT n,  
       'Кӯчаи ' || (n % 5000 + 1),  
       n % 250 + 1,  
       '7340' || lpad((n % 100)::text, 2, '0')  
FROM generate_series(1, 1000000) AS n;  

Намунаи 8-12: Пур кардани ҷадвали addresses бо як миллион сатр.

Функсияи generate_series(1, 1000000) ададҳои 1 то 1 000 000-ро ҳамчун сатрҳо бармегардонад ва ҳар адад ба тағйирёбандаи n дода мешавад. Аз ҳар n як суроға месозем:

• n % 5000 + 1 — бақияи тақсим, ки дар боби 6 омӯхтем — номи кӯчаро аз Кӯчаи 1 то Кӯчаи 5000 медиҳад. Ҳамин тавр дар ҳар кӯча 200 суроға мешавад.
• Оператори || сатрҳоро пайваст мекунад: 'Кӯчаи ' || 42 натиҷаи Кӯчаи 42-ро медиҳад.
• Функсияи lpad() сатрро аз чап то дарозии додашуда бо аломат пур мекунад: lpad('7', 2, '0') натиҷаи 07-ро медиҳад.

Дастури INSERT ... SELECT натиҷаи дархостро ба ҷадвал ворид мекунад. Ин роҳи дигари ворид кардани сатрҳост, ки дар баробари INSERT ... VALUES зиёд истифода мешавад. Дар компютери мо сохтани як миллион сатр камтар аз як сония вақт гирифт.

Бо дархости SELECT count(*) FROM addresses; санҷед, ки ҷадвал 1 000 000 сатр дорад.

Яке аз истифодаҳои маъмули ҷадвали суроғаҳо ҷустуҷӯ аз рӯйи сутуни street аст. Аз ин рӯ, барои омӯзиши самаранокии индексҳо ҳамин мисолро истифода мебарем.

Арзёбии самаранокии дархост бо EXPLAIN

Барои чен кардани самаранокӣ пеш ва пас аз илова кардани индекс, фармони EXPLAIN-и PostgreSQL-ро истифода мебарем.

Он нақшаи иҷрои дархост-ро нишон медиҳад. Нақша метавонад муайян кунад, ки пойгоҳи додаҳо ҷадвалро чӣ гуна аз назар мегузаронад, индексҳоро истифода мебарад ё не ва ғайра.

Агар калимаи ANALYZE-ро илова кунем, EXPLAIN дархостро воқеан иҷро карда, вақти иҷрои онро низ нишон медиҳад.

Сабт кардани вақтҳои ибтидоии иҷро

Се дархости намунаи 8-13-ро барои таҳлили самаранокӣ пеш ва пас аз илова кардани индекс истифода мебарем.

Инҳо дархостҳои одии SELECT бо банди WHERE мебошанд, ки дар аввали онҳо EXPLAIN ANALYZE навишта шудааст.

Ин калимаҳо ба пойгоҳи додаҳо мегӯянд, ки дархостро иҷро кунад ва ба ҷойи натиҷаи додаҳо омори раванди иҷро ва вақти сарфшударо нишон диҳад.

EXPLAIN ANALYZE SELECT * FROM addresses  
WHERE street = 'Кӯчаи 42';

EXPLAIN ANALYZE SELECT * FROM addresses  
WHERE street = 'Кӯчаи 3000';

EXPLAIN ANALYZE SELECT * FROM addresses  
WHERE street = 'Кӯчаи 9999';  

Намунаи 8-13: Дархостҳои санҷишӣ барои арзёбии самаранокии индекс.

Дархости охирин кӯчаеро меҷӯяд, ки дар ҷадвал нест — ин ҳам ҳолати маъмулии ҷустуҷӯ аст.

Дар компютери мо дархости аввал чунин омор медиҳад (PostgreSQL 18):

Gather  (cost=1000.00..13581.23 rows=199 width=30) (actual time=0.094..10.603 rows=200.00 loops=1)  
  Workers Planned: 2  
  Workers Launched: 2  
  Buffers: shared hit=7353  
  ->  Parallel Seq Scan on addresses  (cost=0.00..12561.33 rows=83 width=30) (actual time=0.023..8.436 rows=66.67 loops=3)  
        Filter: (street = 'Кӯчаи 42'::text)  
        Rows Removed by Filter: 333267  
        Buffers: shared hit=7353  
Planning Time: 0.030 ms  
Execution Time: 10.614 ms  

Ҳоло ҳамаи ҷузъиёти ин натиҷа ба мо лозим нест, бинобар ин онҳоро пурра шарҳ намедиҳам. Ду қисми он муҳим аст.

Аввал, барои ёфтани сатрҳое, ки:

street = 'Кӯчаи 42'  

доранд, пойгоҳи додаҳо азназаргузаронии пайдарпай — sequential scan-ро анҷом медиҳад. Калимаи Parallel нишон медиҳад, ки кор байни се раванд тақсим шудааст.

Ин яъне тамоми ҷадвал санҷида мешавад: пойгоҳи додаҳо ҳар сатрро аз назар гузаронда, сатрҳоеро, ки street-и онҳо ба Кӯчаи 42 баробар нест, аз натиҷа хориҷ мекунад. Сатри Rows Removed by Filter нишон медиҳад, ки ҳар раванд беш аз 333 ҳазор сатрро беҳуда аз назар гузарондааст — барои ёфтани ҳамагӣ 200 сатр.

Дуюм, Execution Time вақти иҷрои дархостро нишон медиҳад. Дар компютери мо он тақрибан 11 миллисония буд. Вақти шумо аз омилҳои гуногун, аз ҷумла сахтафзори компютер, вобаста мешавад.

Барои санҷиш ҳар дархости намунаи 8-13-ро чанд бор иҷро карда, кӯтоҳтарин вақти иҷрои ҳар кадомро сабт кунед.

Мебинед, ки вақти як дархост дар иҷроҳои гуногун андаке тағйир меёбад. Ин метавонад аз равандҳои дигари сервер ё аз он вобаста бошад, ки пас аз иҷрои қаблӣ додаҳо дар хотира мондаанд.

Илова кардани индекс

Акнун мебинем, ки илова кардани индекс усули ҷустуҷӯ ва вақти иҷрои дархостро чӣ гуна тағйир медиҳад.

CREATE INDEX street_idx ON addresses (street);  

Намунаи 8-14: Сохтани индекси B-tree дар ҷадвали addresses.

Ин фармон ба фармонҳои сохтани маҳдудиятҳо монанд аст.

Аввал CREATE INDEX, баъд номи интихобкардаи индекс — дар ин ҷо street_idx — меояд.

Сипас ON, номи ҷадвали ҳадаф ва сутунро менависем.

Фармонро иҷро кунед. PostgreSQL қиматҳои сутуни street-ро аз назар гузаронда, индекс месозад.

Индексро танҳо як бор сохтан лозим аст.

Пас аз анҷоми сохтани он, ҳар се дархости намунаи 8-13-ро дубора иҷро карда, вақтҳои EXPLAIN ANALYZE-ро сабт кунед.

Мисоли натиҷа:

Bitmap Heap Scan on addresses  (cost=5.97..699.72 rows=199 width=30) (actual time=0.013..0.052 rows=200.00 loops=1)  
  Recheck Cond: (street = 'Кӯчаи 42'::text)  
  Heap Blocks: exact=200  
  Buffers: shared hit=203  
  ->  Bitmap Index Scan on street_idx  (cost=0.00..5.92 rows=199 width=0) (actual time=0.006..0.006 rows=200.00 loops=1)  
        Index Cond: (street = 'Кӯчаи 42'::text)  
        Index Searches: 1  
        Buffers: shared hit=3  
Planning Time: 0.011 ms  
Execution Time: 0.066 ms  

Тағйиротро мебинед?

Аввал, пойгоҳи додаҳо ҳоло ба ҷойи санҷидани ҳар сатри ҷадвал аз индекси street_idx истифода мебарад.

Дуюм, дархост хеле тезтар шудааст. Ба сатрҳои Buffers низ нигаред: пеш аз индекс 7353 саҳифаи ҷадвал хонда мешуд, акнун ҳамагӣ 203.

Ҷадвали 8-1 вақтҳои иҷрои гирдкардашударо дар компютери мо пеш ва пас аз илова кардани индекс нишон медиҳад.

Ҷадвали 8-1: Чен кардани самаранокии индекс

Вақтҳои иҷро даҳҳо маротиба беҳтар шудаанд.

Оё даҳ миллисония воқеан муҳим аст? Вақте барои ёфтани ҷавоб пайваста дархост мефиристед ё низоми пойгоҳи додаҳоро барои ҳазорон корбар месозед, ин сарфаи вақт ҷамъ шуда, аҳаммияти калон пайдо мекунад.

Агар лозим шавад индексро нест кунед — масалан, ҳангоми муқоисаи самаранокии чанд навъи индекс — аз DROP INDEX ва номи индекс истифода баред.

Нуктаҳои муҳим ҳангоми истифодаи индексҳо

Дидед, ки индексҳо метавонанд суръатро хеле беҳтар кунанд. Пас оё бояд ба ҳар сутуни ҷадвал индекс илова кард?

Шитоб накунед! Индексҳо арзишманданд, вале на ҳамеша заруранд. Илова бар ин, онҳо ҳаҷми пойгоҳи додаҳоро зиёд мекунанд ва ҳангоми навиштани додаҳо хароҷоти нигоҳдорӣ доранд.

Барои муайян кардани вақти муносиб барои истифодаи индексҳо ин тавсияҳоро ба назар гиред:

• Ҳуҷҷатҳои низоми пойгоҳи додаҳои худро хонед. Бифаҳмед кадом навъҳои индекс дастрасанд ва барои ҳар навъи дода кадомаш муносиб аст. Масалан, PostgreSQL ғайр аз B-tree боз панҷ навъи индекс дорад, ки дар боби 12 шарҳ дода мешаванд. Яке аз онҳо — GiST — барои навъҳои геометрӣ ва GIN барои jsonb ва ҷустуҷӯи пурраматнӣ мувофиқ аст.
• Барои сутунҳои пайвасткунии ҷадвалҳо индексро баррасӣ кунед. Дар PostgreSQL калидҳои асосӣ ба таври пешфарз индекс доранд, вале сутунҳои калидҳои хориҷӣ худкор индекс намегиранд. Онҳо метавонанд номзадҳои хуб барои индекс бошанд.
• Индекс дар калиди хориҷӣ метавонад ҳангоми несткунии каскадӣ аз санҷиши гарони тамоми ҷадвал пешгирӣ кунад.
• Ба сутунҳое индекс илова кунед, ки зуд-зуд дар банди WHERE истифода мешаванд. Тавре дидед, индексҳо метавонанд суръати ҷустуҷӯро хеле беҳтар кунанд.
• Бо EXPLAIN ANALYZE танзимоти гуногунро бисанҷед. Беҳсозии самаранокӣ як раванд аст. Агар пойгоҳи додаҳо индексеро истифода набарад ва он барои калиди асосӣ ё маҳдудияти дигар зарур набошад, метавонед онро нест кунед. Ин ҳаҷми пойгоҳро кам карда, воридкунӣ, навсозӣ ва несткуниро тезтар мекунад.

Хулоса

Бо абзорҳое, ки дар ин боб омӯхтед, метавонед пойгоҳҳои додаҳои сохтаи худ ё ба шумо супурдашударо барои ҷамъоварӣ ва таҳқиқи додаҳо мувофиқтар кунед.

Муҳим аст маҳдудиятҳое муайян намоед, ки ҳам ба додаҳо ва ҳам ба интизориҳои корбарон мувофиқ бошанд: қиматҳои бемантиқро қабул накунанд, мавҷуд будани қиматҳои заруриро таъмин созанд ва байни ҷадвалҳо робитаҳои дуруст барқарор кунанд.

Ҳамчунин омӯхтед, ки чӣ гуна дархостҳоро тезтар иҷро кунед ва объектҳои пойгоҳи додаҳоро бо услуби ягона ташкил намоед. Ин ҳам барои шумо ва ҳам барои дигар истифодабарандагони додаҳо фоидаовар аст.

Дар боби оянда бо функсияҳои ҷамъбастии SQL сифати маҷмӯи додаҳоро арзёбӣ карда, аз онҳо маълумоти қобили истифода мегирем.

Худатон бисанҷед

Омодаед дониши худро аз мавзуъҳои ин боб санҷед?

Ду ҷадвали зеринро аз пойгоҳи додаҳое баррасӣ кунед, ки барои сабти маҷмӯаи пластинкаҳои винилии LP-и худ месозед. Аввал фармонҳои CREATE TABLE-ро омӯзед:

CREATE TABLE albums (  
    album_id bigint GENERATED ALWAYS AS IDENTITY,  
    catalog_code text,  
    title text,  
    artist text,  
    release_date date,  
    genre text,  
    description text  
);

CREATE TABLE songs (  
    song_id bigint GENERATED ALWAYS AS IDENTITY,  
    title text,  
    composers text,  
    album_id bigint  
);  

Ҷадвали albums маълумоти умумии маҷмӯи сурудҳои ҳар пластинкаро нигоҳ медорад.

Ҷадвали songs ҳар суруди албомро алоҳида сабт мекунад. Ҳар суруд ном ва сутуне барои оҳангсозон дорад. Оҳангсозон метавонанд аз иҷрокунандаи албом фарқ кунанд.

Ба саволҳои зерин ҷавоб диҳед:

1. Фармонҳои CREATE TABLE-ро тағйир диҳед: ба ҳар ду ҷадвал калидҳои асосӣ ва хориҷӣ, инчунин маҳдудиятҳои иловагӣ — масалан, NOT NULL ё UNIQUE — илова кунед. Интихоби худро шарҳ диҳед.
2. Ба ҷойи калиди сунъии album_id дар ҷадвали albums кадом сутунро ҳамчун калиди табиӣ истифода бурдан мумкин аст? Барои боварӣ ҳосил кардан ба дурустии ин интихоб чӣ маълумот лозим аст?
3. Агар ба ин ҷадвалҳо зуд-зуд дархост фиристед, кадом сутунҳо номзадҳои хуб барои индекс мебошанд? Чаро?

Ҷавобҳо дар замима, бахши «Ҷавобҳо ба машқҳо», оварда шудаанд.