Test Your PostgreSQL CHECK Constraint Knowledge with this Quiz

Progress

True / False

In PostgreSQL, a CHECK constraint can only be applied to a single column.

True False

A CHECK constraint can be applied to a single column or multiple columns. It is a condition that must be true for all rows in the table.

True / False

The CHECK constraint in PostgreSQL is enforced by the database whenever an insert or update operation is performed.

True False

PostgreSQL enforces CHECK constraints during both insert and update operations to ensure data integrity.

True / False

In PostgreSQL, CHECK constraints can reference other tables.

True False

CHECK constraints cannot reference columns in other tables. They are limited to conditions within the same table.

True / False

In PostgreSQL, a CHECK constraint can use expressions involving multiple columns of the same table.

True False

CHECK constraints can use expressions that involve multiple columns of the same table to enforce complex conditions.

True / False

In PostgreSQL, CHECK constraints can be temporarily disabled and later re-enabled.

True False

CHECK constraints can be disabled using ALTER TABLE ... ALTER CONSTRAINT ... NO VALIDATE and re-enabled with ALTER TABLE ... VALIDATE CONSTRAINT ....

True / False

In PostgreSQL, CHECK constraints are validated for existing data in the table when they are first created.

True False

When a CHECK constraint is created, PostgreSQL validates it against existing data to ensure all rows satisfy the condition.

True / False

PostgreSQL allows the creation of a CHECK constraint using custom functions.

True False

PostgreSQL allows the use of custom functions within CHECK constraints to enforce more complex rules

True / False

CHECK constraints in PostgreSQL can be used to enforce referential integrity.

True False

CHECK constraints cannot enforce referential integrity, which is the job of foreign keys. CHECK constraints enforce conditions on the data within the same table.

True / False

In PostgreSQL, CHECK constraints can be defined at the table level, affecting multiple columns.

True False

CHECK constraints can be defined at the table level and can include conditions that involve multiple columns within the table.

Q10

True / False

In PostgreSQL, the CHECK constraint condition can include subqueries.

True False

CHECK constraints cannot include subqueries. The condition must be a simple expression that does not involve subqueries or references to other tables.

Q11

Single Choice

Which SQL statement correctly adds a CHECK constraint to ensure that the "age" column in a MySQL table only allows values greater than 18?

SQL Code

ALTER TABLE employees
ADD CONSTRAINT chk_age CHECK (age > 18);

Yes No NullNull

The SQL statement correctly adds a CHECK constraint to the "employees" table, ensuring that the "age" column only allows values greater than 18.

Q12

Single Choice

In MySQL, what does the following CHECK constraint do?

SQL Code

CHECK (salary >= 0)

Ensures that the "salary" column must be greater than 0. Ensures that the "salary" column must be a positive integer. Ensures that the "salary" column cannot be negativeEnsures that the "salary" column must be less than 1000.

The CHECK constraint in this case ensures that the "salary" column cannot have a negative value.

Q13

Single Choice

Does MySQL enforce CHECK constraints by default?

Yes On NullNull

As of MySQL 8.0.16, MySQL parses and stores CHECK constraints, but it does not enforce them by default. The constraints are parsed, but they do not affect the data.

Q14

Single Choice

Given the following table creation SQL code, what will happen if you try to insert a row with a status value of 'Archived'?

SQL Code

CREATE TABLE orders (
 order_id INT PRIMARY KEY,
 status ENUM('Pending', 'Shipped', 'Delivered') CHECK (status IN ('Pending', 'Shipped', 'Delivered'))
);

The row will be inserted. The row will be rejected due to the CHECK constraint. MySQL will raise a syntax error.The CHECK constraint will be ignored.

Although the CHECK constraint is defined, MySQL does not enforce it as of version 8.0.16. Therefore, the row with a status value of 'Archived' will be inserted without issue.

Q15

Single Choice

What is the correct way to define a CHECK constraint to ensure the quantity column in the products table is always greater than zero?

SQL Code

CREATE TABLE products (
 product_id INT PRIMARY KEY,
 product_name VARCHAR(255),
 quantity INT CHECK (quantity > 0)
);

