none
חסימת ערך בעמודה

    שאלה

  • שלום

    לכל שאלה יש כמה תשובות

    לכל תשובה יש שדה האם היא התשובה ה"דיפולטית" לשאלה

    אני מעוניינת לעשות, שלא יהיה יותר מתשובה אחת דיפולטית לשאלה.

    השאלה אם אפשר לחסום את זה רק בזמן שמכניסים ערך  (שזה פשוט), או ברמת הגדרת הטבלה

    תודה


    • נערך על-ידי 3Maya יום שני 28 מאי 2012 15:19
    יום שני 28 מאי 2012 15:18

תשובות

  • לא בטוח שהבנתי את השאלה..
    הבנתי כך- טבלת השאלות היא בצד ה-1 ואילו טבלת התשובות בצד ה-N (רבים), כלומר- לכל שאלה מספר תשובות.
    לכל שאלה מותר שתהיה תשובה אחת שהיא ברירת מחדל (בטבלת התשובות), כלומר- IsDefault=1.
    פתרון- אינדקס מפולטר, בערך כך (מקווה שאין שגיאות סינטקס):

    Create Unique Index Idx_QuestionID On Answer(QuestionID) Where IsDefault=1;

     

    Blog: http://about.me/GeriReshef

    • הוצע כתשובה על-ידי Guy GlantserMVP, Moderator יום שלישי 29 מאי 2012 04:51
    • סומן כתשובה על-ידי 3Maya יום שלישי 29 מאי 2012 06:31
    יום שלישי 29 מאי 2012 02:27
  • הי,

    אופציה אחרת היא שינוי ה-Design של בסיס הנתונים. במקום להחזיק את העמודה IsDefault בטבלה Answer ולוודא שהיא שווה ל-1 רק ברשומה אחת לכל QuestionId, אפשר להחליף אותה בעמודה - DefaultAnswerId - בטבלה Question, כך שכל שאלה תצביע על התשובה הדיפולטית. אפשר ורצוי גם ליצור Foreign Key על העמודה הזאת. בצורה כזאת לא צריך לדאוג לחוקיות של הנתונים (לפחות לא באופן שתואר בשאלה).

    בהצלחה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי 3Maya יום שלישי 29 מאי 2012 06:44
    יום שלישי 29 מאי 2012 04:55
    מנחה דיון

