Bird
Raised Fist0
SQLquery~5 mins

PRIMARY KEY constraint in SQL

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Introduction
A PRIMARY KEY constraint makes sure each row in a table is unique and easy to find. It helps keep data organized and prevents duplicates.
When you want to identify each record in a table uniquely, like each student in a class.
When you need to link data between tables, like orders linked to customers.
When you want to prevent duplicate entries in a table.
When you want to quickly search for a specific row using a unique ID.
When you want to enforce data integrity by making sure no two rows have the same key.
Syntax
SQL
CREATE TABLE table_name (
  column_name datatype PRIMARY KEY,
  other_column datatype
);
A PRIMARY KEY column cannot have NULL values.
You can only have one PRIMARY KEY per table, but it can be made of multiple columns (composite key).
Examples
Creates a Students table where StudentID uniquely identifies each student.
SQL
CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(100)
);
Creates a table with a composite primary key using OrderID and ProductID together.
SQL
CREATE TABLE Orders (
  OrderID INT,
  ProductID INT,
  PRIMARY KEY (OrderID, ProductID)
);
Defines EmployeeID as primary key and ensures Email is unique but not primary.
SQL
CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  Email VARCHAR(255) UNIQUE
);
Sample Program
Creates a Books table with BookID as the primary key. Inserts two books successfully. The third insert fails because BookID 1 is already used, showing how PRIMARY KEY prevents duplicates.
SQL
CREATE TABLE Books (
  BookID INT PRIMARY KEY,
  Title VARCHAR(100),
  Author VARCHAR(100)
);

INSERT INTO Books (BookID, Title, Author) VALUES (1, '1984', 'George Orwell');
INSERT INTO Books (BookID, Title, Author) VALUES (2, 'To Kill a Mockingbird', 'Harper Lee');

-- This will fail because BookID 1 already exists
INSERT INTO Books (BookID, Title, Author) VALUES (1, 'Brave New World', 'Aldous Huxley');

SELECT * FROM Books;
OutputSuccess
Important Notes
PRIMARY KEY columns automatically create a unique index to speed up searches.
Trying to insert a duplicate primary key value causes an error and stops the insert.
Use PRIMARY KEY when you need a unique identifier; use UNIQUE constraint if duplicates are allowed but uniqueness is needed for some columns.
Summary
PRIMARY KEY ensures each row is unique and not null.
Only one PRIMARY KEY per table, but it can include multiple columns.
It helps keep data clean and easy to find.

Practice

(1/5)
1. What is the main purpose of a PRIMARY KEY in a database table?
easy
A. To speed up database backups
B. To uniquely identify each row and prevent null values
C. To store large text data
D. To allow duplicate values in a column

Solution

  1. Step 1: Understand the role of PRIMARY KEY

    A PRIMARY KEY uniquely identifies each row and does not allow nulls, ensuring data integrity.

  2. Step 2: Compare options with PRIMARY KEY purpose

    Options A, B, and C do not describe uniqueness or null restriction, which are key features of PRIMARY KEY.

  3. Final Answer:

    To uniquely identify each row and prevent null values -> Option B

  4. Quick Check:

    PRIMARY KEY = unique + not null [OK]
Hint: PRIMARY KEY means unique and no nulls [OK]
Common Mistakes:
  • Thinking PRIMARY KEY allows duplicates
  • Confusing PRIMARY KEY with foreign key
  • Assuming PRIMARY KEY stores large text
2. Which of the following is the correct syntax to define a PRIMARY KEY on the id column during table creation?
easy
A. CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));
B. CREATE TABLE users (id INT, name VARCHAR(50) PRIMARY KEY);
C. CREATE TABLE users (id INT, PRIMARY KEY name);
D. CREATE TABLE users (id INT, name VARCHAR(50), PRIMARY KEY id);

Solution

  1. Step 1: Review correct PRIMARY KEY syntax inline

    PRIMARY KEY is declared after the column type, like id INT PRIMARY KEY.

  2. Step 2: Check each option's syntax

    CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); correctly places PRIMARY KEY after id INT. Options B, C, and D have syntax errors or misplaced keywords.

  3. Final Answer:

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

  4. Quick Check:

    PRIMARY KEY follows column type inline [OK]
