Section 2.12 Constraints

Question 1

What is a constraint in the context of a database?

Answer 1

1. A constraint is a rule that governs allowable values in a database and is implemented with special keywords in a CREATE TABLE statement.
2. The database automatically rejects any insert, update, or delete statements that violate a constraint.

Question 2

What is the syntax for creating a NOT NULL constraint in a CREATE TABLE statement?

Answer 2

A NOT NULL constraint is placed after the column name and data type.

CREATE TABLE table_name (
    column1_name data_type NOT NULL,
    column2_name data_type NOT NULL,
    ...
);

Question 3

How do you define a FOREIGN KEY constraint in SQL?

Answer 3

A FOREIGN KEY constraint is defined in a separate clause of a CREATE TABLE statement.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(255),
    DepartmentCode INT,
    FOREIGN KEY (DepartmentCode) REFERENCES Departments(Code)
);

📖 Separate Clauses?

Question 4

This statement provides an example with which types of constraints?

Answer 4

1. PRIMARY KEY: Uniquely identifies each row in the Employee table (ID column).
2. NOT NULL: Ensures the Name column always has a value.
3. FOREIGN KEY: Links Employee records to valid Department records (DepartmentCode referencing Code).
4. DEFAULT: Sets a default value of ‘999’ for DepartmentCode if none is given.

These constraints ensure data integrity and consistency in the database.

Question 5

What does the DEFAULT constraint do?

Answer 5

The DEFAULT constraint specifies a value inserted ; when a column is omitted from an INSERT statement, but does not limit allowable values.

Question 6

1. The UNIQUE constraint, which ensures that all values in a column; or a group of columns are unique.
2. Demonstrate the UNIQUE constraint (create, alter)?

Answer 6

CREATE TABLE your_table_name (
   column_name DATA_TYPE UNIQUE,
   -- other columns and constraints
);

ALTER TABLE your_table_name
ADD CONSTRAINT unique_constraint_name UNIQUE (column_name);

Question 7

Add a constraint CK_BirthDate to the existing or non-existent table Employees specifying that BirthDate must be greater than HireDate which satisfies data to be allowed in the table.

Answer 7

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    BirthDate DATE,
    HireDate DATE,
    CHECK (BirthDate < HireDate) 
);

ALTER TABLE Employees
  ADD CONSTRAINT CK_BirthDate 
  CHECK (BirthDate < HireDate); 

📖 Countering?

Question 8

How do you add a CHECK constraint in a CREATE TABLE statement?

Answer 8

It can be added in the column declaration or as a separate clause.

Question 9

How can you name a constraint when creating a table?

Answer 9

Question 10

What is the syntax to add a new CHECK constraint using ALTER TABLE?

Answer 10

Use the following syntax to add a CHECK constraint:

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName 
CHECK (condition);

Question 11

What is the command to drop the UNIQUE constraint called UniqueNameMgr from a table called DEPARTMENT?

Answer 11

Use the following SQL command:

ALTER TABLE Department DROP INDEX UniqueNameMgr;

Question 12

What is the syntax for adding a PRIMARY KEY constraint to an existing table?

Answer 12

Question 13

What type of constraint is used to ensure that a column cannot have a NULL value?

Answer 13

The NOT NULL constraint is used to ensure that a column cannot contain NULL values.

Example:

sql ColumnName VARCHAR(50) NOT NULL

Question 14

What is the syntax for defining a composite PRIMARY KEY in a CREATE TABLE statement?

Answer 14

1. Inline constraint definition you specify the PRIMARY KEY constraint directly within the column definitions.
2. OOL constraint definition you specify the PRIMARY KEY constraint separately from the column definitions, usually after all columns have been declared.

Question 15

What SQL command is used to add a UNIQUE constraint to an existing table?

Answer 15

Use the ALTER TABLE command with ADD CONSTRAINT.

Example:

ALTER TABLE TableName ADD CONSTRAINT UniqueConstraintName UNIQUE (ColumnName);

Question 16

How can you check if a constraint has been violated in a table?

Answer 16

After attempting to insert or update data that violates a constraint, the database generates an error message indicating the violation.

Question 17

What is the difference between a PRIMARY KEY constraint and a UNIQUE constraint?

Answer 17

1. A PRIMARY KEY constraint does not allow NULL values and uniquely identifies each record.
2. A UNIQUE constraint allows one NULL value and ensures all other values are unique.

Question 18

Can multiple FOREIGN KEY constraints be defined in a single table?

Answer 18

Yes, multiple FOREIGN KEY constraints can be defined in a single table, each referencing different parent tables or columns.

Question 19

How can you enforce that a HireDate must be later than a BirthDate using a constraint?

Answer 19

You can use a CHECK constraint.

Example:

sql CHECK (HireDate > BirthDate)

Question 20

What happens if a CHECK constraint is violated during an INSERT operation?

Answer 20

If a CHECK constraint is violated, the INSERT operation fails, and an error message is returned, preventing the data from being saved in the table.

📖

Question 21

What is an example SQL command to drop a PRIMARY KEY constraint from a table?

Answer 21

Use the following SQL command to drop a primary key constraint:

sql ALTER TABLE TableName DROP PRIMARY KEY;

Question 22

How do you alter an existing column to add a DEFAULT constraint?

Answer 22

You can alter the column with the ALTER $ command & SET DEFAULT:

sql ALTER TABLE TableName ALTER COLUMN ColumnName SET DEFAULT 'DefaultValue';

Question 23

What is the significance of naming constraints?

Answer 23

Naming constraints helps identify them in error messages and makes database management easier by allowing you to drop or modify specific constraints.

Question 24

How can you specify that a column must be greater than a certain value using a constraint?

Answer 24

You would use a CHECK constraint for this purpose.

Example:

sql CHECK (ColumnName > $)

Question 25

What is the syntax for adding a foreign key constraint to an existing table?

Answer 25

Use the ALTER TABLE command with ADD CONSTRAINT.

Example:

ALTER TABLE TableName ADD CONSTRAINT FK_Name FOREIGN KEY (ColumnName) REFERENCES OtherTable (OtherColumn);

Question 26

What category is aFOREIGN KEY constraint?

Answer 26

A FOREIGN KEY is exclusively a table constraint.

A FOREIGN KEY is used to establish a relationship between two tables.

Question 27

What SQL statement retrieves the creation details of a specified table?

Answer 27

SHOW CREATE TABLE TableName retrieves the SQL statement that creates the specified table, including the definitions for all constraints that are associated with it.

This command is useful for understanding the structure and constraints of an existing table.

Question 28

What SQL command can be used to find constraints associated with a specific table?

Answer 28

SELECT Column_Name, Constraint_Name 
FROM Information_Schema.Key_Column_Usage 
WHERE Table_Name = 'TableName';

This query provides information on the key columns and their constraints in the specified table.

Question 29

How can unnamed constraints like NOT NULL and DEFAULT be modified?

Answer 29

Unnamed constraints such as NOT NULL and DEFAULT are added or dropped with a CHANGE clause.

The CHANGE clause allows for altering the structure of the table without needing to drop and recreate the entire column.