The CHECK constraint is correct, and it will enforce the condition. The CHECK constraint is correct, but MySQL will not enforce it. The CHECK constraint is incorrect syntax.The CHECK constraint should use "quantity >= 0".

The CHECK constraint syntax is correct, but MySQL does not enforce CHECK constraints as of version 8.0.16.

Q16

Single Choice

When creating a table in MySQL, which of the following statements about the CHECK constraint is true?

CHECK constraints are strictly enforced in MySQL. CHECK constraints are parsed but not enforced in MySQL. CHECK constraints are enforced only on INTEGER columns.CHECK constraints are deprecated in MySQL.

MySQL parses CHECK constraints but does not enforce them by default as of version 8.0.16.

Q17

Single Choice

You have the following MySQL table definition. Which of the following statements about inserting records is correct?

SQL Code

CREATE TABLE employees (
 emp_id INT PRIMARY KEY,
 salary DECIMAL(10,2),
 CHECK (salary > 3000 AND salary < 10000)
);

MySQL will enforce the salary range condition. MySQL will ignore the salary range condition. MySQL will raise an error due to the CHECK constraint.MySQL will enforce the constraint only for new records

MySQL does not enforce CHECK constraints as of version 8.0.16, so the salary range condition will be ignored.

Q18

Single Choice

Which SQL statement would add a CHECK constraint to an existing MySQL table to ensure that the price column is always greater than zero?

SQL Code

ALTER TABLE products
ADD CONSTRAINT chk_price CHECK (price > 0);

This will enforce the constraint. This will add the constraint but not enforce it. This will raise a syntax error.This will drop the price column.

The ALTER TABLE statement will successfully add the CHECK constraint, but it will not enforce it as MySQL does not enforce CHECK constraints by default.

Q19

Single Choice

How would you define a CHECK constraint in MySQL to ensure the birth_date column in the users table does not allow future dates?

SQL Code

CREATE TABLE users (
 user_id INT PRIMARY KEY,
 birth_date DATE,
 CHECK (birth_date <= CURRENT_DATE)
);

This will enforce the constraint on the birth_date column. This will add the constraint but will be ignored by MySQL. This will raise an error due to incorrect use of CURRENT_DATEThis will only work for new rows inserted after the constraint is added.

The CHECK constraint is correctly defined but will not be enforced as MySQL does not enforce CHECK constraints by default.

Q20

Single Choice

Which of the following accurately describes the current status of CHECK constraints in MySQL 8.0?

CHECK constraints are fully enforced by MySQL 8.0. CHECK constraints are only enforced for certain data types CHECK constraints are parsed, stored, but not enforced by default.CHECK constraints are deprecated and should not be used

As of MySQL 8.0.16, CHECK constraints are parsed and stored in the table definition, but they are not enforced by the database engine.

Q21

Multiple Choice

Which SQL command creates a table with a CHECK constraint ensuring that the 'age' column only accepts values greater than or equal to 18?

SQL Code

CREATE TABLE users (
 user_id SERIAL PRIMARY KEY,
 age INT,
 CHECK (age >= 18)
);

CREATE TABLE users ( user_id SERIAL PRIMARY KEY, age INT, CHECK (age >= 18) ); CREATE TABLE users ( user_id SERIAL PRIMARY KEY, age INT CHECK (age >= 18) ); CREATE TABLE users ( user_id SERIAL PRIMARY KEY, age INT, CONSTRAINT check_age CHECK (age >= 18) );CREATE TABLE users ( user_id SERIAL PRIMARY KEY, age INT CHECK (age > 18) );

This command creates a 'users' table with a CHECK constraint that enforces the rule that 'age' must be 18 or older.

Q22

Multiple Choice

Which SQL command creates a table with a CHECK constraint ensuring that the 'salary' column is between 30000 and 200000?

SQL Code

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 salary NUMERIC(10, 2) NOT NULL,
 CHECK (salary BETWEEN 30000 AND 200000)
);

CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, salary NUMERIC(10, 2) NOT NULL, CHECK (salary BETWEEN 30000 AND 200000) ); CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, salary NUMERIC(10, 2) NOT NULL CHECK (salary BETWEEN 30000 AND 200000) ); CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, salary NUMERIC(10, 2) CHECK (salary >= 30000 AND salary <= 200000) );CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, salary NUMERIC(10, 2) NOT NULL, CONSTRAINT check_salary CHECK (salary BETWEEN 30000 AND 200000) );

