Ch 7 CONCEPTS

Question 1

Table A is Horse with PK ID
Table B is Student with PK ID
Table C is LessonSchedule with PKs HorseID and LessonDateTime & FK StudentID referencing Student(ID) and FK HorseID referencing Horse(ID)

If a row is deleted from A, how do we tell rows in C with the same Horse ID to be automatically deleted?

Answer 1

After the rest of the LessonSchedule table is created, in the same table creation statement, we add:

FOREIGN KEY (HorseID) REFERENCES Horse(ID) ON DELETE CASCADE,

Question 2

Table A is Horse with PK ID
Table B is Student with PK ID
Table C is LessonSchedule with PKs HorseID and LessonDateTime & FK StudentID referencing Student(ID) and FK HorseID referencing Horse(ID)

If a row is deleted from B, how do we tell rows in C with the same Student IDs to be automatically set to NULL?

Answer 2

After the rest of the LessonSchedule table is created, in the same table creation statement, we add:

FOREIGN KEY (StudentID) REFERENCES Student(ID) ON DELETE SET NULL

Question 3

How do you create a CHECK statement while creating a table that checks and requires the input to be one of MULTIPLE options?

Answer 3

Breed VARCHAR(20) CHECK (Breed IN (‘Egyptian Arab’, ‘Holsteiner’, ‘Quarter Horse’, ‘Paint’, ‘Saddlebred’))

You have to use the IN keyword before the list of possible optiona and also put the check statement in parenthesis AND the LIST in parenthasis inside the check.

ColName DataType CHECK (ColName IN (‘A’, ‘B’, ‘C’))

Question 4

What is a transaction and how do you start one?

Answer 4

A transaction is a group of tasks to be executed. All tasks have to succeed in executing or else the entire transaction fails.

BEGIN TRANSACTION transaction_name ;

Question 5

What is a save point and how do you create one?

Answer 5

In SQL, a SAVEPOINT is a mechanism that allows you to create a point within a transaction to which you can later roll back. This feature is particularly useful when you want to implement a partial rollback in case of errors or other exceptional conditions within a transaction.

START TRANSACTION;

– Perform some SQL operations

SAVEPOINT my_savepoint;

Question 6

How do you go back to a specific save point?

Answer 6

ROLLBACK TO my_savepoint;