Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Unique Index

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Unique Index

      MySQL Indexes are used to return the data from the database real quick. The users cannot see the indexes performing, instead they are just used to speed up the queries.
      However a unique index, in addition to speeding up data retrieval queries, is also used to maintain data integrity in a table. When a unique index is defined on a table column, we cannot add any duplicate values into that column.

      MySQL Unique Index

      A unique index can be created on one or more columns of a table using the CREATE UNIQUE INDEX statement in MySQL.
      • If we are creating unique index on only a single column, all the rows in that column must be unique.
      • We cannot create a unique index where NULL values are present in multiple rows in a single column.
      • If we are creating unique index on multiple columns, the combination of rows in those columns must be unique.
      • We cannot create a unique index on multiple columns if the combination of columns contains NULL values in more than one row.

      Syntax

      Following is the syntax for creating a unique index in MySQL 
      CREATE UNIQUE INDEX index_name
      ON table_name (column1, column2, ..., columnN);

      Example

      Let us first create a table named CUSTOMERS using the following query 
      CREATE TABLE CUSTOMERS (
         ID INT NOT NULL,
         NAME VARCHAR(15) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS VARCHAR(25),
         SALARY DECIMAL(10, 2),
         PRIMARY KEY(ID)
      );
      In the following query, we are inserting some values in to the above created table using the INSERT statement 
      INSERT INTO CUSTOMERS VALUES 
      (1, 'Ramesh', '32', 'Ahmedabad', 2000),
      (2, 'Khilan', '25', 'Delhi', 1500),
      (3, 'Kaushik', '23', 'Kota', 2500),
      (4, 'Chaitali', '26', 'Mumbai', 6500),
      (5, 'Hardik','27', 'Bhopal', 8500),
      (6, 'Komal', '22', 'MP', 9000),
      (7, 'Muffy', '24', 'Indore', 5500);
      The table will be created as follows
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      6
      Komal
      22
      Hyderabad
      4500.00
      7
      Muffy
      24
      Indore
      10000.00
      Now, create a unique index for the column named SALARY in the CUSTOMERS table using the following query 
      CREATE UNIQUE INDEX unique_ind ON CUSTOMERS (SALARY);

      Inserting Duplicate Values

      Now, let us try to update the value in the SALARY column with a duplicate (already existing data) value using the following query 
      UPDATE CUSTOMERS SET SALARY = 2000 WHERE ID = 2;

      Error

      The above query results in an error because a column that has unique index cannot contain duplicate values in it.
      ERROR 1062 (23000): Duplicate entry '2000.00' for key 'customers.unique_ind'

      Creating Unique Index on Multiple Columns

      In MySQL, we can also create a unique index on multiple columns of a table using the CREATE UNIQUE INDEX statement. To do so, you just need to pass the name of the columns (you need to create the index on) to the query.

      Example

      Assume the previously created CUSTOMERS table and create a unique index on the columns named NAME and AGE using the following query 
      CREATE UNIQUE INDEX mul_unique_index ON CUSTOMERS(NAME, AGE);

      Verification

      Using the following query, we can list all the indexes that are created on the CUSTOMERS table 
      SHOW INDEX FROM CUSTOMERS\G
      The table of index information is displayed as 
      *************************** 1. row ***********************
              Table: customers
         Non_unique: 0
           Key_name: PRIMARY
       Seq_in_index: 1
        Column_name: ID
          Collation: A
        Cardinality: 7
           Sub_part: NULL
             Packed: NULL
               Null:
         Index_type: BTREE
            Comment:
      Index_comment:
            Visible: YES
         Expression: NULL
      *************************** 2. row ***********************
              Table: customers
         Non_unique: 0
           Key_name: mul_unique_index
       Seq_in_index: 1
        Column_name: NAME
          Collation: A
        Cardinality: 7
           Sub_part: NULL
             Packed: NULL
               Null:
         Index_type: BTREE
            Comment:
      Index_comment:
            Visible: YES
         Expression: NULL
      *************************** 3. row ***********************
              Table: customers
         Non_unique: 0
           Key_name: mul_unique_index
       Seq_in_index: 2
        Column_name: AGE
          Collation: A
        Cardinality: 7
           Sub_part: NULL
             Packed: NULL
               Null:
         Index_type: BTREE
            Comment:
      Index_comment:
            Visible: YES
         Expression: NULL
      *************************** 4. row ***********************
              Table: customers
         Non_unique: 0
           Key_name: unique_ind
       Seq_in_index: 1
        Column_name: SALARY
          Collation: A
        Cardinality: 7
           Sub_part: NULL
             Packed: NULL
               Null: YES
         Index_type: BTREE
            Comment:
      Index_comment:
            Visible: YES
         Expression: NULL

      Creating Unique Index Using a Client Program

      In addition to creating an index using a MySQL query, we can also create the unique index using a client program.

      Syntax

      PHPNodeJSJavaPython
      To create an unique index into MySQL table through a PHP program, we need to execute the CREATE UNIQUE INDEX statement using the query() function of mysqli as follows 
      $sql = "CREATE UNIQUE INDEX uidx_tid ON tutorials_table (tutorial_id)";
      $mysqli->query($sql);

      Example

      Following are the programs
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      // printf('Connected successfully.');
      
      // UNIQUE INDEX
      $sql = "CREATE UNIQUE INDEX uidx_tid ON tutorials_table (tutorial_id)";
      if ($mysqli->query($sql)) {
          printf("Unique Index created successfully!.");
      }
      if ($mysqli->errno) {
          printf("Index could not be created!.", $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      Unique Index created successfully!.