Search⌘ K
Home
Courses
An Introductory Guide to SQL

... continue

Explore how to apply restrictions in MySQL tables to enhance data integrity. Understand primary keys, the use of AUTO_INCREMENT for unique IDs, and how to enforce NOT NULL and default values to manage data input effectively.

We'll cover the following...

In the previous lesson we worked with a very simple create table statement. In this lesson we’ll gradually apply more restrictions on what our table can accept.

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/4lesson.sh and wait for the mysql prompt to start-up.

MySQL
-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 



-- Query 1

CREATE TABLE Actors (

Id INT AUTO_INCREMENT,

FirstName VARCHAR(20) NOT NULL,

SecondName VARCHAR(20) NOT NULL,

DoB DATE NOT NULL,

Gender ENUM('Male','Female','Other') NOT NULL,

MaritalStatus ENUM('Married', 'Divorced', 'Single', 'Unknown') DEFAULT "Unknown",

NetWorthInMillions DECIMAL NOT NULL,

PRIMARY KEY (Id));



-- Query 2

CREATE TABLE IF NOT EXISTS Actors (

Id INT AUTO_INCREMENT,

FirstName VARCHAR(20) NOT NULL,

SecondName VARCHAR(20) NOT NULL,

DoB DATE NOT NULL,

Gender ENUM('Male','Female','Other') NOT NULL,

MaritalStatus ENUM('Married', 'Divorced', 'Single', 'Unknown') DEFAULT "Unknown",

NetWorthInMillions DECIMAL NOT NULL,

PRIMARY KEY (Id));
Terminal 1
Terminal
Loading...

  1. We’ll want to restrict a user from adding duplicate rows.

    In a relational database we can uniquely identify a row by designating a single column or a set of columns as the primary key.

    We discuss the concept of a primary key in depth in a later lesson but for now, it suffices to know that a column or multiple columns acting as the primary key serve to uniquely identify each row in a table. In the case of our Actors table, we may be tempted to choose the combination of the ...