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.

Example:
sql Name VARCHAR(20) 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.

Example:
sql FOREIGN KEY (DepartmentCode) REFERENCES Department (Code)

Question 4

Provide an example of a CREATE TABLE statement with multiple constraints including a PRIMARY KEY.

Answer 4

sql CREATE TABLE Employee ( ID INT, Name VARCHAR(20) NOT NULL, DepartmentCode INT DEFAULT 999, PRIMARY KEY (ID), FOREIGN KEY (DepartmentCode) REFERENCES Department (Code) );

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

How can you enforce uniqueness in a column using a constraint?

Answer 6

Use the UNIQUE constraint, which ensures that all values in a column or a group of columns are unique.

Example:
sql UNIQUE (ColumnName)

Question 7

What is the purpose of a CHECK constraint?

Answer 7

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

Example:
sql CHECK (BirthDate < HireDate)

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.

Example:
sql CHECK (HireDate >= '2000-01-01' AND HireDate <= '2019-12-31')

Question 9

How can you name a constraint when creating a table?

Answer 9

Use the CONSTRAINT keyword followed by the name of the constraint and the constraint declaration.

Example:
sql CONSTRAINT ConstraintName UNIQUE (Column1, Column2)

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:

sql ALTER TABLE TableName ADD CONSTRAINT ConstraintName CHECK (condition);

Question 11

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

Answer 11

Use the following SQL command:

sql ALTER TABLE Department DROP INDEX UniqueNameMgr;

Question 12

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

Answer 12

The syntax is:

sql ADD [CONSTRAINT ConstraintName] PRIMARY KEY (Column1, Column2 ...)

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

A composite PRIMARY KEY is defined by specifying multiple columns in a table constraint.

Example:

sql PRIMARY KEY (Column1, Column2)

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:

sql 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

When 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 UNIQUE constraint and a PRIMARY KEY constraint?

Answer 17

A PRIMARY KEY constraint does not allow NULL values and uniquely identifies each record, while 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 TABLE command:

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 > 1000)

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:

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