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
      Composite Key

      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.

       Composite Key

      A MySQL Composite Key is a key that consists of two or more columns in a table, used to uniquely identify a record (combination of values in the same table row). It can also be described as a Primary Key created on multiple columns.
      With composite key on multiple columns of a table, a combination of these columns guarantees uniqueness, even though individually these columns may or may not guarantee uniqueness. Therefore, when the database table doesn't have any column which is individually capable of identifying a unique row (or a record) from the table, then we might need two or more two fields/columns to get a unique record/row from the table.

      Creating MySQL Composite Key

      To create a composite key in a MySQL table, we create a primary key on two or more columns of a table using the PRIMARY KEY keyword in the CREATE TABLE statement. The composite key must have the following features −
      • A Composite Key may or may not be a part of the Foreign key.
      • A Composite Key can not be NULL.
      • A Composite Key also can be created by combining more than one Candidate Key.
      • It is also known as Compound key.
      • All the attributes in a compound keys are foreign keys.

      Syntax

      Following is the syntax to create a Composite Key while creating a table −
      CREATE TABLE table_name(
         column1 datatype, column2 datatype, column3 datatype..., 
         CONSTRAINT composite_key_name 
         PRIMARY KEY(column_name1, column_name2,..)
      );
      

      Example

      In the following example, we are trying to create a table named CUSTOMERS and add a composite key on ID and NAME columns as shown −
      CREATE TABLE CUSTOMERS (
         ID INT NOT NULL,
         NAME VARCHAR (20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),
         PRIMARY KEY(ID, NAME)
      );
      
      PRIMARY KEY is added to both ID and NAME columns in the CUSTOMERS table. The combination of values inserted into these columns must be unique, even if the individual column values has duplicates.

      Verification

      To verify if a composite key is created or not, let us display the table definition of a CUSTOMERS table using the DESC query −
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      int
      NO
      PRI
      NULL
      
      NAME
      varchar(20)
      NO
      PRI
      NULL
      
      AGE
      int
      NO
      
      NULL
      
      ADDRESS
      char(25)
      YES
      
      NULL
      
      SALARY
      decimal(18, 2)
      YES
      
      NULL
      

      Dropping MySQL Composite Key

      We can drop the MySQL Composite Key by using the ALTER TABLE... DROP statement.

      Syntax

      Following is the syntax to drop the Composite key from the column of a table −
      ALTER TABLE table_name DROP PRIMARY KEY;
      

      Example

      Using the following SQL statement, we can drop the Composite key constraint from the table −
      ALTER TABLE CUSTOMERS DROP PRIMARY KEY;
      

      Verification

      To verify if the Composite Key has been dropped or not, we display the CUSTOMERS table using the DESC keyword −
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      int
      NO
      
      NULL
      
      NAME
      varchar(20)
      NO
      
      NULL
      
      AGE
      int
      NO
      
      NULL
      
      ADDRESS
      char(25)
      YES
      
      NULL
      
      SALARY
      decimal(18, 2)
      YES
      
      NULL
      

      Composite Key Using a Client Program

      We can also apply a Composite Key constraint on Fields to uniquely identified using a client program.

      Syntax

      PHPNodeJSJavaPython
      To apply a Composite key on fields through a PHP program, we need to execute the "Create/Alter" statement using the mysqli function query() as follows −
      $sql = 'ALTER TABLE customers ADD PRIMARY KEY(cust_Id, cust_Name)';
      $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.');
      
      //creating composite key using alter statement.
      $sql = 'ALTER TABLE customers ADD PRIMARY KEY(cust_Id, cust_Name)';
      if ($mysqli->query($sql)) {
          echo "composite key column created successfully in customers table \n";
      }
      if ($mysqli->errno) {
          printf("Table could not be created!.", $mysqli->error);
      }
      $mysqli->close();
      

      Output

      The output obtained is as follows −
      composite key column created successfully in customers table