Page 92 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

EXERCISE 24 : Enforcing Constraints, Primary Key and
Foreign Key

Objectives

At the end of this exercise you shall be able to
• connect the MySQL database server
• design the table
• create primary & foreign key

Requirements

Tools/Materials
• Desktop/Laptop with latest configuration
• Operating system: window 10:11
• XAMPP server r3.3.0

Procedure

TASK 1 : Access MySQL:
MySQL Workbench: Download and install the user-friendly GUI for database management.

Command Prompt: Open the Command Prompt and navigate to the MySQL installation directory (e.g., C:\Program
Files\MySQL\MySQL Server 8.0\bin).
Connect to your Database:

MySQL Workbench: Enter your server hostname, username, password, and database name to connect.
Command Prompt: Use the mysql command followed by your credentials (e.g., mysql -h localhost -u root -p).

TASK 2 : Design Your Tables:
1 Primary Key: Identify the unique identifier for each table. This column should not allow null values and should
have a unique constraint applied:
product id INT PRIMARY KEY AUTO INCREMENT,
name VARCHAR(255) NOT NULL UNIQUE,
price DECIMAL(8,2) NOT NULL

);
Establish Relationships:
2 Foreign Key: Define references between related tables. The referencing column must match the data type of
the referenced column:
CREATE TABLE orders (
order id INT PRIMARY KEY AUTO INCREMENT,
customer id INT NOT NULL,
product id INT NOT NULL,
FOREIGN KEY (customer id) REFERENCES customers(customer id),

FOREIGN KEY (product id) REFERENCES products(product id)
ON DELETE SET NULL
);

77
CITS : IT & ITES - Computer Software Application - Exercise 23