Udemy REST Assured free

MySQL Keys and Indexes Interview Questions

Showing 1 - 6 of 6 results

What is an index? How can an index be declared in MySQL?

An index is a data structure of a MySQL table that is used to speed up the queries.

It is used by the database search engine to find out the records faster. One or more fields of a table can be used as an index key. Index key can be assigned at the time of table declaration or can be assigned after creating the table.

To create index use below command:

CREATE TABLE users(
username VARCHAR(50) PRIMARY KEY,
email VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
INDEX (username, email));

To show index use below command:

mysql> SHOW INDEXES FROM Shopping.users;

What are the differences between a primary key and a foreign key?

The database table uses a primary key to identify each row uniquely. It is necessary to declare the primary key on those tables that require to create a relationship among them. One or more fields of a table can be declared as the primary key.

When the primary key of any table is used in another table as the primary key or another field for making a database relation, then it is called a foreign key.

The differences between these two keys are mentioned below.

  • The primary key uniquely identifies a record, whereas foreign key refers to the primary key of another table.
  • The primary key can never accept a NULL value but foreign key accepts a NULL value.
  • When a record is inserted in a table that contains the primary key then it is not necessary to insert the value on the table that contains this primary key field as the foreign key.
  • When a record is deleted from the table that contains the primary key then the corresponding record must be deleted from the table containing the foreign key for data consistency. But any record can be deleted from the table that contains a foreign key without deleting a related record of another table.

Example

CREATE TABLE manufacturers(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));

CREATE TABLE items(id INT AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(50), 
type VARCHAR(50), 
brand VARCHAR(50), 
manufacturer_id INT, 
FOREIGN KEY (manufacturer_id) REFERENCES manufacturers(id));

What Is The Difference Between A Super Key And The Candidate Key?

Below are the difference between Super Key and Candidate Key:

Point of Distinction Super Key Candidate Key
Basics Super Key is an attribute (or set of attributes) that is used to uniquely identifies all attributes in a relation. Candidate key is a set of attributes (or attribute) which uniquely identify the tuples in a relation or table. As we know that Primary key is a minimal super key, so there is one and only one primary key in any relation but there is more than one candidate key can take place. Candidate Key is a proper subset of a super key. A super key with no repeated attribute is called candidate key.
Relation All super keys can’t be candidate keys. But all candidate keys are super keys.
Count In a relation, Number of Super Keys are more than number of candidate key. Less than Super key
Criteria Various super keys together makes the criteria to select the candidate keys. Various candidate keys together makes the criteria to select the primary keys.

Null are allowed in both Super Key as well as Candidate Key.

What Is The Main Difference Between Primary Key, Unique Key, And Foreign Key?

Below are the difference between Primary Key, Unique Key and Foreign Key

Parameters Primary Key Unique Key Foreign Key
Basic A primary key is a column of table which uniquely identifies each tuple (row) in that table. Primary key enforces integrity constraints to the table. Unique key constraints also identifies an individual tuple uniquely in a relation or table. A table can have more than one unique key unlike primary key. Foreign key is a field in the table that is primary key in another table.
Null Value Acceptance Cannot accept null values. Can accept one null value. Accept multiple null values.
Number of keys that can be defined in a table Only one primary key More than one unique key We can have more than one foreign key in a table.
Index Create cluster index Create non-cluster index Do not automatically create an index clustered or non-clustered. You must manually create an index of foreign key.
Support Auto-increment of Value Supported Not supported Not supported
Restriction on delete We can't delete primary key value from the parent table which is used as a foreign key in child table. To delete we first need to delete that primary key value from the child table. Unique Constraint can not be related with another table's as a Foreign Key. In SQL Server, Unique key can be made foreign key into another table. We can delete the foreign key value from the child table even though that refers to the primary key of the parent table.

 

Can a foreign key contain null or duplicate value ?

Yes, Foreign key contains null value or duplicate value. A foreign key containing null values cannot match the values of a parent key, since a parent key by definition can have no null values. However, a null foreign key value is always valid, regardless of the value of any of its non-null parts.

You can have multiple foreign keys on the same table. Unlike primary keys, foreign keys can contain duplicate values. Also, it is OK for them contain NULL values. Indexes aren't automatically created for foreign keys; however, as a DBA, you can define them. A table is allowed to contain more than one foreign key.

Is primary key contain null value ?

A PRIMARY KEY column is equivalent to UNIQUE and NOT NULL and is indexed column by default. It should be UNIQUE because a primary key identifies rows in a table so 2 different row should not have the same key.

In addition a primary key may be used as a FOREIGN KEY in other tables and that's why it cannot be NULL so that the other table can find the rows in the referenced table.

Subscribe to MySQL Keys and Indexes Interview Questions