This command ensures that the 'salary' column in the 'employees' table only accepts values between 30000 and 200000.

Q23

Multiple Choice

Which SQL command adds a CHECK constraint to ensure that the 'stock' column in the 'products' table is always non-negative?

SQL Code

ALTER TABLE products
ADD CONSTRAINT check_stock CHECK (stock >= 0);

ALTER TABLE products ADD CONSTRAINT check_stock CHECK (stock >= 0); ALTER TABLE products ADD CHECK (stock >= 0); ALTER TABLE products ALTER COLUMN stock SET CHECK (stock >= 0);ALTER TABLE products ADD CONSTRAINT stock_non_negative CHECK (stock >= 0);

This command adds a CHECK constraint to the 'products' table to ensure that the 'stock' column cannot have negative values.

Q24

Multiple Choice

Which SQL command creates a table with a CHECK constraint on multiple columns to ensure that the 'start_date' is earlier than the 'end_date'?

SQL Code

CREATE TABLE events (
 event_id SERIAL PRIMARY KEY,
 start_date DATE NOT NULL,
 end_date DATE NOT NULL,
 CHECK (start_date < end_date)
);

CREATE TABLE events ( event_id SERIAL PRIMARY KEY, start_date DATE NOT NULL, end_date DATE NOT NULL, CHECK (start_date < end_date) ); CREATE TABLE events ( event_id SERIAL PRIMARY KEY, start_date DATE NOT NULL, end_date DATE NOT NULL CHECK (start_date < end_date) ); CREATE TABLE events ( event_id SERIAL PRIMARY KEY, start_date DATE NOT NULL, end_date DATE NOT NULL, CONSTRAINT check_dates CHECK (start_date < end_date) );CREATE TABLE events ( event_id SERIAL PRIMARY KEY, start_date DATE, end_date DATE, CHECK (start_date < end_date) );

This command creates an 'events' table with a CHECK constraint that enforces the rule that 'start_date' must be earlier than 'end_date'.

Q25

Multiple Choice

Which SQL command creates a table with a CHECK constraint that ensures 'discount' is less than 'price' in the 'products' table?

SQL Code

CREATE TABLE products (
 product_id SERIAL PRIMARY KEY,
 price NUMERIC(10, 2) NOT NULL,
 discount NUMERIC(10, 2),
 CHECK (discount < price)
);

CREATE TABLE products ( product_id SERIAL PRIMARY KEY, price NUMERIC(10, 2) NOT NULL, discount NUMERIC(10, 2), CHECK (discount < price) ); CREATE TABLE products ( product_id SERIAL PRIMARY KEY, price NUMERIC(10, 2) NOT NULL, discount NUMERIC(10, 2) CHECK (discount < price) ); CREATE TABLE products ( product_id SERIAL PRIMARY KEY, price NUMERIC(10, 2) NOT NULL, discount NUMERIC(10, 2), CONSTRAINT check_discount CHECK (discount < price) );CREATE TABLE products ( product_id SERIAL PRIMARY KEY, price NUMERIC(10, 2), discount NUMERIC(10, 2), CHECK (discount <= price) );

This command creates a 'products' table with a CHECK constraint to ensure that 'discount' is always less than 'price'.

Q26

Multiple Choice

Which SQL command alters an existing table to add a CHECK constraint ensuring that 'quantity' is positive in PostgreSQL?

SQL Code

ALTER TABLE inventory
ADD CONSTRAINT check_quantity_positive CHECK (quantity > 0);

ALTER TABLE inventory ADD CONSTRAINT check_quantity_positive CHECK (quantity > 0); ALTER TABLE inventory ADD CHECK (quantity > 0); ALTER TABLE inventory MODIFY COLUMN quantity CHECK (quantity > 0);ALTER TABLE inventory ALTER COLUMN quantity SET CHECK (quantity > 0);

This command adds a CHECK constraint to the 'inventory' table to ensure that the 'quantity' column always has positive values.

Q27

Multiple Choice

