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

A CHECK constraint specifies an expression that must be satisfied for data to be allowed in the table, violated when the expression is FALSE.

Demonstrate a CHECK constraint (create, alter)?

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 directly within the column definitions.

Question 15

What SQL command is used to add a UNIQUE constraint to a 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);