Primary key in mysql

                         The primary key in mysql is used to identify the unique values in table. It don't allow duplicate entries in mysql table. The primary key column should be NOT NULL. It don't allow null values in mysql table. The mysql table can have only one primary key.

How to create primary key in mysql:

                         The mysql query for  create primary key is:

         Create table table_name (column_name datatype(length), primary key(column_name))

For example,
                        create table table1(name varchar(30), address varchar(100), primary key(name))

Where the column 'name' is primary key.

How to add primary key to existing table in mysql:

                       You created table without primary like this:

                

                   create table table1(name varchar(30),address varchar(100))

                     But you need to add primary key in order to remove duplicate entries in mysql. At the time you can add primary key to existing table in mysql. Following mysq query is used for add primary key.

                 Alter table table_name add primary key(column_name)

For example,

                 Alter table table1 add primary key(num)   

How to remove primary key in mysql:

                You created table with primary. But you need not primary key for particular table. Then how can remove primary key from table in mysql. The following mysql query is used to drop the primary key from table in mysql.

                    alter table table_name drop primary key

For example,

               

                      alter table table1 drop primary key 

          where, 

                       The primary key is removed from table 'table1'. Now you can add duplicate values to your mysql table.

Related Post:

distinct in mysql
Order by in mysql
Group by multiple columns in mysql
How to use order by together with group by in mysql
Difference between delete truncate drop in mysql
how to use order by before group by in mysql
How to reset auto increment initial value in mysql

Add auto_increment to existing column in mysql

                     Auto_ increment is used to increase by 1 while insert a new data to mysql table. The auto increment column must be defined as key.

Create table with auto_increment in mysql:

                      You can add auto_increment while creating new table. The mysql query is:

                       CREATE TABLE table1 

                               (No int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, 

                                 Game_name varchar(15), Image varchar(15)) 

Now the table is created with auto increment.

Add auto_increment to existing column in mysql:

                      You created a table in which the column with specific definitions in mysql. You created table column without auto increment. Then how can you add it to existing column in mysql.  The following mysql query is used to add auto increment to existing column in mysql.

Mysql Query:

                         Alter table table_name modify column_name datatype(length) AUTO_INCREMENT PRIMARY KEY

Consider the example:
The structure of table1 in database new look like this:

                         Now we are going to add auto increment to column 'No' in mysql table 'table1'.  The mysql query is:

                     Alter table table1 modify No int(5) AUTO_INCREMENT PRIMARY KEY

Now your structure look like this:

     Now you can see the column name 'No' is auto increment. Suppose  your mysql query like below:

     Alter table table1 modify No int(5) AUTO_INCREMENT

Then you'll get error like this:

                                 So you should be add 'AUTO_INCREMENT' with 'PRIMARY KEY' in mysql table.

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

change column datatype in mysql
Change table name in mysql
Add primary key to existing column in mysql
how to add auto increment to existing column in mysql
How to remove auto increment from column in mysql
How to reset auto increment initial value in mysql

how to use order by before group by in mysql

Add primary key to existing column in mysql

                      Primary key is used to avoid duplicate entries in mysql table. A mysql table can have only one primary key.

Create table with primary key in mysql:

                                                The mysql query for create table with primary key is:

                       CREATE TABLE table_name(column_name datatype(length), 
                             PRIMARY KEY(column_name))

For example,
                       CREATE TABLE table1(No int(5) datatype(length), ,Game_name varchar(15),                                     Image varchar(15), PRIMARY KEY(No))

Now the table column 'No' is primary key in this table.

How to add primary key to existing column in mysql:

                         
                              You can add primary while creating new table. But can you add primary key to existing column in mysql? Yes, you can add primary key to existing column in mysql. The mysql query is:
                     
                       Alter table table_name add PRIMARY KEY(column_name)

Consider the example:
 table1 is name of this table.

When you click the structure of table in your database, it is look like this:

                    There is no index defined.  Suppose you've to add primary key to column 'Game_name'. On that time you can add the primary key. The mysql query is:

                     Alter table table1 add PRIMARY KEY(Game_name)

Now you click the structure of table in mysql database, the index is defined as below:

                 Now your Game_name column  is changed from NULL into NOT NULL with primary key. Because the primary key should not be null. Only one primary key in a mysql table. A mysql table haven't more than one primary key.

Related Post:
Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

change column datatype in mysql
Change table name in mysql
Add primary key to existing column in mysql
how to add auto increment to existing column in mysql
How to reset auto increment initial value in mysql