LU06c - SQL-DDL: Constraint Management - Under Construction

Source:

• W3Schools | Constraints | Overview
• W3Schools | Constraints | PRIMARY KEY
• W3Schools | Constraints | FOREIGN KEY
• W3Schools | Constraints | NOT NULL
• W3Schools | Constraints | UNIQUE

1. Discuss what database Constraints are and for they are needed
2. Explain the four most important CONSTRAINTS in database systems
3. Apply constraints to entity and realation tables in databases

Sources:

• Youtube-DE | Übersicht Constraints
• Youtube-EN | CONSTRAINT Tutorial

MySQL constraints ensure data integrity, enforcing rules at the database level. Constraints restrict the type of data that can be inserted into tables, preventing invalid entries and ensuring relationships between tables remain accurate. The most common constraints in MySQL are

• Primary Key
• Foreign Key
• NOT NULL
• AUTO_INCREMENT
• UNIQUE

Let’s explore these constraints with their syntax and practical examples.

The Primary Key constraint uniquely identifies each record in a table. A primary key column (or a set of columns) must contain unique, non-null values. Each table can have only one primary key.

General Syntax

CREATE TABLE table_name (
  column_name1 datatype PRIMARY KEY,
  column_name2 datatype
);

Example In the follwoing example, customer_id is the primary key, ensuring that every customer has a unique ID. It prevents any duplication of the customer_id.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);

Volkan Demir