Test Your PostgreSQL Primary Key Knowledge with this Quiz

Progress

True / False

In PostgreSQL, a primary key uniquely identifies each record in a table.

True False

A primary key is a column or a set of columns that uniquely identifies each row in a table, ensuring no duplicate records.

True / False

A table in PostgreSQL can have more than one primary key.

True False

A table can have only one primary key, which may consist of one or multiple columns (composite key).

True / False

In PostgreSQL, a primary key column cannot contain NULL values.

True False

Primary key columns must contain unique and non-NULL values to ensure the integrity of the data.

True / False

In PostgreSQL, a primary key constraint automatically creates a unique index on the primary key column(s).

True False

When a primary key is defined, PostgreSQL automatically creates a unique index on the column(s) to enforce the uniqueness constraint.

True / False

In PostgreSQL, you can define a primary key on an existing table using the ALTER TABLE statement.

True False

You can use the ALTER TABLE statement to add a primary key constraint to an existing table.

True / False

PostgreSQL allows defining a primary key on a column that already has a unique constraint.

True False

A primary key can be defined on a column that already has a unique constraint, as long as it does not contain NULL values.

True / False

In PostgreSQL, a composite primary key can consist of columns from different tables.

True False

A composite primary key must consist of columns within the same table.

True / False

Dropping a primary key constraint in PostgreSQL will automatically drop any associated indexes.

True False

Dropping a primary key constraint will also remove the unique index associated with it.

True / False

In PostgreSQL, you can use the USING INDEX clause to specify a custom index for enforcing a primary key constraint.

True False

The USING INDEX clause is not available for primary key constraints in PostgreSQL; primary keys always create their own unique indexes.

Q10

True / False

In PostgreSQL, defining a primary key on a table that already contains duplicate values in the target column(s) will succeed without errors.

True False

Defining a primary key on columns that contain duplicate values will result in an error, as primary keys require all values to be unique and non-NULL.

Q11

Single Choice

What is the main purpose of a Primary Key in a PostgreSQL table?

To enforce uniqueness of values in a column To allow duplicate values in a column To define a relationship between two tablesTo store large binary objects

A Primary Key in PostgreSQL ensures that all values in the specified column(s) are unique and not null.

Q12

Single Choice

Which SQL command is used to define a Primary Key when creating a new table in PostgreSQL?

CREATE PRIMARY KEY ADD PRIMARY KEY CONSTRAINT PRIMARY KEYCREATE TABLE

The Primary Key is defined within the CREATE TABLE statement by specifying the column(s) and using the PRIMARY KEY constraint.

Q13

Single Choice

In PostgreSQL, can a table have more than one Primary Key?

Yes No NullNull

A table in PostgreSQL can have only one Primary Key, which can consist of one or more columns.

Q14

Single Choice

How do you add a Primary Key to an existing table in PostgreSQL?

ALTER TABLE table_name ADD PRIMARY KEY (column_name) ALTER TABLE table_name MODIFY PRIMARY KEY (column_name) ALTER TABLE table_name SET PRIMARY KEY (column_name)ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY (column_name);

To add a Primary Key to an existing table, you use the ALTER TABLE command along with ADD CONSTRAINT to define the Primary Key.

Q15

Single Choice

Consider the following PostgreSQL SQL statement:

SQL Code

CREATE TABLE employees (
 emp_id SERIAL,
 first_name VARCHAR(50),
 last_name VARCHAR(50),
 PRIMARY KEY (emp_id)
);
What does the PRIMARY KEY (emp_id) line do?

It allows duplicate values in the emp_id column It enforces uniqueness and non-null values in the emp_id column It indexes the first_name columnIt allows null values in the emp_id column

The PRIMARY KEY (emp_id) line ensures that the emp_id column contains unique, non-null values in the employees table.

Q16

Single Choice

What happens if you try to insert a duplicate value in a column that is defined as a Primary Key in PostgreSQL?

