Section 2.12 Constraints Flashcards
(38 cards)
What is a constraint in the context of a database?
- A constraint is a rule that governs allowable values in a database and is implemented with special keywords in a
CREATE TABLEstatement. - The database automatically rejects any insert, update, or delete statements that violate a constraint.
What is the syntax for creating a NOT NULL constraint in a CREATE TABLE statement?
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,
...
);
How do you define a FOREIGN KEY constraint in SQL?
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)
);
This statement provides an example with which types of constraints?
- PRIMARY KEY: Uniquely identifies each row in the Employee table (ID column).
- NOT NULL: Ensures the Name column always has a value.
- FOREIGN KEY: Links Employee records to valid Department records (DepartmentCode referencing Code).
- DEFAULT: Sets a default value of ‘999’ for DepartmentCode if none is given.
These constraints ensure data integrity and consistency in the database.
What does the DEFAULT constraint do?
The DEFAULT constraint specifies a value inserted ; when a column is omitted from an INSERT statement, but does not limit allowable values.
- The
UNIQUEconstraint, which ensures that all values in a column; or a group of columns are unique. - Demonstrate the
UNIQUEconstraint (create, alter)?
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);
There is no need to ALTER column since it’s included last
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.
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);
How do you add a CHECK constraint in a CREATE TABLE statement?
It can be added in the column declaration or as a separate clause.
How can you name a constraint when creating a table?
What is the syntax to add a new CHECK constraint using ALTER TABLE?
Use the following syntax to add a CHECK constraint:
ALTER TABLE TableName ADD CONSTRAINT ConstraintName CHECK (condition);
What is the command to drop the UNIQUE constraint called UniqueNameMgr from a table called DEPARTMENT?
Use the following SQL command:
ALTER TABLE Department
DROP INDEX UniqueNameMgr;
What is the syntax for adding a PRIMARY KEY constraint to an existing table?
What type of constraint is used to ensure that a column cannot have a NULL value?
The NOT NULL constraint is used to ensure that a column cannot contain NULL values.
Example:
sql
ColumnName VARCHAR(50) NOT NULL
What is the syntax for defining a composite PRIMARY KEY in a CREATE TABLE statement?
-
Inline constraint definition you specify the
PRIMARY KEYconstraint directly within the column definitions. -
OOL constraint definition you specify the
PRIMARY KEYconstraint separately from the column definitions, usually after all columns have been declared.
What SQL command is used to add a UNIQUE constraint to an existing table?
Use the ALTER TABLE command with ADD CONSTRAINT.
Example:
ALTER TABLE TableName
ADD CONSTRAINT UniqueConstraintName UNIQUE (ColumnName);
How can you check if a constraint has been violated in a table?
After attempting to insert or update data that violates a constraint, the database generates an error message indicating the violation.
What is the difference between a PRIMARY KEY constraint and a UNIQUE constraint?
- A
PRIMARY KEYconstraint does not allow NULL values and uniquely identifies each record. - A
UNIQUEconstraint allows one NULL value and ensures all other values are unique.
Can multiple FOREIGN KEY constraints be defined in a single table?
Yes, multiple FOREIGN KEY constraints can be defined in a single table, each referencing different parent tables or columns.
How can you enforce that a HireDate must be later than a BirthDate using a constraint?
You can use a CHECK constraint.
Example:
sql
CHECK (HireDate > BirthDate)
What happens if a CHECK constraint is violated during an INSERT operation?
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.
What is an example SQL command to drop a PRIMARY KEY constraint from a table?
Use the following SQL command to drop a primary key constraint:
sql
ALTER TABLE TableName
DROP PRIMARY KEY;
How do you alter an existing column to add a DEFAULT constraint?
You can alter the column with the ALTER $ command & SET DEFAULT:
ALTER TABLE TableName ALTER COLUMN ColumnName SET DEFAULT 'DefaultValue';
What is the significance of naming constraints?
Naming constraints helps identify them in error messages and makes database management easier by allowing you to drop or modify specific constraints.
How can you specify that a column must be greater than a certain value using a constraint?
You would use a CHECK constraint for this purpose.
Example:
sql
CHECK (ColumnName > $)
