Most Popular Tutorials




What is mysql keys?

{INDEX|KEY} :- So KEY is an INDEX

KEY is normally a synonym for INDEX.

The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. Keys are used to enforce referential integrity in your database.

File name : index.php


Clustered and Secondary Indexes

Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, you must understand how InnoDB uses the clustered index to optimize the most common lookup and DML operations for each table

  • When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.
  • If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.
  • If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

    • How the Clustered Index Speeds Up Queries

    Accessing a row through the clustered index is fast because the index search leads directly to the page with all the row data. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index records.)

    File name : index.php


    what is difference between primary key and foreign key.

  • primary key enforces clustered index, which is the actual physical order of how data is stored in the disc. On the other hand, Foreign key doesn't create a clustered index in the table.
  • 2) The primary key is unique in the table. So a table cannot contain more than one row with the same primary key, but that's not required for foreign key. You can have more than one rows in a table with same foreign key e.g. all employees will have a unique id but many of them working for the same department will have the same dept_id. This is one of the main difference between a unique key and a primary key.
  • 3) Foreign key helps to maintain the integrity of related tables in the database. For example, it's not possible to delete a department, unless a single employee is referring that. So, an accidental delete of such department will be prevented by database management systems e.g. Oracle, MySQL or SQL SERVER. You can further setup cascade actions with foreign key e.g. when a department is deleted, update the dept_id of all employees in Employee table referring to this department. You can setup such arrangement using ON CASCADE clause.

    • File name : index.php

    primary key:-

  • primary key cannot accept null values.
  • only one primary key in table
  • primary key uniquely identify a record in table.
  • primary key is clustered index.


    • foreign key :-

  • foreign key accept multiple null values.
  • More than one foreign key in a table.
  • Foreign key is a field in a table. that is primary key in another table.
  • foreign key is non-clustered index.

    • what is candidate key?

    CANDIDATE KEY in SQL is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes. The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key.

    Candidate key properties :-

    File name : index.php

  • It must contain unique values
  • Candidate key in SQL may have multiple attributes
  • Must not contain null values
  • It should contain minimum fields to ensure uniqueness
  • Uniquely identify each record in a table



    • How to create candidate key in mysql?

    File name : index.php

    create table itechxpert
    ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name varchar(40),
    Age int
    );

    alter table itechxpert add unique key name_age_unique(Name,Age);


    insert into itechxpert(Name,Age) values('mahtab',30);

    when you insert again this data then show error. because name_age_unique column is candidate key. which is unique.

    What is the Alternate key?

    ALTERNATE KEYS is a column or group of columns in a table that uniquely identify every row in that table. A table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are not primary key are called an Alternate Key.

    File name : index.php


    super key

    A super key is a group of single or multiple keys which identifies rows in a table.

    File name : index.php


    What is the Composite key?

    COMPOSITE KEY is a combination of two or more columns that uniquely identify rows in a table. The combination of columns guarantees uniqueness, though individually uniqueness is not guaranteed. Hence, they are combined to uniquely identify records in a table.

    The difference between compound and the composite key is that any part of the compound key can be a foreign key, but the composite key may or maybe not a part of the foreign key.

    File name : index.php


    Difference between primary key and candidate key

    Primary Key                            Candidate Key
    There can be only one primary key in a relation     They can be multiple candidate keys in a relation.
    It is not mandatory to specify a primary key for each relation.    It is not possible to define any relation without the candidate key.
    No attributes of the primary key can contain Null values.    The attributes of the candidate key can contain Null values.
    A primary key defines the most important attribute of a relation.    Candidate key features candidates that can be defined as the primary key.
    A primary key is the candidate key.    Each candidate key is not the primary key.





    Previous Next


    Trending Tutorials


    PHP

    PHP

    100+ Tutorials
    oops

    PHP OOPs

    80+ Tutorial
    Codeigniter

    Codeigniter

    100+ Tutorial
    Laravel

    Laravel

    100+ Tutorials
    Node.js

    NODE.JS

    100+ Tutorials
    Javascript

    Javascript

    100+ Tutorials


    Review & Rating

    0.0 / 5

    0 Review

    5
    (0)

    4
    (0)

    3
    (0)

    2
    (0)

    1
    (0)

    Write Review Here


    Most Popular Tutorials

     Important Link

    Ittutorial