The row is inserted with a warning The row is inserted, but the duplicate value is ignored The insertion fails with an errorThe existing row with the duplicate value is updated

PostgreSQL does not allow duplicate values in a column defined as a Primary Key, so the insertion fails.

Q17

Single Choice

Given the following PostgreSQL SQL statement:

SQL Code

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 customer_id INT,
 order_date DATE,
 PRIMARY KEY (order_id)
);
What will happen if you attempt to execute this command?

The table will be created successfully The table will not be created due to a syntax error The table will be created but with a duplicate Primary Key constraintThe table will be created, but the PRIMARY KEY (order_id) line will be ignored

The PRIMARY KEY (order_id) constraint is defined twice in the same statement, which will result in a syntax error in PostgreSQL.

Q18

Single Choice

How can you define a composite Primary Key in PostgreSQL using SQL?

PRIMARY KEY (column1, column2) ADD PRIMARY KEY (column1, column2) CONSTRAINT pk_name PRIMARY KEY (column1, column2)UNIQUE (column1, column2)

In PostgreSQL, a composite Primary Key can be defined by listing multiple columns in the PRIMARY KEY clause.

Q19

Single Choice

Consider the following PostgreSQL SQL command:

SQL Code

CREATE TABLE enrollment (
 student_id INT,
 course_id INT,
 PRIMARY KEY (student_id, course_id)
);
What type of key is defined in this table?

Composite Primary Key Foreign Key Unique KeyCandidate Key

The PRIMARY KEY constraint includes two columns, student_id and course_id, making it a composite Primary Key.

Q20

Single Choice

In PostgreSQL, which of the following is a correct method to remove a Primary Key constraint from a table?

ALTER TABLE table_name DROP PRIMARY KEY; ALTER TABLE table_name REMOVE PRIMARY KEY; ALTER TABLE table_name DROP CONSTRAINT pk_name;ALTER TABLE table_name DELETE PRIMARY KEY;

To remove a Primary Key constraint in PostgreSQL, you need to use the ALTER TABLE command with the DROP CONSTRAINT clause, specifying the name of the constraint

Q21

Multiple Choice

Which statement best describes a primary key in an RDBMS like PostgreSQL?

SQL Code

CREATE TABLE students (
 student_id SERIAL PRIMARY KEY,
 first_name VARCHAR(50),
 last_name VARCHAR(50)
);

A primary key uniquely identifies each row in a table. A primary key can contain NULL values. A primary key can have duplicate values.A primary key is optional in every table.

A primary key in an RDBMS uniquely identifies each record in a table, ensuring that no two rows have the same primary key value.

Q22

Multiple Choice

What is the significance of using the SERIAL keyword in defining a primary key in PostgreSQL?

SQL Code

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 order_date DATE,
 customer_id INT
);

SERIAL automatically generates unique identifiers. SERIAL allows for manual entry of primary key values. SERIAL is used to create foreign keys.SERIAL does not enforce uniqueness.

The SERIAL keyword in PostgreSQL automatically generates a unique identifier for the primary key column, which is essential for ensuring each record is unique.

Q23

Multiple Choice

How does a primary key contribute to database normalization in an RDBMS?

SQL Code

CREATE TABLE departments (
 department_id SERIAL PRIMARY KEY,
 department_name VARCHAR(100)
);

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 first_name VARCHAR(50),
 department_id INT REFERENCES departments(department_id)
);

Primary keys reduce data redundancy. Primary keys allow multiple NULL values. Primary keys are used to create indexes.Primary keys can be duplicated in other tables.

Primary keys help in database normalization by uniquely identifying records, reducing redundancy, and ensuring that relationships between tables are clearly defined.

Q24

Multiple Choice

Can a table in PostgreSQL have more than one primary key? Select the correct statements.

SQL Code

CREATE TABLE projects (
 project_id SERIAL PRIMARY KEY,
 project_name VARCHAR(100),
 manager_id INT
);

A table can have only one primary key. A table can have multiple primary keys. A primary key can consist of multiple columns.A primary key must be a single column.

