Primary key

A unique key is nearly the same as primary key. It is a candidate key to uniquely identify each row in a
table. A unique key or primary key comprises a single column or set of columns. No two distinct rows in a
table can have the same value or combination of values in those columns. Depending on its design, a table
may have arbitrarily many unique keys but at most one primary key. A unique key must uniquely identify
all possible rows that exist in a table and not only the currently existing rows. Examples of unique keys
are Social Security numbers associated with a specific person or ISBNs associated with a specific book.
Telephone books and dictionaries cannot use names or words or Dewey Decimal system numbers as
primary keys because they do not uniquely identify telephone numbers or words. A primary key is a
special case of unique keys. The major difference is that for unique keys the implicit NOT NULL constraint
is not automatically enforced, while for primary keys it is. Thus, the values in a unique key column may or
may not be NULL. Another difference is that primary keys must be defined using another syntax. Unique
keys as well as primary keys can be referenced by foreign keys.

Foreign key
A foreign key is linking two tables together, it must be a primary key in one table. It identifies a column
or a set of them in one table that refers to a column or columns in 10 another referenced table. The key
should uniquely identify a column or columns in the referenced table but it is not necessary for it to be
unique itself. The values in one row of the referencing columns must occur in a single row in the
referenced table. Thus, a row in the referencing table can’t contain values that don't exist in the
referenced table (except potentially NULL). This way references can be made to link information together
and it is an essential part of database normalization. Multiple rows in the referencing table may refer to
the same row in the referenced table. Most of the time, it reflects the one (master table, or referenced
table) to many (child table, or referencing table) relationship. The referencing and referenced table may
be the same table, i.e. the foreign key refers back to the same table. Such a foreign key is known in
SQL:2003 as a self-referencing or recursive foreign key. [11] A table can have more than one foreign keys,
which then can have a different referenced table. Foreign key depends on the primary key in the
referential table, that’s why cascading links between tables can be made with foreign keys. If these
relationships are not used correctly it can lead to serious problems in the database .

What is a Database?
A database is a separate application that stores a collection of data. Each database has
one or more distinct APIs for creating, accessing, managing, searching and replicating
the data it holds.

Other kinds of data stores can also be used, such as files on the file system or large
hash tables in memory but data fetching and writing would not be so fast and easy with
those type of systems.

Nowadays, we use relational database management systems (RDBMS) to store and

manage huge volume of data. This is called relational database because all the data is
stored into different tables and relations are established using primary keys or other
keys known as Foreign Keys.

A Relational DataBase Management System (RDBMS) is a software that −

 Enables you to implement a database with tables, columns and indexes.

 Guarantees the Referential Integrity between rows of various tables.

 Updates the indexes automatically.

 Interprets an SQL query and combines information from various tables.

RDBMS Terminology
Before we proceed to explain the MySQL database system, let us revise a few
definitions related to the database.

 Database − A database is a collection of tables, with related data.

 Table − A table is a matrix with data. A table in a database looks like a simple spreadsheet.

 Column − One column (data element) contains data of one and the same kind, for example
the column postcode.

 Row − A row (= tuple, entry or record) is a group of related data, for example the data of
one subscription.

 Redundancy − Storing data twice, redundantly to make the system faster.

 Primary Key − A primary key is unique. A key value can not occur twice in one table. With a
key, you can only find one row.

 Foreign Key − A foreign key is the linking pin between two tables.

 Compound Key − A compound key (composite key) is a key that consists of multiple
columns, because one column is not sufficiently unique.

 Index − An index in a database resembles an index at the back of a book.

 Referential Integrity − Referential Integrity makes sure that a foreign key value always
points to an existing row.

MySQL Database
MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses.
MySQL is developed, marketed and supported by MySQL AB, which is a Swedish
company. MySQL is becoming so popular because of many good reasons −

 MySQL is released under an open-source license. So you have nothing to pay to use it.

 MySQL is a very powerful program in its own right. It handles a large subset of the
functionality of the most expensive and powerful database packages.

 MySQL uses a standard form of the well-known SQL data language.

 MySQL works on many operating systems and with many languages including PHP, PERL, C,
C++, JAVA, etc.

 MySQL works very quickly and works well even with large data sets.

 MySQL is very friendly to PHP, the most appreciated language for web development.

 MySQL supports large databases, up to 50 million rows or more in a table. The default file size
limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a
theoretical limit of 8 million terabytes (TB).

 MySQL is customizable. The open-source GPL license allows programmers to modify the
MySQL software to fit their own specific environments.

SQL data Types

Properly defining the fields in a table is important to the overall optimization of your
database. You should use only the type and size of field you really need to use. For
example, do not define a field 10 characters wide, if you know you are only going to
use 2 characters. These type of fields (or columns) are also referred to as data types,
after the type of data you will be storing in those fields.

MySQL uses many different data types broken into three categories −

 Numeric

 Date and Time

 String Types.

Let us now discuss them in detail.

Numeric Data Types
MySQL uses all the standard ANSI SQL numeric data types, so if you're coming to
MySQL from a different database system, these definitions will look familiar to you. The
following list shows the common numeric data types and their descriptions −

 INT − A normal-sized integer that can be signed or unsigned. If signed, the allowable range
is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to
4294967295. You can specify a width of up to 11 digits.

 TINYINT − A very small integer that can be signed or unsigned. If signed, the allowable
range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. You can specify
a width of up to 4 digits.

 SMALLINT − A small integer that can be signed or unsigned. If signed, the allowable range is
from -32768 to 32767. If unsigned, the allowable range is from 0 to 65535. You can specify a
width of up to 5 digits.

 MEDIUMINT − A medium-sized integer that can be signed or unsigned. If signed, the

