מדריך SQL זה מתמקד ביצירת טבלאות. מדריך זה הינו חלק מרשימת פוסטים הנוגעים לפקודת ה DDL
ב SQL SERVER. פקודות ה Data Definition Language אחראיות על עיצוב מבנה (Structure) הטבלה לרבות יצירתה, מחיקתה ושינוי העמודות בתוכה (כגון מחיקה, הוספה, ושינוי הסוג).
כדי להגיע לפוסטים הנוספים ניתן להעזר בקישורים הבאים :
פקודת CREATE TABLE
פקודה זו מאפשרת לנו ליצור טבלאות חדשות :
|
סוג עמודה |
תיאור |
דוגמא |
|
VARCHAR(size) |
עמודה מחרוזתית הערך בתוך הסוגריים מציין מהו הגודל המקסימלי של כל שדה בעמודה (בתווים) גודל מקסימלי 4000 תווים |
VARCHAR(3) à ‘ABC’ VARCHAR(3) à ‘AB’ |
|
DECIMAL)p,s) |
עמודה מספרית Precision – מספר הספרות Scale – כמה מתוכן אחרי הנקודה |
DECIMAL(5,2) à 476.29 DECIMAL(5,2) à 6.29 |
|
DATE |
עמודה תאריכית |
'YYYY-MM-DD' |
קיימים עוד סוגים רבים של עמודות ב SQL Server, בפוסט זה אנו נתמקד בשלושת אלו.
ערך דיפולט
ניתן להגדיר לעמודות מסויימות ערך דיפולט – משמעות הדיפולט היא שבמידה ולא ציינו אחרת, ערך מסויים שנגדיר מראש יהיה הערך אשר יכנס לתוך שדה בעמודה, לדוגמא :
שכר דיפולטיבי 9800, תאריך העסקה דיפולטיבי SYSDATE.
כדי להגדיר ערך Default יש לכתוב :
DEFAULT ערך_דיפולטיבי
לדוגמא :
salary DECIMAL(8,2) DEFAULT 9500 hire_date DATE DEFAULT ’01-JAN-2011’ hire_date DATE DEFAULT GETDATE()
הגדרת אילוצים לעמודה – רמות שונות
ניתן להגדיר אילוצים בשתי רמות, רמת הגדרת העמודה או רמת הגדרת הטבלה.
הגדרת אילוצים ברמת העמודה
הגדרה ברמת העמודה
v נוצרת כחלק מהגדרת העמודה.
v מתייחסת תמיד לעמודה אחת.
אילוץ ברמת העמודה נראה במבנה הבא :
[CONSTRAINT שם_אילוץ[ סוג_האילוץ
סוג_האילוץ – בסוג האילוץ אנו נכתוב את האילוץ אשר אנו רוצים לכפות על העמודה (לדוגמא Unique או Not Null).
שם_האילוץ – כאשר נגדיר לעמודה אילוץ (לדוגמא Unique) ובעת אחת מפעולותינו על הטבלה נפר את האילוץ (לדוגמא ננסה להכניס ערכים לא ייחודיים) אנו נקבל הודעת שגיאה דיפולטיבית הנראית בפורמט SYS_C, לדוגמא SYS_C004183 (הודעת שגיאה אשר ממנה קשה להבין מה היתה הבעיה אשר הובילה לשגיאה).
לשימושים מאוחרים יותר על מנת שנדע מה היתה השגיאה, על איזו עמודה ובאיזו טבלה, מומלץ תמיד לתת לאילוץ שם כדי שנוכל בקלות לזהות את השגיאה שלנו.
הפורמט המקובל נראה בצורה הבאה :
שם אילוץ בשתי אותיות _ מספר אותיות של שם העמודה _ מספר אותיות של שם הטבלה
PRIMARY KEY (PK) – מפתח ראשי
להלן התחביר בו נשתמש כדי להגדיר מפתח ראשי :
שם_עמודה סוג עמודה [DEFAULT ערך] [CONSTRAINT שם_אילוץ] PRIMARY KEY,
דוגמא :
CREATE TABLE emps (emp_id decimal(3) CONSTRAINT emps_empid_pk PRIMARY KEY, emp_name varchar(25))
NOT NULL(NN) – איסור ערכי NULL בעמודה
להלן התחביר בו נשתמש כדי להגדיר איסור ערכי NULL בעמודה :
שם_עמודה סוג עמודה [DEFAULT ערך] [CONSTRAINT שם_אילוץ] NOT NULL,
דוגמא:
CREATE TABLE emps (emp_id decimal(3) CONSTRAINT emps_empid_pk PRIMARY KEY, emp_name varchar(25) CONSTRAINT emps_emnm_nn NOT NULL)
! אילוץ זה ניתן להגדרה ברמת העמודה בלבד.
UNIQUE (UQ) – ערכים ייחודיים בעמודה
להלן התחביר בו נשתמש כדי להגדיר אילוץ ערכים יחודיים בעמודה :
שם_עמודה סוג עמודה [DEFAULT ערך] [CONSTRAINT שם_אילוץ] UNIQUE
דוגמא :
CREATE TABLE emps (emp_id decimal(3) CONSTRAINT emps_empid_pk PRIMARY KEY, emp_name varchar(25) CONSTRAINT emps_emnm_nn NOT NULL, emp_phone varchar(25)CONSTRAINT emps_empn_uq UNIQUE)
CHECK (CK) – הגבלת הערכים ע"פ תנאי המותאם אישית
להלן התחביר בו נשתמש כדי להגדיר אילוץ בדיקה על עמודה :
שם_עמודה סוג עמודה [DEFAULT ערך] [CONSTRAINT שם_אילוץ] CHECK (תנאי)
התנאי הנכתב ב- CHECK דומה במבנהו לכל תנאי אשר נכתב במשפט WHERE.
חל איסור לכתוב בתנאי של חלק ה- CHECK :
v ערכים החוזרים כתוצאה משימוש ב- SEQUENCES.
v פונקציות כגון ROWNUM, SYSDATE.
v תתי שאילתות.
דוגמא א :
CREATE TABLE emps (emp_id decimal(3) CONSTRAINT emps_empid_pk PRIMARY KEY, emp_name varchar(25) CONSTRAINT emps_emnm_nn NOT NULL, emp_phone varchar(25) CONSTRAINT emps_empn_uq UNIQUE, emp_mail varchar(25) CONSTRAINT emps_emml_ck CHECK (emp_mail LIKE ‘_%@%.%’))
דוגמא ב :
CREATE TABLE emps (emp_id decimal(3) CONSTRAINT emps_empid_pk PRIMARY KEY, emp_name varchar(25) CONSTRAINT emps_emnm_nn NOT NULL, emp_phone varchar(25) CONSTRAINT emps_empn_uq UNIQUE, emp_mail varchar(25) CONSTRAINT emps_emml_ck CHECK (emp_mail LIKE ‘_%@%.%’), emp_sal decimal(8,2) CONSTRAINT emp_sal_ck CHECK (emp_sal > 5000))
FOREIGN KEY (FK) – מפתח זר
להלן התחביר בו נשתמש כדי להגדיר מפתח זר :
שם_עמודה … [CONSTRAINT שם_אילוץ] REFERENCES שם_טבלה (שם_עמודה) [ON DELETE CASCADE] [ON DELETE SET NULL]
דוגמא :
טבלת האב
CREATE TABLE deps (dep_id decimal(3) CONSTRAINT deps_id_pk PRIMARY KEY , dep_name varchar(25))
טבלת הבן
CREATE TABLE emps (emp_id decimal(3) CONSTRAINT emps_empid_pk PRIMARY KEY, emp_name varchar2(25) CONSTRAINT emps_emnm_nn NOT NULL, emp_phone varchar2(25) CONSTRAINT emps_empn_uq UNIQUE, emp_mail varchar2(25) CONSTRAINT emps_emml_ck CHECK (emp_mail LIKE ‘_%@%.%’), emp_sal decimal(8,2) CONSTRAINT emp_sal_ck CHECK (emp_sal > 5000), dep_id decimal(3) CONSTRAINT emp_depid_fk REFERENCES deps(dep_id))
הגדרת אילוצים ברמת הטבלה
v נוצרת לאחר הגדרת העמודות השונות.
v יכולה להתייחס ליותר מעמודה אחת (אילוץ המורכב משתי עמודות יחדיו).
v מאפשרת יצירת מספר אילוצים על אותה עמודה.
v לא ניתן ליצור אילוץ NOT NULL בצורה זו.
אילוץ ברמת הטבלה נראה במבנה הבא :
[CONSTRAINT שם_אילוץ] סוג_האילוץ(שם_עמודה)
כדי לכפות את האילוץ על יותר מעמודה אחת נכתוב בתוך הסוגריים את שמות כל העמודות עליהן אנו רוצים שהאילוץ יעבוד כאשר פסיק (,) מפריד בינהן.
[CONSTRAINT שם_אילוץ] סוג_האילוץ(... שם_עמודה ,שם_עמודה)
לדוגמא
CREATE TABLE emps (emp_id DECIMAL(3), emp_f_name VARCHAR(25) , emp_l_name VARCHAR(25) , emp_phone VARCHAR(25) CONSTRAINT emps_empn_nn NOT NULL, emp_mail VARCHAR(25) , emp_sal DECIMAL(8,2) , dep_id DECIMAL(3), CONSTRAINT emps_empid_pk PRIMARY KEY (emp_id), CONSTRAINT emps_empn_uq UNIQUE(emp_f_name, emp_l_name), CONSTRAINT emps_emml_ck1 CHECK (emp_mail LIKE '_%@%.%'), CONSTRAINT emps_emml_ck2 CHECK (LENGTH(emp_mail) < 15), CONSTRAINT emps_emml_uq UNIQUE (emp_mail) , CONSTRAINT emp_sal_ck CHECK (emp_sal > 5000), CONSTRAINT emp_depid_fk FOREIGN KEY (dep_id) REFERENCES deps(dep_id) )
