MariaDB Primary Key

In this MariaDB tutorial, we will study the use of MariaDB Primary Key, and we will also cover some examples. The lists of topic that comes under discussion are given below:

MariaDB Primary Key
Primary Key Auto_Increment in MariaDB
MariaDB Primary Key Index
Primary Key Constraint in MariaDB
MariaDB Primary Key Multiple Columns
MariaDB Primary Key Foreign Key
Primary Key Varchar example in MariaDB
MariaDB Primary Key Create Table
MariaDB Primary Key Vs Unique

Table of Contents

In MariaDB, a primary key is defined as one or more fields that uniquely identify a record in a table. A primary key column can’t contain a null value, and only one column can be a primary key in the table.

A primary key in MariaDB can be created by two methods: the CREATE TABLE statement and the ALTER statement.

The syntax to create a primary key in the table using the CREATE TABLE statement is given below:

CREATE TABLE table_name
( column1 column-definition,
  column2 column-definition,
  ...
  CONSTRAINT [constraint_name]
  PRIMARY KEY [using BTREE | HASH] (column1,column2,... column_n
);

The syntax explanation:

Table_name: The name of the table that we want to create.
column1, column2: The columns we want to create in the table, and by using CREATE TABLE syntax, as this is an over-simplification way to demonstrate a primary key.
constraint_name: The name of the primary key
column1, column2,. . . column_n: The column that makes the primary key.

The example of creating a primary key using MariaDB is given below:

CREATE TABLE airbnb(
id INT PRIMARY KEY,
PropertyName VARCHAR(50) UNIQUE NOT NULL,
host_name VARCHAR(20),
neighbourhood VARCHAR(50) NOT NULL,
latitude FLOAT NOT NULL UNIQUE,
longitude FLOAT NOT NULL UNIQUE,
room_type VARCHAR(20) NOT NULL,
Price FLOAT NOT NULL):   

DESC airbnb;

To describe the creation of the Airbnb table, the syntax is given below:

DESC AIRBNB;

As shown in the diagram above, the primary key in the Airbnb table is the ID column, which is identified using the query DESC AIRBNB command.

This is how to create a table with a primary key column in MariaDB.

Read MariaDB Delete Row

MariaDB Primary Key Auto_Increment

In MariaDB, the Auto_Increment column can be used to generate a unique identity for new rows every time. The columns start from the default value 1, and they can’t be started automatically from the null value or a lower value less than 1.

Each table has one auto_increment column. It must be defined as a key (not necessarily a primary key or a unique key)

The diagram of MariaDB Primary Key Auto_increment is given below:

CREATE TABLE USA_telebooth(
Customer_id INT PRIMARY KEY AUTO_INCREMENT,
Caller_Name BIGINT NOT NULL,
Caller_zipcode BIGINT NOT NULL);

DESC USA_telebooth;

After the creation of the table USA_telebooth, let’s describe the table by using the syntax:

DESC USA_telebooth;

After insertion of details in all columns in the table USA_telebooth by using the insert command:

INSERT INTO Table_Name values
(value1,value2,....);

The insertion of details in the table USA_telebooth by autoincrement is given below:

INSERT INTO USA_telebooth VALUES
(1,'A J Nady',54789);

SELECT * FROM USA_telebooth;

MariaDB Primary Key Auto_Increment insert

Read MariaDB Full Outer Join

MariaDB Primary Key Index

Well, there are three types of index in MariaDB:

Primary key (unique and not null)
Unique indexes (unique and can’t be null)
Plain indexes (not necessarily unique)

The terms ‘KEY’ and ‘INDEX’ are generally used interchangeably, and statements should work with either keyword.

PRIMARY KEY

In MariaDB, the primary key is a single field or a combination of fields that uniquely defines a record. No part of a primary key column contains NULL values. A table can have only a primary key.

The syntax of the primary key is given below:

CREATE TABLE table_name
( column1 column-definition,
  column2 column-definition,
  ...
  CONSTRAINT [constraint_name]
  primary key [using BTREE | HASH] (column1,column2,... column_n
);

2. UNIQUE INDEX

In MariaDB, a unique index must be unique, but it can be NULL. Therefore, each key-value identifies only one record, but each record needs to be represented.

For example, to create a unique key in the table, Airbnb is given below:

CREATE TABLE airbnb(
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
PropertyName VARCHAR(50) UNIQUE,
host_name VARCHAR(20) NOT NULL,
neighbourhood VARCHAR(50),
latitude FLOAT UNIQUE NOT NULL,
longitude FLOAT UNIQUE NOT NULL,
room_type VARCHAR(20) NOT NULL,
Price FLOAT);

DESC airbnb;

As you see in the description of the Airbnb table, the column names are PropertyName, latitude, and room_type. As you know, they can have a one-time null value in the column of PropertyName, latitude, and longitude.

3. PLAIN INDEX

In MariaDB, the plain index doesn’t need to be unique necessarily. The given example is below:

CREATE TABLE USA_numericname(
Alpha INT NOT NULL, 
Beta INT, 
INDEX (Alpha,Beta));

INSERT INTO USA_numericname values 
(1,1), 
(2,2), 
(2,2);

SELECT * FROM USA_numericname;

As we see in the above example, in the USA_numericname table, the values are not different. It contains the same value as (2,2) by 2 times for both alpha and beta columns of the table USA_numericname.

Read MariaDB Cast with Examples

MariaDB Primary Key Constraint

In MariaDB, the primary key supports the implementation of constraints in the table using the CREATE TABLE or ALTER TABLE statements. If you try to enter invalid data in the column, MariaDB throws an error.

The syntax of MariaDB Primary Key constraints is given below:

[constraint [symbol]] constraint_expression;

constraint_expression:
| PRIMARY KEY [index_type] (index_col_name1, ...) [index_option] ...
  | FOREIGN KEY [index_name] (index_col_name1, ...) 
       REFERENCES table_name (index_col_name1, ...)
       [ON DELETE reference_option]
       [ON UPDATE reference_option]
  | UNIQUE [INDEX|KEY] [index_name1]
       [index_type] (index_col_name1, ...) [index_option] ...
  | CHECK (check_constraints)

It provides restrictions on the data that you can add to the table. It will allow you to enforce data integrity from MariaDB, rather than through application errors. There are four types of constraints:

Constraints	Description
PRIMARY KEY	Need to set the column for referencing rows. Values must be unique but not be null.
FOREIGN KEY	Set the column to refer primary key on another table.
UNIQUE	Requires value in column/s in table occur once in the table.
CHECK	Check whether data meets the given conditions.

Types of Constraints

In this table, we will learn to add a primary key in an existing table using the ALTER command. The sample example is given below:

Create table Medicare(                                
agency name varchar(100)not null,                     
street_address varchar(100)not null,                  
city varchar(20) not null,                            
state varchar(20) not null,                           
zip_code int not null unique,                         
total_episode not null unique,                        
No_of_distinct_Users int not null,                    
total_health_agency_charges int not null,        
total_health_agency_charges_Medicare int not NULL;
                                                      
DESC Medicare;                                        
                                                      
ALTER TABLE Medicare ADD Provider_ID INT PRIMARY KEY ;

As we see in the above diagram, we used the ALTER command to add the primary constraint in the table by the field name, Provider_ID. Just suppose you want to add the Provider_ID column in the table, but at the beginning/ starting part of the table, then try to use the first|after clause by column_name in the ALTER command.

ALTER TABLE TABLE_NAME set column_name constraint [first|after column_name];

By the alter command, a sample example is given below:

ALTER TABLE Medicore set Provider_ID int(10) PRIMARY KEY [FIRST Agency_Name];

Read MariaDB Substring

MariaDB Primary Key Multiple Columns

In MariaDB, let us create two tables with primary keys: products and categories. The creation of table products is given below:

CREATE TABLE if NOT EXISTS products(
product_code VARCHAR(10) PRIMARY KEY,
product_NAME VARCHAR(20) NOT NULL
);

DESC PRODUCTS;

After the insert statement and the select statement, we will write the code for the table Products. The insert command and image are below:

INSERT INTO products values('xg-2019','xgadget of 2019');

SELECT * FROM products;

Let’s create a new table called Categories whose primary key is the auto_increment column and the description of the column parts is given below:

CREATE TABLE categories(
category_id int auto_increment primary key,
name varchar(20)
);

DESC categories;

INSERT INTO categories values
(1,'Gadgets'),
(2,'Accessories');

SELECT * FROM categories;

The following statement creates a table whose primary key consists of two columns:

CREATE TABLE Walmart(
product_code varchar(10),
category_id int,
primary key(product_code,category_id)
);

In MariaDB, the Walmart table stores the relationship between the products table and the categories table. Each product belongs to one or more categories, and conversely, categories belong to one or more products. This is called a many-to-many relationship.

Read MariaDB LIMIT

MariaDB Primary Key Foreign Key

In MariaDB, a foreign key is a column or set of columns in a table that refers to a column or set of columns of another table. A table that has a foreign key is called a child table, and the table that references the foreign key is called a parent table.

Typically, a foreign key column in the child table is preferred over a foreign key to a parent table, which is called a primary key.

The syntax of defining a foreign key constraint:

CREATE TABLE table_name(
 column_name column_type,
 ....,
);

The creation of the parent table foreign key table Airbnb:

CREATE TABLE airbnb(
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
PropertyName VARCHAR(50) UNIQUE,
host_name VARCHAR(20) NOT NULL,
neighbourhood VARCHAR(50),
latitude FLOAT UNIQUE NOT NULL,
longitude FLOAT UNIQUE NOT NULL,
room_type VARCHAR(20) NOT NULL,
Price FLOAT);

DESC airbnb;

The creation of the foreign key child table Airbnb_cust:

CREATE TABLE airbnb_cust(
cust_id INT PRIMARY KEY,
Cust_Name VARCHAR(20) NOT NULL,
Days_to_Stay INT NOT NULL,
Property_id INT 
FOREIGN KEY (Property_id)
REFERENCES airbnb(id)
);

DESC airbnb_cust;

As you see in the parent table Airbnb, the ID column is the primary key, wherein the child table Airbnb_cust, Property_id is also the primary key of the Airbnb table. The connection of Property_id makes the ID column a parent-child table. Remember, the child table is always a foreign key to the parent table’s primary key.

Read MariaDB index with Examples

MariaDB Primary Key Varchar

In MariaDB, Varchar is a data type syntax with a maximum size of 255 characters, allowing for the storage of several characters. It is a variable-length string.

The syntax of MariaDB primary key varchar is given below:

[NATIONAL] VARCHAR(N) [character set char_name] [COLLATE collation_name]

Creation of a column by using VARCHAR in the table Airbnb is given below:

CREATE TABLE airbnb(
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
PropertyName VARCHAR(50) UNIQUE,
host_name VARCHAR(20) NOT NULL,
neighbourhood VARCHAR(50),
latitude FLOAT UNIQUE NOT NULL,
longitude FLOAT UNIQUE NOT NULL,
room_type VARCHAR(20) NOT NULL,
Price FLOAT);

DESC airbnb;

Read MariaDB varchar

MariaDB Primary Key Create Table

In this section, we will learn how to create tables. Before creating a table, we will learn how to determine its name, field name, and constraints. The general syntax to CREATE TABLE is given below:

CREATE TABLE TABLE_NAME(column_name column_type constraint);

Use the primary key to create a primary key column in the table. It is a special type of unique key. There can be more than one type of primary key in the table, and it can’t be null.

Review a command to learn how to create a table USA_AIRBNB:

CREATE TABLE airbnb(
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
PropertyName VARCHAR(50) UNIQUE,
host_name VARCHAR(20) NOT NULL,
neighbourhood VARCHAR(50),
latitude FLOAT UNIQUE NOT NULL,
longitude FLOAT UNIQUE NOT NULL,
room_type VARCHAR(20) NOT NULL,
Price FLOAT);

DESC airbnb;

The above example uses a not null attribute to avoid errors caused by the null value. The auto_increment attribute instructs MariaDB to add the next available value to the ID field. Multiple columns separated by commas can define a primary key.

Read MariaDB LIKE Operator

MariaDB Primary Key Vs Unique

PRIMARY KEY: It is a column of the table that uniquely identifies each row in the table. Only the primary key is permitted to be used once in the table. It doesn’t accept any duplicate values or Null values in the column of the primary key.

The primary key is used infrequently, so it is selected with care where changes can occur rarely. A primary key of one table can refer to the foreign key of any other table.

To make the primary key understandable, we took a table named CATEGORIES having attributes such as category_id and Name.

The category_id cannot be identical to or a NULL value, as each category_id corresponds to a unique product code name purchased by the user. In reality, two users can’t have the same category_id on the product.

UNIQUE KEY: It identifies an individual row uniquely within a table. It can have more than one unique key, unlike the primary key, and it can accept one NULL value in a column. it is also referenced as the foreign key of another table.

For a better understanding of unique keys, we take the categories table with category_id and name attributes.

The Category_id column is allocated with a primary key, and the Name column can have unique constraints, where each entry in the Name column should be unique, because each name of the product has the name of the brand and its company.

If the category name in the Name column is given as Null, it means that the category has no name for the product.

Key Difference between Primary Key and Unique

The Primary key can’t have NULL values, whereas the Unique key can have a NULL value.

A table can have a primary key column, and there can be multiple unique keys on a table.
A clustered index is automatically created where a primary key is defined, whereas a unique key generates a non-clustered index.