A table in PostgreSQL can have only one primary key, but it can consist of multiple columns (composite key). This ensures that the combination of values in the primary key columns is unique.

Q25

Multiple Choice

Which of the following constraints is automatically applied to a primary key in PostgreSQL?

SQL Code

CREATE TABLE inventory (
 item_id SERIAL PRIMARY KEY,
 item_name VARCHAR(100),
 quantity INT
);

NOT NULL UNIQUE CHECKDEFAULT

A primary key in PostgreSQL automatically enforces both the UNIQUE and NOT NULL constraints, ensuring that the key values are both unique and cannot be NULL.

Q26

Multiple Choice

How can a primary key be altered in an existing PostgreSQL table?

SQL Code

CREATE TABLE sales (
 sale_id INT,
 transaction_id INT,
 sale_date DATE
);

ALTER TABLE sales ADD PRIMARY KEY (sale_id);
ALTER TABLE sales DROP CONSTRAINT sales_pkey;
ALTER TABLE sales ADD PRIMARY KEY (transaction_id);

Drop the existing primary key, then add a new one. Directly replace the existing primary key. Primary keys cannot be altered once set.Add a new primary key alongside the existing one.

In PostgreSQL, altering a primary key requires dropping the existing primary key constraint and then adding a new one. This process ensures that the new primary key adheres to the rules of uniqueness and non-nullability.

Q27

Multiple Choice

In a composite primary key, how does PostgreSQL ensure the uniqueness of records?

SQL Code

CREATE TABLE enrollments (
 student_id INT,
 course_id INT,
 enrollment_date DATE,
 PRIMARY KEY (student_id, course_id)
);

By ensuring that the combination of key values is unique. By allowing NULL values in any of the key columns. By enforcing uniqueness on each individual column.By allowing duplicate combinations across columns.

A composite primary key in PostgreSQL ensures uniqueness by combining multiple columns. The combination of values in these columns must be unique across all rows in the table.

Q28

Multiple Choice

What happens if an attempt is made to insert a duplicate value into a primary key column in PostgreSQL?

SQL Code

CREATE TABLE members (
 member_id SERIAL PRIMARY KEY,
 member_name VARCHAR(100)
);

INSERT INTO members (member_name) VALUES ('Alice');
INSERT INTO members (member_id, member_name) VALUES (1, 'Bob');

PostgreSQL raises a constraint violation error. The duplicate value is automatically adjusted. The duplicate value is ignored, and the row is not inserted.The existing record is updated with the new value.

If a duplicate value is inserted into a primary key column, PostgreSQL will raise a constraint violation error, as primary key values must be unique.

Q29

Multiple Choice

Can a primary key in PostgreSQL be created on a column with NULL values?

SQL Code

CREATE TABLE libraries (
 library_id SERIAL PRIMARY KEY,
 library_name VARCHAR(100),
 location VARCHAR(100)
);

INSERT INTO libraries (library_name, location) VALUES ('Central Library', NULL);
ALTER TABLE libraries ADD PRIMARY KEY (location);

No, primary key columns cannot have NULL values. Yes, but only one NULL value is allowed. Yes, as long as the column is unique.No, but NULL values are converted to a default value.

In PostgreSQL, a primary key cannot be created on a column with NULL values. The primary key constraint enforces both uniqueness and non-nullability.

Q30

Multiple Choice

What is the role of a primary key in establishing relationships between tables in PostgreSQL?

SQL Code

CREATE TABLE departments (
 department_id SERIAL PRIMARY KEY,
 department_name VARCHAR(100)
);

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 department_id INT REFERENCES departments(department_id)
);

Primary keys uniquely identify records for referencing in other tables. Primary keys are optional in relational databases. Primary keys allow duplicate values across related tables.Primary keys do not enforce data integrity.

A primary key plays a crucial role in establishing relationships between tables by serving as a unique identifier that foreign keys in other tables can reference.

PostgreSQL Database Quiz Questions