Hint: PRIMARY KEY goes right after column type [OK]
Common Mistakes:
  • Placing PRIMARY KEY after wrong column
  • Using PRIMARY KEY without parentheses for multiple columns
  • Syntax errors with misplaced keywords
3. Given the table creation:
CREATE TABLE orders (
  order_id INT,
  product_id INT,
  PRIMARY KEY (order_id, product_id)
);

What will happen if you try to insert two rows with the same order_id but different product_id?
medium
A. Only the second row will be inserted
B. Only the first row will be inserted; second causes error
C. Both rows cause a syntax error
D. Both rows will be inserted successfully

Solution

  1. Step 1: Understand composite PRIMARY KEY behavior

    The PRIMARY KEY is on both order_id and product_id together, so uniqueness is checked on the pair.

  2. Step 2: Analyze insertion of rows with same order_id but different product_id

    Since product_id differs, the pair is unique, so both rows can be inserted without error.

  3. Final Answer:

    Both rows will be inserted successfully -> Option D

  4. Quick Check:

    Composite PRIMARY KEY = unique pairs [OK]
Hint: Composite key means unique pairs, not single columns [OK]
Common Mistakes:
  • Assuming order_id alone must be unique
  • Thinking composite key allows duplicates in all columns
  • Confusing syntax error with constraint violation
4. Consider this table creation:
CREATE TABLE employees (
  emp_id INT,
  email VARCHAR(100),
  PRIMARY KEY emp_id
);

What is wrong with this statement?
medium
A. PRIMARY KEY must be declared with parentheses around column name
B. PRIMARY KEY cannot be on emp_id column
C. email column must be PRIMARY KEY instead
D. Table cannot have a PRIMARY KEY

Solution

  1. Step 1: Check syntax for table-level PRIMARY KEY

    When declaring PRIMARY KEY separately, column names must be inside parentheses, like PRIMARY KEY (emp_id).

  2. Step 2: Identify error in given statement

    The statement misses parentheses around emp_id, causing syntax error.

  3. Final Answer:

    PRIMARY KEY must be declared with parentheses around column name -> Option A

  4. Quick Check:

    Table-level PRIMARY KEY needs parentheses [OK]
Hint: Table-level PRIMARY KEY needs parentheses [OK]
Common Mistakes:
  • Omitting parentheses in table-level PRIMARY KEY
  • Thinking PRIMARY KEY can't be on emp_id
  • Confusing column-level and table-level syntax
5. You want to create a table students where each student is uniquely identified by a combination of student_id and campus_id. Which of the following statements correctly enforces this using a PRIMARY KEY?
hard
A. CREATE TABLE students (student_id INT PRIMARY KEY, campus_id INT PRIMARY KEY);
B. CREATE TABLE students (student_id INT, campus_id INT, PRIMARY KEY student_id, campus_id);
C. CREATE TABLE students (student_id INT, campus_id INT, PRIMARY KEY (student_id, campus_id));
D. CREATE TABLE students (student_id INT, campus_id INT, UNIQUE (student_id, campus_id));

Solution

  1. Step 1: Understand composite PRIMARY KEY requirement

    To uniquely identify rows by two columns, a composite PRIMARY KEY on both columns is needed.

  2. Step 2: Evaluate each option's correctness

    CREATE TABLE students (student_id INT, campus_id INT, PRIMARY KEY (student_id, campus_id)); correctly defines a composite PRIMARY KEY. CREATE TABLE students (student_id INT PRIMARY KEY, campus_id INT PRIMARY KEY); tries to assign two PRIMARY KEYs, which is invalid. CREATE TABLE students (student_id INT, campus_id INT, PRIMARY KEY student_id, campus_id); has syntax errors. CREATE TABLE students (student_id INT, campus_id INT, UNIQUE (student_id, campus_id)); uses UNIQUE but not PRIMARY KEY.

  3. Final Answer:

    CREATE TABLE students (student_id INT, campus_id INT, PRIMARY KEY (student_id, campus_id)); -> Option C

  4. Quick Check:

    Composite PRIMARY KEY uses parentheses around columns [OK]
Hint: Use parentheses for composite PRIMARY KEY columns [OK]
Common Mistakes:
  • Trying multiple PRIMARY KEYs on separate columns
  • Missing parentheses in PRIMARY KEY declaration
  • Using UNIQUE instead of PRIMARY KEY for uniqueness
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard