How to Create a SQL Table with Primary Key

A SQL (Structural Query Language) table is a structured set of data organized in rows and columns, SQL is the main storage unit in a relational database management system (RDBMS). The Columns can also be referred to as fields or entity while the Rows can also be called records or value in a table.

To create a SQL table, you must first create a database in your Database Management System (DBMS) like MySQL

create database databasename(name of the database) 
Use databasename

Let’s create a simple SQL table called ‘books_2’

CREATE TABLE books_2 
	(
		book_id INT NOT NULL AUTO_INCREMENT,
		title VARCHAR(100),
                Author_Name VARCHAR(100),
		pages INT,
		PRIMARY KEY(book_id)
	);

the name of the table is called ‘books’. INT, VARCHAR are data types representing integer and variable character respectively, (100) is the length of character allowed. NOT NULL means that the value cannot be empty. AUTO INCREMENT means the value automatically increases as new input is made.

Let’s add Rows which the vlaues or records to our table columns

INSERT INTO books_2 (title, Author_Name, pages)
VALUES
('The Namesake', 'Ediko', 32),
('48 Laws of Power', 'Robert Grenne', 400),
('100 Ways to Make it Big', 'Edward', 125),
('Back-end Engineering', 'Imtiaz', 345);

To READ the details of the table use SELECT * FROM books_2;

Here’s the result below using MySQL DBMS

SQL PRIMARY KEY

A primary key is a column (or a set of columns) that identifies each row in a table in a unique way. A primary key make sure that every row is unique by ensuring that no two rows have the same primary key value, and it cannot contain NULL values (NOT NULL). In the ablove table books_2 the primary key is the book_id column which cannot be null (NOT NULL).

Primary key can be written in either two way

CREATE TABLE books_2
(
book_id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(100),
Author_Name VARCHAR(100),
pages INT,
PRIMARY KEY(book_id)
);

OR

CREATE TABLE books_2
(
book_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100),
Author_Name VARCHAR(100),
pages INT
);