Working with Keys in MySQL

Primary key add in MySQL :

In MySQL, we add a primary key on the column of the existing table.

For Example : First of all we have to create a table with tha specified fields.

Create a table :
create table friend(
friend_id int(10),
friend_name varchar(200,
date date
);

Insert the values into the table :
insert into friend values(01,'Ram','2009-11-21');

insert into friend values(02,'Shayam','2009-12-22);

select * from friend;
Output :



Command to view data type of Table named employees:

The describe employees return the table attribute like fieldname,Type,Null,Key,Default,Extra etc.


mysql> describe friend;
Output:

Command to add primary key into the Table name friend:

The  Alter Query is used to redefine the table 'friend' and add keyword is used to add primary key on column 'friend_name'.

mysql> alter table friend 
add primary key (friend_name);

Data type of Table named friendafter adding primary key:

describe friend;

Output:

Unique key in mysql :

The alter unique key is to be used to changed the structure of the table and how to add a unique key of the specified column in the table.
In the unique key allows one of the null value to be inserted in the column and in the unique key table can contains more than one unique keys.

For Example : In the following we have to create a table on the required fieldnames and datetypes.

 
In this table contain a Id as a primary key.

Create table "emp":

CREATE TABLE emp (
ID int(20) NOT NULL,
EmpID bigint(20) NOT NULL,
PRIMARY KEY (`ID`)
);


Query to alter the column unique :

The  Alter Query is used to modify the table 'Emp'
and add a unique multiple columns to the table 'Emp'.


mysql> ALTER TABLE emp ADD UNIQUE (ID, EmpID);
Query OK, 2 rows affected (0.16 sec)

Describe the table "emp" :

The Describe table is used to show the fieldnames, Type,Null, etc. of table emp.


mysql> describe emp;

Output:

Foreign Key in mysql :

A Foreign key relationship between two database tables. These tables follows
three conditions which as given below.

1. Both tables must be of the same database table types.
2. These fields which have to used in the foregin key relationship must be indexed.
3. The foregin key field relationship must be similiar in datatype.

CREATE TABLE species (id int NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL, PRIMARY KEY(id)) ;



INSERT INTO species VALUES (1, 'birds'), (2, 'dog'),
(3, 'cat'), (4, 'monkey');


create a another table which name is zoo .
CREATE table zoo (
id int(4) NOT NULL,
name VARCHAR(50) NOT NULL,
FK_species int(4) NOT NULL,
INDEX (FK_species),
FOREIGN KEY (FK_species) REFERENCES species (id),
PRIMARY KEY(id)
);

As the above command,  relationship in a foreign keynow exists between the fieldszoo.species and species.id. An entry in the zoo table will be permitted
only if the corresponding zoo.species field matches a value in the species idfield. what happens when we have attempt to enter a record for Harry cat with an invalid species command:

Query:

mysql> INSERT INTO zoo VALUES (1, 'Harry', 5);
output:

 

MySQL defines the species table to find if the species query exists and, finding that it does not, rejects the record. Contrast this with what happens
when you enter the same record with a valid species code (one that already exists in the species table):

Query:

mysql> insert into zoo VALUES (1, 'Harry', 3);
Query OK, 1 row affected (0.06 sec)

Here, MySQL findout the species table to see if the species query exists and, finding that it does, permits the record to be saved to the zoo table.

To delete a foreign key relationship, first use the SHOW CREATE TABLE command to find out InnoDB's internal label for the field.

Query:

+-------+---------------------------------------------------+
| Table  | Create Table |
+-------+---------------------------------------------------+
| zoo     | CREATE TABLE `zoo` (
`id` int(4) NOT NULL default '0',
`name` varchar(50) NOT NULL default '',
`FK_species` tinyint(4) NOT NULL default '0',
KEY `FK_species` (`FK_species`),
CONSTRAINT `zoo_ibfk_1` FOREIGN KEY (`FK_species`)
REFERENCES `species` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------+

And then use the command of the ALTER TABLE  with the DROP FOREIGN KEY given as below:

Query:

mysql> ALTER TABLE zoo DROP FOREIGN KEY zoo_ibfk_1;

To add a foreign key to an existing table, use the ALTER TABLE command with an ADD FOREIGN KEY clause to define the appropriate field as a foreign key:

Query:
mysql> ALTER TABLE zoo ADD FOREIGN KEY
(FK_species) REFERENCES species (id);