Create a table in MySQL with a primary key

To create a table in MySQL (with a primary key), start by connecting to the MySQL server from PHPMyAdmin. Indeed phpMyAdmin (PMA) is the central interface to manage MySQL databases and tables.

MySQL is an Open Source database, which means that it is free software. To start, connect to the MySQL server and database from a web browser like Google Chrome or Firefox for example.

MySQL is a relational database management system (RDBMS) that is the most popular open source database system in the world. It can be used with any operating system.

The MySQL RDBMS provides many benefits, including high performance and reliability, which make it a popular choice for web-based applications.

Some of the features of MySQL include:

  • Relational Database Management System (RDBMS)
  • it is an Open Source software
  • Can be used with any operating system
  • Provides high performance and reliability
  • Many benefits include being web-based

Indeed, MySQL is an open-source DBMS that is popular among developers because it’s free and easy to use.

Create a MySQL table and add a primary key

If your development server is your machine, then the URL is: https://localhost/pma/. Where localhost can be replaced by your machine name. The default port for PMA is 3306.

To sum up you need two information’s:

  1. The server URL: https://localhost/pma/ where localhost is replaced by your servername
  2. The server port, by default it is 3306

Simply copy and paste the script into the phpMyAdmin interface for example.

This MySQL example table contains three columns:

  • The CLIENTID column which represents the client number of type auto incremented integer
  • A NAME column for the customer name of type varchar(20) with the UNIQUE attribute
  • A CITY column to store the customer’s city varchar(20)

At the end of the script, the ClientID column is explicitly defined a primary key.

Create a table, insert and select data with MySQL

A MySQL database is a collection of tables, which store data. The tables are created to store different types of data.

A MySQL table is a collection of data. It can be created by using the SQL CREATE TABLE statement.

This section defines what a MySQL table is and how to create one from scratch.

This MySQL tutorial also provides some examples of common queries and the syntax used to execute them.

The three steps in the code are :

  1. Create the table to store the data
  2. Insert some sample lines
  3. and select the content from the table.

MySQL code to add a table with a primary key

To start, test if the table exists in our database with the exists() function. If it does, then the table is deleted with the T-SQL drop table command. Then create the SALES table which simply contains three columns.

To execute the code and create a table in a MySQL database, go to the SQL tab, copy, and adapt the SQL code as needed.

The client ID column is defined as the primary key of the table.

-- Create the CUSTOMERS MySQL table with the column NAME declared as UNIQUE
-- The UNIQUE keyword defines the column with a unique value
CREATE TABLE Customers (
	CLIENTID int AUTO_INCREMENT,	
	NAME varchar(20) UNIQUE,	
	CITY varchar(20),
    PRIMARY KEY (CLIENTID)
);

-- Insert data for manipulation examples
INSERT INTO Customers (NAME, CITY) 
VALUES 	('MAMMADOU', 'Lyon'),
		('SERGEI', 'Lyon'),
		('CHRISTOPHE', 'Paris');

-- Check inserted rows
SELECT	*
FROM    Customers;

This short MySQL tutorial explains how to create a table with the Create Table command. And also, how to insert some rows with the SQL Insert Into command. Finally, how to display the contents of the table with a Select query.

To go further, here is an article to learn how to create a SQL Server table with a T-SQL script.

Be the first to comment

Leave a Reply

Your email address will not be published.


*