Which SQL command adds a CHECK constraint to ensure that a 'percentage' column is between 0 and 100 in PostgreSQL?

SQL Code

ALTER TABLE assessments
ADD CONSTRAINT check_percentage_range CHECK (percentage >= 0 AND percentage <= 100);

ALTER TABLE assessments ADD CONSTRAINT check_percentage_range CHECK (percentage >= 0 AND percentage <= 100); ALTER TABLE assessments ADD CHECK (percentage BETWEEN 0 AND 100); ALTER TABLE assessments ALTER COLUMN percentage SET CHECK (percentage BETWEEN 0 AND 100);ALTER TABLE assessments ADD CHECK (percentage >= 0 AND percentage <= 100);

This command adds a CHECK constraint to the 'assessments' table ensuring that the 'percentage' column's value is always between 0 and 100.

Q28

Multiple Choice

Which SQL command creates a table with a CHECK constraint that ensures the 'rating' column only accepts values from 1 to 5 in PostgreSQL?

SQL Code

CREATE TABLE reviews (
 review_id SERIAL PRIMARY KEY,
 rating INT NOT NULL,
 CHECK (rating BETWEEN 1 AND 5)
);

CREATE TABLE reviews ( review_id SERIAL PRIMARY KEY, rating INT NOT NULL, CHECK (rating BETWEEN 1 AND 5) ); CREATE TABLE reviews ( review_id SERIAL PRIMARY KEY, rating INT NOT NULL, CONSTRAINT check_rating CHECK (rating BETWEEN 1 AND 5) ); CREATE TABLE reviews ( review_id SERIAL PRIMARY KEY, rating INT CHECK (rating >= 1 AND rating <= 5) );CREATE TABLE reviews ( review_id SERIAL PRIMARY KEY, rating INT NOT NULL, CHECK (rating > 1 AND rating < 5) );

This command creates a 'reviews' table with a CHECK constraint ensuring that the 'rating' column only allows values between 1 and 5.

Q29

Multiple Choice

Which SQL command creates a table with a CHECK constraint that ensures a 'code' column starts with 'ABC' in PostgreSQL?

SQL Code

CREATE TABLE items (
 item_id SERIAL PRIMARY KEY,
 code VARCHAR(10) NOT NULL,
 CHECK (code LIKE 'ABC%')
);

CREATE TABLE items ( item_id SERIAL PRIMARY KEY, code VARCHAR(10) NOT NULL, CHECK (code LIKE 'ABC%') ); CREATE TABLE items ( item_id SERIAL PRIMARY KEY, code VARCHAR(10) NOT NULL, CONSTRAINT check_code CHECK (code LIKE 'ABC%') ); CREATE TABLE items ( item_id SERIAL PRIMARY KEY, code VARCHAR(10) NOT NULL CHECK (code LIKE 'ABC%');CREATE TABLE items ( item_id SERIAL PRIMARY KEY, code VARCHAR(10) CHECK (code LIKE 'ABC%') );

This command creates an 'items' table with a CHECK constraint ensuring that the 'code' column starts with the string 'ABC'.

Q30

Multiple Choice

Which SQL command creates a table with a CHECK constraint that ensures the 'age' column is greater than 0 and less than 120 in PostgreSQL?

SQL Code

CREATE TABLE persons (
 person_id SERIAL PRIMARY KEY,
 age INT NOT NULL,
 CHECK (age > 0 AND age < 120)
);

CREATE TABLE persons ( person_id SERIAL PRIMARY KEY, age INT NOT NULL, CHECK (age > 0 AND age < 120) ); CREATE TABLE persons ( person_id SERIAL PRIMARY KEY, age INT NOT NULL, CONSTRAINT check_age CHECK (age > 0 AND age < 120) ); CREATE TABLE persons ( person_id SERIAL PRIMARY KEY, age INT CHECK (age BETWEEN 1 AND 119) );CREATE TABLE persons ( person_id SERIAL PRIMARY KEY, age INT NOT NULL, CHECK (age > 0 AND age <= 120) );

This command creates a 'persons' table with a CHECK constraint ensuring that the 'age' column only accepts values greater than 0 and less than 120.

PostgreSQL Database Quiz Questions