Primary key declaration in MySQL - Step-by-Step Execution

Concept Flow - Primary key declaration

Start table creation

↓

Define columns

↓

Declare primary key on column(s)

↓

Primary key enforces uniqueness

↓

Table ready for data insertion

When creating a table, you define columns and then declare one or more columns as the primary key, which ensures each row is unique.

Execution Sample

MySQL

CREATE TABLE users (
  id INT,
  name VARCHAR(50),
  PRIMARY KEY (id)
);

This code creates a table named 'users' with 'id' as the primary key, ensuring 'id' values are unique.

Execution Table

Step	Action	Evaluation	Result
1	Start table creation	Table 'users' does not exist	Table creation process begins
2	Define column 'id' as INT	Column 'id' added	Column 'id' ready
3	Define column 'name' as VARCHAR(50)	Column 'name' added	Column 'name' ready
4	Declare PRIMARY KEY on 'id'	Check uniqueness and NOT NULL constraint	Primary key set on 'id'
5	Finish table creation	Table structure valid	Table 'users' created with primary key

💡 Table creation ends after primary key declaration ensures uniqueness

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Table 'users'	Does not exist	Exists with column 'id'	Exists with columns 'id', 'name'	Primary key on 'id' declared	Ready for data insertion
Primary Key	None	None	None	Declared on 'id'	Declared on 'id'

Key Moments - 2 Insights

Why must a primary key column be unique and not null?

Because the primary key uniquely identifies each row, it cannot have duplicates or missing values. This is shown in execution_table step 4 where uniqueness and NOT NULL are enforced.

Can a table have more than one primary key?

No, a table can have only one primary key, but it can be made of multiple columns (composite key). This is implied in the declaration step 4 where one primary key is set.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the primary key declared?

A Step 4

B Step 2

C Step 3

D Step 5

💡 Hint

Check the 'Action' column for primary key declaration in execution_table row 4

According to variable_tracker, what is the state of the table after step 3?

A Table does not exist

B Table exists with columns 'id' and 'name', no primary key

C Primary key declared on 'id'

D Table ready for data insertion

💡 Hint

Look at 'Table users' row and 'After Step 3' column in variable_tracker

What does the primary key enforce according to the concept_flow?

A Allows null values in key column

B Allows duplicate rows

C Ensures uniqueness of rows

D Deletes duplicate rows automatically

💡 Hint

Refer to the last box in concept_flow about primary key enforcing uniqueness

Concept Snapshot

Primary key declaration syntax:
PRIMARY KEY (column_name)
- Declares one or more columns as unique identifiers
- Ensures no duplicate or null values in key columns
- Only one primary key per table allowed
- Must be declared during or after column definitions

Full Transcript

When creating a table in MySQL, you define columns first. Then you declare a primary key on one or more columns. The primary key ensures each row is unique and the key columns cannot be null. This is important because it helps identify each row clearly. The execution steps show starting the table creation, adding columns, declaring the primary key, and finishing the table. The variable tracker shows how the table and primary key state change after each step. Common confusions include why the primary key must be unique and not null, and that only one primary key is allowed per table. The quiz questions help reinforce these points by referencing the execution steps and variable states.

Practice

1. What is the main purpose of a PRIMARY KEY in a MySQL table?

easy

A. To uniquely identify each row and prevent duplicates

B. To allow NULL values in a column

C. To speed up SELECT queries only

D. To store large text data

Solution

1. Step 1: Understand the role of PRIMARY KEY

   A PRIMARY KEY uniquely identifies each row in a table and ensures no duplicate or NULL values exist in that column.

2. Step 2: Compare options with PRIMARY KEY purpose

   Only To uniquely identify each row and prevent duplicates correctly states the unique identification and prevention of duplicates, which is the main purpose.

3. Final Answer:

   To uniquely identify each row and prevent duplicates -> Option A

4. Quick Check:

   Primary key = unique row ID [OK]

Hint: Primary key means unique and no duplicates [OK]

Common Mistakes:

• Thinking primary key allows NULL values
• Confusing primary key with indexing only
• Assuming primary key stores large text

2. Which of the following is the correct syntax to declare a primary key inline in a MySQL CREATE TABLE statement?

easy

A. CREATE TABLE users (id INT, name VARCHAR(50 PRIMARY KEY));

B. CREATE TABLE users (id INT, name VARCHAR(50), PRIMARY KEY id);

C. CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));

D. CREATE TABLE users (id INT, PRIMARY KEY, name VARCHAR(50));

Solution

1. Step 1: Review inline primary key syntax

   Inline primary key declaration is done by adding PRIMARY KEY right after the column type, like id INT PRIMARY KEY.

2. Step 2: Check each option

   CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); correctly places PRIMARY KEY inline after id INT. Others have syntax errors or misplaced keywords.

3. Final Answer:

   CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); -> Option C

4. Quick Check:

   Inline primary key = column type + PRIMARY KEY [OK]

Hint: Inline primary key goes right after column type [OK]

Common Mistakes:

• Placing PRIMARY KEY after column list without parentheses
• Using PRIMARY KEY without column name
• Putting PRIMARY KEY after another column

3. Given the table creation below, what will be the result of inserting two rows with the same id?

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);
INSERT INTO products (id, name) VALUES (1, 'Pen');
INSERT INTO products (id, name) VALUES (1, 'Pencil');

medium

A. Both rows inserted successfully

B. Error due to duplicate primary key value

C. Second row overwrites the first row

D. NULL inserted for the second row's id

Solution

1. Step 1: Understand primary key uniqueness

   The primary key column id must have unique values. Duplicate values cause errors.

2. Step 2: Analyze the insert statements

   The first insert adds id=1. The second insert tries to add the same id=1, violating uniqueness.

3. Final Answer:

   Error due to duplicate primary key value -> Option B

4. Quick Check:

   Duplicate primary key = error [OK]

Hint: Primary key duplicates cause insert errors [OK]

Common Mistakes:

• Assuming duplicate inserts overwrite existing rows
• Thinking NULL can be inserted in primary key
• Believing both rows insert without error

4. Identify the error in the following MySQL table creation statement:

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

medium

A. No error, statement is correct

B. Primary key cannot be declared separately

C. order_id must be VARCHAR type

D. Missing parentheses around the primary key column

Solution

1. Step 1: Check syntax for separate primary key declaration

   When declaring a primary key separately, the column name(s) must be inside parentheses, like PRIMARY KEY (order_id).

2. Step 2: Identify the error in the statement

   The statement uses PRIMARY KEY order_id without parentheses, causing a syntax error.

3. Final Answer:

   Missing parentheses around the primary key column -> Option D

4. Quick Check:

   Separate primary key needs parentheses [OK]

Hint: Separate primary key needs ( ) around columns [OK]

Common Mistakes:

• Omitting parentheses in separate primary key
• Thinking primary key must be inline only
• Assuming INT type is invalid for primary key

5. You want to create a table employees with a composite primary key consisting of department_id and employee_number. Which is the correct way to declare this primary key?

hard

A. CREATE TABLE employees (department_id INT, employee_number INT, PRIMARY KEY (department_id, employee_number));

B. CREATE TABLE employees (department_id INT PRIMARY KEY, employee_number INT PRIMARY KEY);

C. CREATE TABLE employees (department_id INT, employee_number INT PRIMARY KEY);

D. CREATE TABLE employees (department_id INT, employee_number INT, PRIMARY KEY department_id, employee_number);

Solution

1. Step 1: Understand composite primary key syntax

   A composite primary key is declared by listing multiple columns inside parentheses after PRIMARY KEY.

2. Step 2: Evaluate each option

   CREATE TABLE employees (department_id INT, employee_number INT, PRIMARY KEY (department_id, employee_number)); correctly declares both columns together as a composite primary key. CREATE TABLE employees (department_id INT PRIMARY KEY, employee_number INT PRIMARY KEY); tries to assign two primary keys separately, which is invalid. CREATE TABLE employees (department_id INT, employee_number INT PRIMARY KEY); declares only one column as primary key. CREATE TABLE employees (department_id INT, employee_number INT, PRIMARY KEY department_id, employee_number); has syntax errors missing parentheses.

3. Final Answer:

   CREATE TABLE employees (department_id INT, employee_number INT, PRIMARY KEY (department_id, employee_number)); -> Option A

4. Quick Check:

   Composite key = PRIMARY KEY (col1, col2) [OK]

Hint: Composite keys list columns inside parentheses [OK]

Common Mistakes:

• Declaring multiple primary keys separately
• Missing parentheses for composite keys
• Assigning primary key to only one column