MariaDB Primary Key With Examples

In this MariaDB tutorial, we will study the use of MariaDB Primary Key and we will also cover some examples. The lists of the 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

MariaDB Primary Key

In MariaDB, a primary key in MariaDB can be defined as one or more fields that help to uniquely define 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: CREATE TABLE statement and ALTER statement.

The syntax to create a primary key in the table using 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 tavle that we want to create.
• column1, column2: The column we want to create in the table and by using CREATE TABLE syntax as this is an over-simplication 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 table Airbnb, the syntax is given below:

DESC AIRBNB;

As we see in the above diagram, the primary key in table Airbnb is the ID column and it is done by 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 default value 1 and it can’t be started automatically from the null value or a lower value less than 1.

Each table has one auto_increment column in the table. It must be defined as key (not necessarily primary key or 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 table USA_telebooth, let describe the table by using the syntax:

DESC USA_telebooth;

After insertion of details in all columns in table USA_telebooth by using 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;

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 term ‘KEY’ and ‘INDEX’ are generally used interchangeably and statements should work with either keyword.

1. PRIMARY KEY

In MariaDB, the primary key is a single or combination of fields that uniquely defines a record. No part in 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 one each record need 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 table Airbnb, the column_name as PropertyName, latitude, 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 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 CREATE TABLE or ALTER TABLE statement. If you try to enter the 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 throw data integrity from MariaDB, rather than through application error. 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.

In this table, we will learn to add a primary key in an existing table using 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 had used ALTER command to add the primary constraint in the table by the field name as 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 tries 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 alter command, 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 of the primary keys in MariaDB: 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 is 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 column auto_increment column and description of 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 to create 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 table products and categories. Each product belongs to one or more categories and on the other hand, categories belong to one or more products. This is called as many to many relationships.

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 prefers the foreign key is called a parent table.

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

The syntax of defining a foreign key constraint:

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

The creation of 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 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 is 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 primary key.

Read MariaDB index with Examples

MariaDB Primary Key Varchar

In MariaDB, Varchar is data type syntax with a maximum size of 255 characters where the size is several characters to store. 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 column by using VARCHAR in 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 used 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 instincts MariaDB to add the next available value to the ID field. Multiple columns are 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 use once in the table. It doesn’t accept any duplicate value or Null value 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, Name.

The category_id can never be identical and NULL value because every category_id has a unique code name of the product which is 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 with or 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.

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 as Name column is given Null which means it can have no name of the category of 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 in the table and there can be multiple unique key on a table.
• A clustered index automatically created where a primary key is defined whereas unique key generated non-clustered index.

Related MariaDB tutorials:

• MariaDB query examples
• How to create a database from an SQL file in MariaDB
• MariaDB Insert Into
• MariaDB create procedure
• How to Change Column in MariaDB

In this MariaDB tutorial, we learned the blow things:

1. MariaDB Primary Key
2. MariaDB Primary Key Auto_Increment
3. MariaDB Primary Key Index
4. MariaDB Primary Key Constraint
5. MariaDB Primary Key Multiple Columns
6. MariaDB Primary Key Foreign Key
7. MariaDB Primary Key Varchar
8. MariaDB Primary Key Create Table
9. MariaDB Primary Key Vs Unique