allowable range is from -8388608 to 8388607. If unsigned, the allowable range is from 0 to
16777215. You can specify a width of up to 9 digits.

 BIGINT − A large integer that can be signed or unsigned. If signed, the allowable range is
from -9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is
from 0 to 18446744073709551615. You can specify a width of up to 20 digits.

 FLOAT(M,D) − A floating-point number that cannot be unsigned. You can define the display
length (M) and the number of decimals (D). This is not required and will default to 10,2,
where 2 is the number of decimals and 10 is the total number of digits (including decimals).
Decimal precision can go to 24 places for a FLOAT.

 DOUBLE(M,D) − A double precision floating-point number that cannot be unsigned. You can
define the display length (M) and the number of decimals (D). This is not required and will
default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for
a DOUBLE. REAL is a synonym for DOUBLE.

 DECIMAL(M,D) − An unpacked floating-point number that cannot be unsigned. In the

unpacked decimals, each decimal corresponds to one byte. Defining the display length (M)
and the number of decimals (D) is required. NUMERIC is a synonym for DECIMAL.

Date and Time Types

The MySQL date and time datatypes are as follows −

 DATE − A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For example,
December 30th, 1973 would be stored as 1973-12-30.
 DATETIME − A date and time combination in YYYY-MM-DD HH:MM:SS format, between
1000-01-01 00:00:00 and 9999-12-31 23:59:59. For example, 3:30 in the afternoon on
December 30th, 1973 would be stored as 1973-12-30 15:30:00.

 TIMESTAMP − A timestamp between midnight, January 1 st, 1970 and sometime in 2037.
This looks like the previous DATETIME format, only without the hyphens between numbers;
3:30 in the afternoon on December 30 th, 1973 would be stored as 19731230153000
( YYYYMMDDHHMMSS ).

 TIME − Stores the time in a HH:MM:SS format.

 YEAR(M) − Stores a year in a 2-digit or a 4-digit format. If the length is specified as 2 (for
example YEAR(2)), YEAR can be between 1970 to 2069 (70 to 69). If the length is specified
as 4, then YEAR can be 1901 to 2155. The default length is 4.

String Types
Although the numeric and date types are fun, most data you'll store will be in a string
format. This list describes the common string datatypes in MySQL.

 CHAR(M) − A fixed-length string between 1 and 255 characters in length (for example
CHAR(5)), right-padded with spaces to the specified length when stored. Defining a length is
not required, but the default is 1.

 VARCHAR(M) − A variable-length string between 1 and 255 characters in length. For

example, VARCHAR(25). You must define a length when creating a VARCHAR field.

 BLOB or TEXT − A field with a maximum length of 65535 characters. BLOBs are "Binary
Large Objects" and are used to store large amounts of binary data, such as images or other
types of files. Fields defined as TEXT also hold large amounts of data. The difference between
the two is that the sorts and comparisons on the stored data are case sensitive on BLOBs
and are not case sensitive in TEXT fields. You do not specify a length with BLOB or TEXT.

 TINYBLOB or TINYTEXT − A BLOB or TEXT column with a maximum length of 255

characters. You do not specify a length with TINYBLOB or TINYTEXT.

 MEDIUMBLOB or MEDIUMTEXT − A BLOB or TEXT column with a maximum length of

16777215 characters. You do not specify a length with MEDIUMBLOB or MEDIUMTEXT.

 LONGBLOB or LONGTEXT − A BLOB or TEXT column with a maximum length of

4294967295 characters. You do not specify a length with LONGBLOB or LONGTEXT.

 ENUM − An enumeration, which is a fancy term for list. When defining an ENUM, you are
creating a list of items from which the value must be selected (or it can be NULL). For
example, if you wanted your field to contain "A" or "B" or "C", you would define your ENUM
as ENUM ('A', 'B', 'C') and only those values (or NULL) could ever populate that field.
To begin with, the table creation command requires the following details −

 Name of the table

 Name of the fields

 Definitions for each field

Syntax
Here is a generic SQL syntax to create a MySQL table −
CREATE TABLE table_name (column_name column_type);

Now, we will create the following table in the TUTORIALS database.

create table tutorials_tbl(

tutorial_id INT NOT NULL AUTO_INCREMENT,

tutorial_title VARCHAR(100) NOT NULL,

tutorial_author VARCHAR(40) NOT NULL,

submission_date DATE,

PRIMARY KEY ( tutorial_id )

);

Here, a few items need explanation −

 Field Attribute NOT NULL is being used because we do not want this field to be NULL. So, if a
user will try to create a record with a NULL value, then MySQL will raise an error.
 Field Attribute AUTO_INCREMENT tells MySQL to go ahead and add the next available
number to the id field.
 Keyword PRIMARY KEY is used to define a column as a primary key. You can use multiple
columns separated by a comma to define a primary key.

Creating Tables from Command Prompt

It is easy to create a MySQL table from the mysql> prompt. You will use the SQL
command CREATE TABLE to create a table.

Example
Here is an example, which will create tutorials_tbl −

root@host# mysql -u root -p

Enter password:*******

mysql> use TUTORIALS;

Database changed
mysql> CREATE TABLE tutorials_tbl(

-> tutorial_id INT NOT NULL AUTO_INCREMENT,

-> tutorial_title VARCHAR(100) NOT NULL,

-> tutorial_author VARCHAR(40) NOT NULL,

-> submission_date DATE,

-> PRIMARY KEY ( tutorial_id )

-> );

Query OK, 0 rows affected (0.16 sec)

mysql>

NOTE − MySQL does not terminate a command until you give a semicolon (;) at the
end of SQL command.