fastread homefastrread library fastread menu

MariaDB : Unique Constraints

Tutorial by:      Date: 2016-04-18 05:41:55

❰ Previous Next ❱

This MariaDB tutorial explains how to create, add, and drop unique constraints in MariaDB with syntax and examples.

What is a unique constraint in MariaDB?

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

What is the difference between a unique constraint and a primary key?

Primary Key

Unique Constraint

None of the fields that are part of the primary key can contain a null value.

Some of the fields that are part of the unique constraint can contain null values as long as the combination of values is unique.

Create unique Contraint - Using a CREATE TABLE statement

The syntax for creating a unique constraint using a CREATE TABLE statement in MariaDB is:

CREATE TABLE table_name

(

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  ...

 

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)

);

table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table.

constraint_name

The name of the unique constraint.

uc_col1, uc_col2, ... uc_col_n

The columns that make up the unique constraint.

Example

Let's look at an example of how to create a unique constraint in MariaDB using the CREATE TABLE statement.

CREATE TABLE websites

( website_id INT(11) PRIMARY KEY AUTO_INCREMENT,

  website_name VARCHAR(25) NOT NULL,

  server_name VARCHAR(20),

  creation_date DATE,

  CONSTRAINT websites_unique UNIQUE (website_name)
);

In this example, we've created a unique constraint on the websites table called websites_unique. It consists of only one field - the website_name field.

We could also create a unique constraint with more than one field as in the example below:

CREATE TABLE websites

( website_id INT(11) PRIMARY KEY AUTO_INCREMENT,

  website_name VARCHAR(25) NOT NULL,

  server_name VARCHAR(20),

  creation_date DATE,
  CONSTRAINT websites_server_unique UNIQUE (website_name, server_name)
);

Create unique contraint - Using an ALTER TABLE statement

The syntax for creating a unique constraint using an ALTER TABLE statement in MariaDB is:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

table_name

The name of the table to modify. This is the table that you wish to add a unique constraint to.

constraint_name

The name of the unique constraint.

column1, column2, ... column_n

The columns that make up the unique constraint.

Example

Let's look at an example of how to add a unique constraint to an existing table in MariaDB using the ALTER TABLE statement.

ALTER TABLE websites

ADD CONSTRAINT websites_unique UNIQUE (website_name);

In this example, we've created a unique constraint on the existing websites table called websites_unique. It consists of the field called website_name.

We could also create a unique constraint with more than one field as in the example below:

ALTER TABLE websites

ADD CONSTRAINT website_server_unique UNIQUE (website_name, server_name);

Drop Unique Constraint

The syntax for dropping a unique constraint in MariaDB is:

ALTER TABLE table_name

DROP INDEX constraint_name;

table_name

The name of the table to modify. This is the table that you wish to remove the unique constraint from.

constraint_name

The name of the unique constraint to remove.

Example

Let's look at an example of how to remove a unique constraint from a table in MariaDB.

ALTER TABLE websites

DROP INDEX websites_unique;

In this example, we're dropping a unique constraint on the websites table called websites_unique.

 

❰ Previous Next ❱


MariaDB

Submit Your Thought, Tutorial, Articls etc.

Submit Your Information India's Number one online promotion website