כל התגובות

  • לא בטוח שהבנתי את השאלה..
    הבנתי כך- טבלת השאלות היא בצד ה-1 ואילו טבלת התשובות בצד ה-N (רבים), כלומר- לכל שאלה מספר תשובות.
    לכל שאלה מותר שתהיה תשובה אחת שהיא ברירת מחדל (בטבלת התשובות), כלומר- IsDefault=1.
    פתרון- אינדקס מפולטר, בערך כך (מקווה שאין שגיאות סינטקס):

    Create Unique Index Idx_QuestionID On Answer(QuestionID) Where IsDefault=1;

     

    Blog: http://about.me/GeriReshef

    • הוצע כתשובה על-ידי Guy GlantserMVP, Moderator יום שלישי 29 מאי 2012 04:51
    • סומן כתשובה על-ידי 3Maya יום שלישי 29 מאי 2012 06:31
    יום שלישי 29 מאי 2012 02:27
  • הי,

    אופציה אחרת היא שינוי ה-Design של בסיס הנתונים. במקום להחזיק את העמודה IsDefault בטבלה Answer ולוודא שהיא שווה ל-1 רק ברשומה אחת לכל QuestionId, אפשר להחליף אותה בעמודה - DefaultAnswerId - בטבלה Question, כך שכל שאלה תצביע על התשובה הדיפולטית. אפשר ורצוי גם ליצור Foreign Key על העמודה הזאת. בצורה כזאת לא צריך לדאוג לחוקיות של הנתונים (לפחות לא באופן שתואר בשאלה).

    בהצלחה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי 3Maya יום שלישי 29 מאי 2012 06:44
    יום שלישי 29 מאי 2012 04:55
    מנחה דיון
  • זו שאלה מעניינת עם השלכות עוד יותר מעניינות אז לשם ההתפלפלות:

    אם הגברת עובדת עם SQL 2005 למשל או מסד נתונים אחר שאין לו אינדקסים מסוננים, או סתם רוצה לכתוב קוד ANSI בלבד אז פתרון 1 לא ישים.

    פתרון 2 מציב בעיה חדשה - יש לך cyclic reference כאשר טבלה 1 מצביעה ל 2 ו 2 ל 1.

    זה מגביל אותך למשל ב cascading referential constraints ויכול לסבך פעולות טריוויאליות כמו סדר פעולות ב INSERT...

    למישהו יש עוד רעיונות ברמת ה design?


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

    יום שלישי 29 מאי 2012 10:29
  • אם מדובר ב-2005 ניתן להחליף את ה-Filtered Index שהצעתי קודם
    ב-Indexed View באופן די דומה (פילטר על IsDefault ואינדקס Unique על QuestionID).

    פתרון נוסף שלא בדקתי (במסגרת ההתפלפלות..)- להגדיר עמודה מחושבת:

    (1-IsDefault)*AnswerId

    וליצור Unique Index על עמודת QuestionID ועל העמודה המחושבת (הערכים שיופיעו בה יהיו 0 לתשובת ברירת המחדל והערכים המקוריים לשאר).

    הערה- כל הפתרונות הנ"ל מאפשרים פתרון ברירת מחדל אחד לכל שאלה,
    אך אינם מחייבים שיהיה פתרון ברירת מחדל.


    Blog: http://about.me/GeriReshef

    יום שלישי 29 מאי 2012 14:23
  • היי גרי,

    נמשיך עם ההתפלפלות התיאורטית...

    ה Indexed View וה Filtered Index פתרונות דומים מאוד אבל שניהם פתרונות "טכניים עקיפים" לפתור בעיה של מודל המידע. באותה מידה אפשר לייצר גם Trigger לאכוף את הלוגיקה. לא לשם אני מכוון.

    יש פתרון אלגנטי שאוכף את החוקיות הנ"ל כחלק מהסכמה ומודל המידע מבלי ליצור סיבוכים מיותרים.

    *רמז - הפתרון שאני מכוון אליו נתמך בתקן ANSI אך לא בהכרח עובד בכל סוגי מסדי הנתונים מכיוון שלא כולם מתנהגים על פי התקן, אפילו לא SQL SERVER בכבודו ובעצמו בדברים מסויימים...

    :-)


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

    יום שלישי 29 מאי 2012 18:51
  • במסגרת ההתפלפלות התיאורתית (שזה אומר בעיקר שאני לא מצי את הדרך שהייתי בוחר בה בהכרח אלא סתם רעיון וכיוון נוסף), הרי שיש פתרון מאוד מאוד פשוט של שימוש בטור "דירוג".

    הרעיון: פשוט מחזיקים טור INT של הדירוג של התשובות לכל שאלה במקום טור bit של האם זו התשובה היא של ברירת המחדל.

    הייתרונות:

    1. יעבוד בכל מסד נתונים מכל סוג צורה וצבע (מכאן שזה לא מה שעמי התכוון כמובן)

    2. מונע את הבעיה שהציג גרי של "אינם מחייבים שיהיה פתרון ברירת מחדל" מכיוון שתמיד יש דירוג.

    3. ניתן בקלות להוסיף תנאי ייחודיות על [טור הדירוג] +[טור מספר השאלה] ובכך לא לאפשר דירוגים זהים

    4. אין צורך בשינוי כל הרשומות של של התשובות של שאלה מסויימת כמו בשיטה הראשונה של שימוש בשדה כן/לא ברירת מחדל. פשוט מעדכנים את העמודה שרוצים שתדורג למספר יותר גדול

    5. אין את בעיית שמירת הנתון בטבלת האב של השאלות

    6. בדרך כלל בכל מקרה יהיה לנו צורך בדירוג אם רוצים להציג את כל התשובות ביחד כדי לקבוע את סדר התשובות בהצגה

    7. יתרונות של פשטות הן בפיתוח והן בעבודה עם אינדקס פשוט על שני טורים (שאם ממש רוצים אפשר להפוך לטור אחד בפורמט מתאים)

    * חיסרון בסוג הטור ושמירת מידע גדול יותר (INT לעומת ביט).
    ** עדכון מחייב שאילתה נוספת של בדיקת המקסימום הקיים כרגע, אם לא רוצים לשנות את הנתונים האחרים אלא רק לבחור כל פעם מספר גדול יותר. חסרון זה נעלם בגרסה 2012 או באורקל או MYSQL כשעושים שימוש ב Sequence

    בטח יש חסרונות נוספים...
    אבל ב 02:30 זה יספיק כרגע :-)


    signature

    יום שלישי 29 מאי 2012 23:35
    מנחה דיון
  • היי,

    אפשר לשים FUNCTION CHECK CONSTRAINT שתקבל את העמודה של השאלה ותבדוק האם כבר קיימת עבורה תשובה דיפולטית.

    הינה דוגמא:

    USE tempdb
    GO
    IF OBJECT_ID('answers') IS NOT NULL DROP TABLE answers
    IF OBJECT_ID('t_uq') IS NOT NULL DROP FUNCTION t_uq
    GO
    
    CREATE TABLE answers(answer_id		INT, 
    					 question_id	INT, 
    					 is_default		BIT)
    GO
    
    CREATE FUNCTION t_uq(@question_id INT)
    RETURNS bit
    AS
    BEGIN
    	 DECLARE @ret bit
    	 IF (SELECT count(*) FROM answers WHERE question_id = @question_id AND is_default = 1) > 1
    	   SET @ret = 0
    	 ELSE 
    	   SET @ret = 1
    	 RETURN @ret
    END
    GO
    
    ALTER TABLE answers ADD CONSTRAINT t_c CHECK(dbo.t_uq(question_id) = 1)
    
    INSERT INTO answers(answer_id, question_id, is_default) VALUES(1, 1, 0) --OK
    INSERT INTO answers(answer_id, question_id, is_default) VALUES(2, 2, 0) --OK
    INSERT INTO answers(answer_id, question_id, is_default) VALUES(3, 1, 1) --OK
    INSERT INTO answers(answer_id, question_id, is_default) VALUES(4, 1, 1) --Fails
    
    SELECT 1 FROM answers WHERE question_id = 1 AND is_default = 1
    
    --So far so good, but watch now:
    
    UPDATE answers SET is_default = 1 WHERE answer_id = 1
    --No error, the constraint doesn't do its job!
    
    --We have invalid data:
    SELECT * FROM answers 

    הדוגמא גם מראה בעיתיות עם המקרה כאשר עושים UPDATE על העמודה

    is_default

    ככה שאם מתוכננים להתבצע UPDATES על העמודה אז הCONSTRAINT יכול להיות יופר..

    לקחתי את הדוגמא הנחמדה מהבלוג של Tibor Karaszi בלינק -

    http://sqlblog.com/blogs/tibor_karaszi/archive/2009/12/17/be-careful-with-constraints-calling-udfs.aspx



    חיים פישנר.

    יום רביעי 30 מאי 2012 05:41
  • האמת שמבחינת ANSI אז לא קיים השימוש בUDF בעבור CONSTRAINTS

    אבל התקן ANSI SQL כן תומך בsubqueries in CHECK constraints

    אבל  SQL SERVER  אינו תומך בכך, לכן בSQL אפשר להשתמש בUDF :)


    חיים פישנר.

    יום רביעי 30 מאי 2012 05:45
  •          Full SQL increases orthogonality and includes deferred constraint
             checking and named constraints. Other technical enhancements in-
             clude additional user options to define datetime data types,
             self-referencing updates and deletes, cascade update on referen-
             tial actions, subqueries in check constraints, scrolled cursors,
             character translations, a bit string data type, temporary tables,
             additional referential constraint options, and simple assertions.

    http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

    הסתמכתי על זה בעקרון...


    חיים פישנר.

    יום רביעי 30 מאי 2012 05:52
  • בוקר טוב חברים,

    1. ל Pituach - הפתרון שלך ישים אך מסבך את הלוגיקה ומוסיף מידע למערכת שלא נדרש במקור.

    2. לחיים - הפתרון שלך בדומה לטריגר נשען על אכיפה דרך קוד.

    תחשבו הרבה יותר פשוט וללא קוד DML...

    רק דרך הסכמה ואובייקטים סטנדרטיים כחלק מהסכמה כמו מפתחות ו Constraints הכי פשוטים.

    דרך אגב - יש 2 פתרונות אלגנטיים שונים לעניין שעונים על התנאים הללו :-)


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

    יום רביעי 30 מאי 2012 06:08
  • אפשרות נוספת

    שימוש בטבלה נוספת מקשרת ביחס 1-1 עם טבלת השאלות. למעשה יישום של מה שכתב גיא רק שאת הנתון שומרים בטבלה שלישית עם טור מפתח חיצוני של מספר שאלה וטור מפתח חיצוני של מספר תשובה של ברירת מחדל. ככה נמנע המצב שהציג עמי של טבלה אחת תלויה בטבלה 2 וטבלה 2 תלויה בטבלה 1.

    * גם מקרה זה מוסיף לנו תוכן במסד הנתונים (אין הוספה של מידע חדש אבל כן הוספה של תוכן בשל הפיצול לטבלה נפרדת). מאבדים את היכולת לדרג את התשובות שברוב האפליקציות יהיה לנו צורך בכל מקרה מפני שנרצה לשלוט על הסדר של הצגת התשובות (לכן זה היה מידע נוסף כמו שגם כתבתי בחסרונות למעלה, אבל מידע מעשי שבדרך כלל יהיה חשוב לממשק ה GUI של האפליקציה שאין לנו אפיון מלא שלו כרגע)


    signature

    • נערך על-ידי pituachMVP, Editor יום רביעי 30 מאי 2012 08:01
    • הוצע כתשובה על-ידי EitanBlumin יום רביעי 30 מאי 2012 08:22
    יום רביעי 30 מאי 2012 08:01
    מנחה דיון
  • הממ, אפשר להגביל את זה בצורה הזאת:

    אבל זה דיי עקום והאמינות יכולה להיפגע בגלל הטבלה הנוספת וחוסר בקישור אל
    השאלות שנכנסות...

    לא עולים לי עוד פתרונות עדיין, אבל אני נהנה מהמשחק :)


    חיים פישנר.

    יום רביעי 30 מאי 2012 08:16
  • LIKE!


    חיים פישנר.

    יום רביעי 30 מאי 2012 08:31
  • אבל חיים, במה שאני כתבתי יש קשר ישיר לשאלות ולכן לא קיימת בעיה זו :-)

    כתבתי "מפתח חיצוני לשאלה" + "מפתח חיצוני לתשובה"

    מה שאתה יישמת בתמונה הוא מעט שונה ולכן נוצרת לך הבעיה

    למעשה מה שהצעתי הוא פעולה דומה למה שעושים בקשר רבים לרבים עם טבלה מקשרת. פשוט במקרה שלנו מגבילים בקשר יחיד ליחיד (יחודייות על טור השאלה מונע קשר רבים למשל).


    signature

    יום רביעי 30 מאי 2012 08:36
    מנחה דיון
  • מתכוון לכך ?


    חיים פישנר.

    יום רביעי 30 מאי 2012 09:03
  • היי חיים ו Pituach,

    אתם מתקרבים לכיוון אחד הפתרונות שחשבתי עליהם, אבל... מה מונע ממני בפתרון שהצגת כאן לשייך לשאלה מסויימת תשובת ברירת מחדל שבכלל שייכת לשאלה אחרת?

    יש פתרון עוד הרבה יותר פשוט.

    ולחידוד הרמז הקודם - לצערי הפתרון הפשוט והנכון לא יעבוד ב SQL Server בגלל אי תאימות לתקן ANSI וזו בדיוק הנקודה שרציתי להדגיש בבעייתיות של האי תאימות הזו...

    :-)


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

    יום רביעי 30 מאי 2012 12:36
  • קודם כל תודה חיים על ה "LIKE" :-)

    וכן לזה היתה הכוונה בערך, בלי החלק כמובן של הרבים (השרטוט לא סוגר את תיאור כל המצב), אבל לזה הכוונה היתה מההתחלה :-)


    signature

    יום רביעי 30 מאי 2012 13:18
    מנחה דיון
  • A. יש פתרון שבטוח יותר טוב מהכל:
    [מי שממהר יכול לדלג ל B]
    - בונים 2 טבלאות פשוטות בלי שום אינדקים ובלי כלום
    - אחר כך מייצרים "אינדקס אבטיח" וזה הכל

    * הבעיה היחידה שאינדקס אבטיח לא מוכר על ידי שרת SQL:-(
    תיאורטית זה פתרון מעולה כי אינדקס אבטיח מגדיר קשר לנתון ברירת מחדל מטבלה אחרת וגם מגדיר לבד אינדקסים ותנאים ומגבלות בטבלאות המקושרות. רק שכאמור הוא מוכר על ידי אבל לא על ידי שום מסד נתונים :-)

    * מה שלא עובד לא מהווה פתרון אלא רק רעיון/גישה תיאורטיים :-)
    בתואר השני היה עלי לבחור אם להתמקד בפיזיקה תיאורטית או פיזיקה יישומית, תמיד סלדתי מעט מפיזיקה תיאורטית כאשר היא חסרת יישום :-)

    ואם נעבור לתשובה יותר רצינית...

    B. לגבי הבעיה של  "בחירת תשובה משאלה לא נכונה" אז ניתן לפתור את זה בקלות על ידי שימוש ב 2 מפתחות חיצוניים מטבלת התשובות.
    1. זה של מספר השאלה המופיע בתשובה
    2. זה של מספר התשובה.

    ניסיון לקחת תשובה לא נכונה ייצור סתירה בין הנתון של מספר השאלה מטבלת התשובות ובין מספר השאלה של המפתח החיצוני מטבלת השאלות (התנאי הפשוט מחייב שהן יהיה שווים)

    ז"א בטבלת הקישור יש 3 טורים של מפתחות חיצוניים ויש בסך הכל תנאי פשוט של שוויון בין הערך של טור A וטור B כאשר A זה מספר השאלה מטבלת השאלה ו B זה מספר השאלה מטבלת התשובה

    הערה חשובה: כמובן שלא ניתן לייצר על פי התקן מפתח חיצוני לשדה שאינו ייחודי ולכן בפתרון ראשון את השדה הנוסף שהוספתי כדי לתקן את המצב שהציג עמי (השדה של מספר השאלה הלקוח מתוך טבלת התשובה) אנחנו לא מייצרים כמפתח ייחודי אלא INT פשוט והנתון בו יוכנס בשאילתה ביחד עם הכנסת שאר הערכים. בפתרון נוסף הצגתי כיצד כן לייצר מפתח חיצוני מלא תוך שימוש בשדה מחושב.

    * כמובן שלא חייבים פיזית להוסיף טור נוסף וניתן לבצע את הבדיקה בזמן הכנסת נתון. פשוט לוודא שמספר השאלה של התשובה זהה למספר השאלה של השאלה. הדרך של שימוש בטור נוסף פשוט יותר קלה להבנה ומאוד פשוטה למימוש.

    ** כמו כן ניתן לוותר על טור נוסף ולעשות שימוש בכל מפתח משולב של 2 הטורים במקום מפתח בודד רק של מספר התשובה.

    *** פתרונות לא חסר וזה לא משנה את הרעיון הבסיסי. מה גם שזה לא אומר שמעשית הייתי הולך בכיוון כזה אם היה לי אפיון מלא של המערכת.

    דרך אגב עמי למה אתה מתכוון ב "אתם מתקרבים לכיוון"? זה לא היה פתרון משולב. Copyright2012@ArielyRonen
    LOL

    בערך משהו כזה:

    CREATE table Q (
    	Q_ID int PRIMARY KEY not null identity,
    	Q_Str nvarchar(10) 
    );
    CREATE TABLE A
    (
        A_ID int PRIMARY KEY NOT NULL identity,
        A_Q_ID int REFERENCES Q(Q_ID) NOT NULL,
        A_Str nvarchar(10) NULL
    );
    CREATE TABLE D_Q_A
    (
        D_Q_ID int REFERENCES Q(Q_ID) NOT NULL,
        D_A_ID int REFERENCES A(A_ID) NOT NULL,
        D_A_Q_ID int NOT NULL -- כאן נכניס את הערך של מספר השאלה המתאים לתשובה שבחרנו
    );
    GO

    וכמובן נשאר לבנות את התנאי שהטור D_A_Q_ID שווה לטור D_Q_ID וככה חסמנו את החור שהיה של תשובה משאלה אחרת.

    או אם רוצים אפשר גם את השימוש בדרך הבאה שמתבססת על שימוש במפתח חיצוני לשדה מחושב (פחות יעיל כי נאלץ לבצע SPLIT בשביל התנאי). בדרך זו אנו נעזרים רק בשדה בודד של מפתח ייחודי ולכן מבטיחים שהנתון שמגיע מהטבלאות המקוריות לא תלוי באפליקציה שמכניסה נתונים (כאמור לכל ייתרון יש חיסרון והיעילות של זה נמוכה יותר)

    CREATE table Q (
    	Q_ID int PRIMARY KEY not null identity,
    	Q_Str nvarchar(10) 
    );
    CREATE TABLE A
    (
        A_ID int PRIMARY KEY NOT NULL identity,
        A_Q_ID int REFERENCES Q(Q_ID) NOT NULL,
    	A_ID2A_Q_ID as convert(nvarchar(100),(convert(nvarchar(3), A_ID) + '@' + convert(nvarchar(3), A_ID))) PERSISTED,
        A_Str nvarchar(10) NULL
    );
    CREATE UNIQUE NONCLUSTERED INDEX A_ID2A_Q_ID_UIndex ON A(A_ID2A_Q_ID)
    CREATE TABLE D_Q_A
    (
        D_Q_ID int REFERENCES Q(Q_ID) NOT NULL,
        D_A_ID2A_Q_ID nvarchar(100) REFERENCES A(A_ID2A_Q_ID) NOT NULL 
    );
    GO
    


    יום רביעי 30 מאי 2012 13:43
    מנחה דיון
  • לא הבנתי את התשובה שלך ואיך זה מונע את המצב הבא:

    INSERT INTO Q VALUES (N'Who is the US President'), (N'What color is the sky')
    INSERT INTO A VALUES (1, N'Clinton'), (1, N'Obama'), (2, N'Blue'), (2, N'red')

    SELECT * FROM Q
    SELECT * FROM A

    INSERT INTO D_Q_A VALUES (1, 3, 3), (2, 1, 1)

    SELECT * FROM D_Q_A INNER JOIN A ON D_Q_A.D_A_ID = A.A_ID INNER JOIN Q ON D_Q_A.D_Q_ID = Q.Q_ID

     


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

    יום חמישי 31 מאי 2012 05:20
  • רשמתי ככה (ציטוט):

    "וכמובן נשאר לבנות את התנאי שהטור D_A_Q_ID שווה לטור D_Q_ID וככה חסמנו את החור שהיה של תשובה משאלה אחרת."

    הכנסת הנתון הזה נוגד את התנאי ולכן לא יכנס:

    INSERT INTO D_Q_A VALUES (1, 3, 3), (2, 1, 1)

    מספר השאלה לפי טבלת השאלות בנתון 1,3,3 הוא 1 אבל לפי טבלת התשובות זה מגיע מתשובה של שאלה מספר 3 ולכן נוצרה שגיאה והתנאי CHECK לא יאפשר הכנסת הנתון.

    זו בדיוק הסיבה שהוספתי את הטור של D_A_Q_ID אחרי שהצגת את החור הקיים באפיון. הטור הזה מוודא וסוגר את החור. זהו הנתון של מספר השאלה הלקוח מטבלת התשובות. אם התשובה לא שייכת לאותה שאלה אז הנתון הזה לא יהיה זהה לנתון של הטור הראשון ולכן תתקבל שגיאה.

    CREATE table Q (
    	Q_ID int PRIMARY KEY not null identity,
    	Q_Str nvarchar(10) 
    );
    CREATE TABLE A
    (
        A_ID int PRIMARY KEY NOT NULL identity,
        A_Q_ID int REFERENCES Q(Q_ID) NOT NULL,
        A_Str nvarchar(10) NULL
    );
    CREATE TABLE D_Q_A
    (
        D_Q_ID int REFERENCES Q(Q_ID) NOT NULL,
        D_A_ID int REFERENCES A(A_ID) NOT NULL,
        D_A_Q_ID int NOT NULL -- כאן נכניס את הערך של מספר השאלה המתאים לתשובה שבחרנו
    );
    ALTER TABLE D_Q_A ADD CONSTRAINT chk_Q CHECK (D_Q_ID = D_A_Q_ID)
    GO
    
    INSERT INTO Q VALUES (N'Who'), (N'What')
    INSERT INTO A VALUES (1, N'Clinton'), (1, N'Obama'), (2, N'Blue'), (2, N'red')
    
    SELECT * FROM Q
    SELECT * FROM A
    
    INSERT INTO D_Q_A VALUES (1, 3, 3), (2, 1, 1)
    
    /*
    Msg 547, Level 16, State 0, Line 2
    The INSERT statement conflicted with the CHECK constraint "chk_Q". The conflict occurred in database "QQ", table "dbo.D_Q_A".
    */
    
    --CLEAN
    drop table D_Q_A
    drop table A
    drop table Q


    signature

    יום חמישי 31 מאי 2012 05:32
    מנחה דיון
  • בשביל מה להחזיק בטבלה 2 טורים שהערך שלהם אותו הדבר?

    תשתמש ב D_Q_ID אם ממילא הוא שווה.

    אני לא יורד לסוף דעתך.

    בהמשך היום אפתח שרשור חדש בנושא עם שמות נורמליים שאפשר לעקוב.


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

    יום חמישי 31 מאי 2012 06:00
  • הסברתי שאפשר גם בלי טור נוסף אבל זו הדרך הכי קלה להראות את זה. כמו שאתה רואה זה עובד. נכון?

    * כבר כתבתי קוד מסודר יותר עם הערות כאן:
    http://ariely.info/Blog/tabid/83/EntryId/86/Questions-Answers-relations.aspx

    רציתי להמתין לאישור שאין יותר חורים כדי להעלות לאוויר בבלוג מסודר גם את ההסברים על הבעיות שיש בדרכים אחרות כפי שעלו בפורום (על ידך), אבל הנה בעקרון הקוד המלא קצת יותר מסודר עם שימוש בשלושת הטורים. ובבלוג יש גם קוד נקי יותר ללא שימוש בטור השלישי והערות והסברים נוספים יגיעו בפירסום הסופי אבל אני חושב שמי שלקח חלק בשירשור כבר יכול להבין מזה

    CREATE table Questions (
    	Q_ID int PRIMARY KEY not null identity,
    	Q_Str nvarchar(100) 
    );
    CREATE TABLE Answers
    (
        A_ID int PRIMARY KEY NOT NULL identity,
        A_Q_ID int REFERENCES Questions(Q_ID) NOT NULL,
        A_Str nvarchar(100) NULL
    );
    CREATE TABLE Default_Q_A
    (
        D_Q_ID int REFERENCES Questions(Q_ID) NOT NULL,
        D_A_ID int REFERENCES Answers(A_ID) NOT NULL,
        D_A_Q_ID int NOT NULL -- כאן נכניס את הערך של מספר השאלה המתאים לתשובה שבחרנו
    );
    ALTER TABLE Default_Q_A ADD CONSTRAINT chk_Default CHECK (D_Q_ID = D_A_Q_ID)
    ALTER TABLE Default_Q_A ADD UNIQUE (D_Q_ID)
    ALTER TABLE Default_Q_A ADD UNIQUE (D_A_ID)
    GO
    
    INSERT INTO Questions VALUES (N'Who'), (N'What')
    INSERT INTO Answers VALUES (1, N'Clinton'), (1, N'Obama'), (2, N'Blue'), (2, N'red')
    GO
    
    SELECT * FROM Questions
    SELECT * FROM Answers
    GO
    
    -- נבחר תשובת ברירת מחדל לשאלה ראשונה על ידי הוספה של הרשומה לטבלת ברירת המחדל
    INSERT INTO Default_Q_A VALUES (1, 2, 1)
    SELECT * FROM Default_Q_A
    GO
    
    -- עתה ננסה להכניס נתון לא חוקי
    -- ננסה לבחור תשובת ברירת מחדל נוספת לאותה שאלה ולכן נקבל שגיאה. יכול להיות רקר ברירת מחדל אחת לכל שאלה
    INSERT INTO Default_Q_A VALUES (1, 1, 1)
    SELECT * FROM Default_Q_A
    GO
    /*
    Msg 2627, Level 14, State 1, Line 1
    Violation of UNIQUE KEY constraint 'UQ__Default___053C2203377B55FE'. Cannot insert duplicate key in object 'dbo.Default_Q_A'. The duplicate key value is (1).
    The statement has been terminated.
    */
    
    -- עתה ננסה להכניס נתון לא חוקי
    -- נבחר את תשובה מספר 3 כברירת מחדל של שאלה ראשונה
    -- אבל תשובה 3 שייכת לשאלה 2 ולכן נקבל שגיאה
    INSERT INTO Default_Q_A VALUES (1, 3, 2)
    /*
    Msg 2627, Level 14, State 1, Line 2
    Violation of UNIQUE KEY constraint 'UQ__Default___053C2203377B55FE'. Cannot insert duplicate key in object 'dbo.Default_Q_A'. The duplicate key value is (1).
    The statement has been terminated.
    */
    
    --CLEAN
    /*
    drop table Default_Q_A
    drop table Answers
    drop table Questions
    */
    

    * כך שהנושא התיאורטי הפך למעשי כרגע :-)
    כמובן שיש עוד פתרונות וזה רק רעיון אחד (כתבתי עוד רעיון בשירשורים מעל ובטח יש עוד)


    signature

    יום חמישי 31 מאי 2012 06:11
    מנחה דיון
  • אני עדיין לא יורד לסוף דעתך.

    מה עוצר אותך מלהכניס את אחת התשובות ששיכות לשאלה 2 כברירת מחדל לשאלה 1?

    INSERT INTODefault_Q_AVALUES (1,3,1) -- left-to-right


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

    יום חמישי 31 מאי 2012 09:02
  • נסה לעשות את זה

    למה לחשוב הרבה בע"פ? שמתי קוד מלא בקישור אז אתה יכול קודם לנסות ואז תקבל שאתה לא יכול לבצע את זה.
    http://ariely.info/Blog/tabid/83/EntryId/86/Questions-Answers-relations.aspx

    אם אחרי שתראה שזה עובד משהו לא יובן אני אוכל להסביר אבל מה טוב יותר מקוד שמסביר זאת?

    תבדוק את הקוד המלא בקישור ששמתי. עדיף לדון על הדוגמה התחתונה שיש בה רק 2 טורים בטבלה המקשרת אבל כמובן אתה יכול לנסות גם בדוגמה העליונה עם השאילתה שאתה רשמת כאן בדיוק ותקבל שאתה לא יכול:

    Msg 2627, Level 14, State 1, Line 2 Violation of UNIQUE KEY constraint 'UQ__Default___053C22036D12A472'. Cannot insert duplicate key in object 'dbo.Default_Q_A'. The duplicate key value is (1).

    The statement has been terminated.

    יש בבלוג דוגמאות קוד של בדיקות כולל מה שאתה שואל. כמו כן שמתי את הודעת השגיאה שמתקבלת למי שעצלן ולא רוצה לנסות לבד וסומך עלי שזה מה שקיבלתי.

    בקצרה אני אנסה שוב להסביר (כנראה שאני לא מסביר את טוב אם זה לא מובן).

    יש 2 פתרונות:

    התנאי שמונע את זה בדוגמה התחתונה הוא:

    ALTER TABLE Default_Q_A ADD CONSTRAINT chk_Default CHECK (D_Q_ID = [dbo].[CONSTRAINT_Default_Q_A](D_A_ID))

    התנאי שמונע את זה בדוגמה למעלה הוא:

    ALTER TABLE Default_Q_A ADD CONSTRAINT chk_Default CHECK (D_Q_ID = D_A_Q_ID)
    הרעיון הבסיסי הוא שיש תנאי שבודק שמספר שאלה כפי שמופיע בטבלה של התשובות זהה למספר השאלה כפי שמופיע בעמודה של ה REFERENCES מטבלת השאלות. לכן לא ניתן להכניס תשובה של שאלה X כברירת מחדל לשאלה Y. זה יגרור סתירה עם התנאים האלו

    signature


    יום חמישי 31 מאי 2012 17:32
    מנחה דיון

  • Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

    יום חמישי 31 מאי 2012 18:28
  • * אני עובד כרגע בעוד פורום ששם הכפתור של "תגובה" נמצא משמאל. אז אני כבר פעם שנייה מוחק לעצמי את התשובה בטעות :-( מזל שאחרי שמוחקים היא עדיין מופיעה... אז אני מעתיק לוורד וכותב אותה שוב:

    אתה מכניס נתון לא הגיוני שאינו בבדיקה!

    לכן כתבתי את השיטה השנייה. נסה את העבודה עם הקוד התחתון

    בחלק העליון כתבתי במפורש מההתחלה שניהול של הנתון מספר השאלה מגיע מאפליקציה! מספר השאלה לא יכול להיות 1 עבור תשובה 3. האפליקציה שמכניסה את הנתון צריכה להכניס נתון הגיוני שהיא הביאה ממסד הנתונים, או שאם רוצים ללכת על בטוח אז תעבוד עם הקוד התחתון בו ניהול הנתון הזה נעשה ב SQL

    * הכל רשום בהסברים בפורום (עדיין לא הגעתי לכתוב את הבלוג מסודר עם כל ההסברים כמו שאמרתי והעלתי אותו בשביל להציג בינתיים את הקוד לקוראי הפורום)

    זה לא המצב שהצגת של הכנסת תשובה של שאלה אחרת אלא זה זיוף נתון לא ממסד הנתונים :-)

    אם אתה רוצה להכניס את תשובה 3 לשאלה 1 אז אתה צריך להכניס 1,3,2 מכיוון שתשובה 3 שייכת לשאלה 2 והאפליקציה מכניסה את הנתון של הטור השלישי מתוך הנתונים של תשובה 3.

    במקרה של הקוד התחתון הכל מכוסה ברמת ה SQL כמו שאמרתי.

    תבדוק שמההתחלה רשמתי בהערה בשורה של הקוד את המשפט:
    -- כאן נכניס את הערך של מספר השאלה המתאים לתשובה שבחרנו

    ולהבהרה שוב: המקרה הראשון מנוהל הנתון על ידי אפליקציה שקראת את (מספר ה ID של השאלה + מספר ה ID של התשובה) מתוך טבלת התשובות + (מספר ה ID של השאלה) מתוך טבלת השאלות, ואת הנתונים האלה מכניסה לטבלת ברירת המחדל

    אם האפליקציה שלך ייצרה את הנתונים האלו אז פנה למפתח שלה :-) זה האפיון של הדוגמה הראשונה. לחילופין תעבור לאפיון של הפתרון השני.

    ** בפתרון השני אין תלות באפליקציה ולכן לא יכול להיות טעות כזו שם. ומכאן שאתה יכול לעשות שימוש בקוד התחתון כפתרון לבעיה ולבדוק אותו

    יום חמישי 31 מאי 2012 19:13
    מנחה דיון
  • הכנסתי את כל ההסברים לבלוג כולל הלוגיקה המלאה ו 2 קבצים להורדה עם 2 הפתרונות המלאים שהצגתי. אני מקווה שזה יהיה יותר מובן עתה

    * בגלל כל ההסברים זה יצא  א ר ו ך,  לכן גם שמתי את קישורים לקפוץ לנקודות שונות במדריך או לסיום להורדה של השאילתות הסופיות.


    יום שישי 01 יוני 2012 18:49
    מנחה דיון
  • היי,

    1. הפתרון הראשון שלך לא עונה על הדרישה של אכיפת החוק דרך הסכימה מכיוון שאתה מסתמך על האפליקציה שתכניס רק נתונים "הגיוניים".

    2. הפתרון השני שלך עובד אבל מסתמך על DML בתוך UDF לבדיקה האם כבר יש תשובת ברירת מחדל. פתרון דומה הציע חיים קודם לכן ללא צורך בתוספת טבלה.  :-)

    עוד רעיונות?


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

    שבת 02 יוני 2012 17:03