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
      Alternate 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.

      Alternate Key

      An Alternate Key in a table is nothing but an alternative to the primary key in that table. In other words, they are candidate keys that are not currently selected as the primary key of a table (but has a potential to be one). Hence, they can also be used to uniquely identify a tuple(or a record) in a table.
      If a table consists of only one Candidate key that is treated as the primary key of the table, then there is no alternate key in that table.
      There is no specific query or syntax to set the alternate key in a table. It is just an ordinary column that is a secondary candidate to be selected as a primary key.

      Features of Alternate Keys

      Even though alternate keys are not primary keys, they contain some important properties/features of their own. They are listed below −
      • The alternate key does not allow duplicate values.
      • A table can have more than one alternate keys.
      • The alternate key can contain NULL values unless the NOT NULL constraint is set explicitly.
      • All alternate keys can be candidate keys, but all candidate keys can not be alternate keys. As a primary key, which is also a candidate key, can not be considered as an alternate key.

      Types of Keys in a Table

      Below is the list of keys that are present in a table −
      • Candidate key
      • Primary key
      • Alternate key
      • Foreign Key

      Candidate Key

      A Candidate key is a subset of super keys that is used to uniquely identify records of a table. It can either be a single field or multiple fields. Primary keys, alternate keys, foreign keys in a table are all types of candidate key.

      Primary Key

      A Primary Key is a main key that is used to retrieve records from a table. It is a single column or field in a table that uniquely identifies each record in a database table.
      It can be set using the PRIMARY KEY keyword while creating a table using the CREATE TABLE statement. The syntax is as follows −
      CREATE TABLE table_name(
         COLUMN_NAME1 datatype, 
         COLUMN_NAME2 datatype,
         ... 
         PRIMARY KEY(COLUMN_NAME)
      );
      

      Alternate Key

      An Alternate key is a Candidate key that could be a primary key but is not. Like primary key, it also uniquely identifies the records in a field of a table to retrieve row tuples from the said table. There can be a single or multiple fields identifying as alternate keys in a table.
      There is no syntax to set an alternate key in a database table.

      Foreign Key

      The Primary key of one table will be the Foreign key in another table. While inserting values into these tables, values in the primary key field must match the values in the foreign key field; otherwise, the foreign key column will not accept the INSERT query and throws an error.
      The syntax to set a foreign key field in a table is −
      CREATE TABLE table_name2(
         ... CONSTRAINT constraint_name 
         FOREIGN KEY (column_name2) 
         REFERENCES table_name1(column_name1)
      );
      

      Example

      In the following example, we are creating a sample table named CUSTOMERS in the MySQL database −
      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)
      );
      
      Now let us insert some records into this table created using the INSERT statement as shown below −
      INSERT INTO CUSTOMERS VALUES 
      (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);
      

      Verification

      To verify the keys added to the CUSTOMERS table, let us display the table definition using the following query −
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      int
      NO
      PRI
      NULL
      
      NAME
      varchar(20)
      NO
      
      NULL
      
      AGE
      int
      NO
      
      NULL
      
      ADDRESS
      char(25)
      YES
      
      NULL
      
      SALARY
      decimal(18, 2)
      YES
      
      NULL
      
      Currently, only PRIMARY KEY is set in the table on ID column. The NAME column acts like the Alternate Key, as it will only contain unique records like a Primary Key column. Whereas, both ID and NAME are the Candidate Keys in the CUSTOMERS table.

      Example

      To illustrate the usage of Foreign Key, we would need two tables. Following is the query to create another table ORDERS with the foreign key set as CUSTOMER_ID.
      CREATE TABLE ORDERS (
         OID INT NOT NULL,
         DATE VARCHAR (20) NOT NULL,
         CUSTOMER_ID INT NOT NULL,
         AMOUNT DECIMAL (18, 2),
         CONSTRAINT fk_customers FOREIGN KEY (CUSTOMER_ID)
         REFERENCES CUSTOMERS(ID)
      );
      
      Using the INSERT statement, insert values into this table as follows −
      INSERT INTO ORDERS VALUES 
      (102, '2009-10-08 00:00:00', 3, 3000.00),
      (100, '2009-10-08 00:00:00', 3, 1500.00),
      (101, '2009-11-20 00:00:00', 2, 1560.00),
      (103, '2008-05-20 00:00:00', 4, 2060.00);
      

      Verification

      To verify if the ORDERS table is referenced to the CUSTOMERS table or not, we drop the CUSTOMERS table without dropping the ORDERS table.
      DROP TABLE CUSTOMERS;
      
      Following error is displayed −
      ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'fk_customers' on table 'orders'.
      

      Rules to be Followed for Alternate Keys

      Below are list of rules of alternate keys that should be followed while inserting the record into a table −
      • Alternate key values should be unique.
      • Alternate key can not be NULL.