PHP Tutorials
- What is MySqli
- mysql query
- mysql query example
- InnoDB
- mysql column Type
- CRUD Example
- Connection Using Function
- mysql keys
- SELECT
- WHERE
- UPDATE
- Count no of Rows
- ALIAS
- AND, AND & OR
- BETWEEN
- COMPARISON OPERATOR
- DELETE
- DELETE LIMIT
- DISTINCT
- EXISTS
- FROM
- GROUP BY
- HAVING
- IN
- INTERSECT
- IS NULL & IS NOT NULL
- LIKE
- NOT
- ORDER BY
- SELECT LIMIT
- SUBQUERY
- TRUNCATE
- UNION && UNION ALL
- Concat & Group_Concat
- mysql Function
- Mysql Insert Id
- MySql Aggregate Function
- Mysql Join
- JOIN in MySql
- Trigger
- Procedure
- Transaction
- views
- Index
- SQL Injection
- Normalization
- Query Bind
- Interview Questions
Important Link
what is key?
{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
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.
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.
File name : index.php
File name : index.php
File name : index.php
File name : index.php
File name : index.php
File name : index.php
File name : index.php