SQL Constraints: PRIMARY KEY and NOT NULL

Definition: Constraints are rules applied to columns in a table. They are used to limit the type of data that can go into a table, ensuring the accuracy and reliability of the information within the database.

Why: In core table-definition concepts, constraints prevent "bad data" from entering your system. Without rules like PRIMARY KEY and NOT NULL, a database could end up with duplicate records or missing vital information, making it difficult to use for real-world applications.

Example: Implementing Constraints

When creating a table, you define constraints immediately after the datatype for each column:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT
);

Detailed Explanation

• PRIMARY KEY: This is a unique identifier for each record. It ensures that no two students can have the same id. A table can have only one primary key, and it cannot contain NULL values.
• NOT NULL: This constraint forces a column to always contain a value. In the example above, the database will reject any attempt to save a student record if the name field is left empty.

Common Constraints Table

Key Notes

• Identification: Think of the PRIMARY KEY as a student's Roll Number or a citizen's ID—it is the one thing that guarantees you are looking at the right person.
• Data Integrity: Constraints are your first line of defense. It is much better for the database to show an error message when someone forgets a name than to have a database full of "anonymous" students.
• Naming: While the database handles them internally, you can also give your constraints custom names to make error messages easier to understand for developers.