Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQLi Articles
Page 93 of 341
How can we remove composite PRIMARY KEY constraint applied on multiple columns of an existing MySQL table?
We can remove composite PRIMARY KEY constraint from multiple columns of an existing table by using DROP keyword along with ALTER TABLE statement.ExampleSuppose we have a table ‘Room_allotment’ having a composite PRIMARY KEY constraint on columns ‘ID’ and ‘RoomNo’ as follows −mysql> describe room_allotment; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | ...
Read MoreHow can we set PRIMARY KEY on multiple columns of an existing MySQL table?
We can set PRIMARY KEY constraint on multiple columns of an existing table by using ADD keyword along with ALTER TABLE statement.ExampleSuppose we have a table ‘Room_allotment’ as follows −mysql> Create table Room_allotment(Id Int, Name Varchar(20), RoomNo Int); Query OK, 0 rows affected (0.20 sec) mysql> Describe Room_allotment; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | Id | int(11) | YES | | NULL | | | Name | varchar(20) | YES | | NULL ...
Read MoreWhat happens if I will try to drop PRIMARY KEY constraint from the AUTO_INCREMENT column?
As we know the AUTO_INCREMENT column must have the PRIMARY KEY constraint on it also hence when we will try to drop PRIMARY KEY constraint from the AUTO_INCREMENT column the MySQL returns an error message regarding the incorrect table definition. The example below will demonstrate it −ExampleSuppose we have ‘Accounts’ table having the following description −mysql> Describe accounts; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | Sr | int(10) | NO | PRI | NULL | auto_increment | ...
Read MoreHow can we remove PRIMARY KEY constraint from a column of an existing MySQL table?
We can remove PRIMARY KEY constraint from a column of an existing table by using DROP keyword along with ALTER TABLE statement.ExampleSuppose we have a table ‘Player’ having a PRIMARY KEY constraint on column ‘ID’ as follows −mysql> DESCRIBE Player; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ID | int(11) | NO | PRI | NULL | | | Name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in ...
Read MoreHow can we apply the PRIMARY KEY constraint to the field of an existing MySQL table?
We can apply the PRIMARY KEY constraint to a column of an existing MySQL table with the help of ALTER TABLE statement. SyntaxALTER TABLE table_name MODIFY colum_name datatype PRIMARY KEY; OR ALTER TABLE table_name ADD PRIMARY KEY (colum_name); Suppose we have the following table named ‘Player’ and we want to add the PRIMARY KEY constraint to the column ‘ID’ then it can be done with the help of ALTER TABLE command as follows −mysql> DESCRIBE Player; +-------+-------------+------+-----+---------+-------+ | Field | Type ...
Read MoreHow can I define a column of a MySQL table PRIMARY KEY without using the PRIMARY KEY keyword?
As we know that a PRIMARY KEY column must have unique values and cannot have null values hence if we will define a column with UNIQUE and NOT NULL constraint both then that column would become PRIMARY KEY column.ExampleIn this example, we have created a table ‘Student123’ by defining column ‘RollNo’ with UNIQUE and NOT NULL constraints. Now, by describing the table we can see that ‘RollNo’ is the PRIMARY KEY column.mysql> Create table Student123(RollNo INT UNIQUE NOT NULL, Name varchar(20)); Query OK, 0 rows affected (0.25 sec) mysql> DESCRIBE Student123; +--------+-------------+------+-----+---------+-------+ | Field | Type ...
Read MoreWhat happens if I will add a UNIQUE constraint on the same column for multiple times?
When we will add a UNIQUE constraint on the same column multiple times then MySQL will create the index on that column for a number of times we have added the UNIQUE constraint.ExampleSuppose we have the table ‘employee’ in which we have the UNIQUE constraint on ‘empid’ column. It can be checked form the following query −mysql> Describe employee; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | empid | int(11) | YES | UNI | NULL | | | ...
Read MoreHow can we remove multicolumn UNIQUE indexes?
Multicolumn UNIQUE indexes can also be removed in the same as we remove UNIQUE constraint from the table.ExampleIn this example, with the following query we have removed the multicolumn UNIQUE indexes on table ‘employee’ −mysql> DROP index id_fname_lname on employee; Query OK, 0 rows affected (0.30 sec) Records: 0 Duplicates: 0 Warnings: 0The removal of UNIQUE indexes can be observed from the result sets of the following query −mysql> show index from employee; Empty set (0.00 sec) mysql> describe employee; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
Read MoreHow can we check the indexes created by a UNIQUE constraint on a MySQL table?
SHOW INDEX statement is used to check the indexes created by a UNIQUE constraint on a MySQL table.SyntaxSHOW INDEX from table_name;ExampleSuppose we have the table ‘empl’ which have a UNIQUE constraint on column ‘empno’.mysql> describe empl; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | empno | int(11) | YES | UNI | NULL | | | F_name | varchar(20) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 2 rows in set (0.23 sec)Now as we know that ...
Read MoreHow can we apply UNIQUE constraint to the field of an existing MySQL table?
We can apply the UNIQUE constraint to a column of an existing MySQL table with the help of ALTER TABLE statement.SyntaxALTER TABLE table_name MODIFY colum_name datatype UNIQUE; OR ALTER TABLE table_name ADD UNIQUE (colum_name);ExampleSuppose we have the following table named ‘Test4’ and we want to add UNIQUE constraint to the column ‘Name’ then it can be done with the help of ALTER TABLE command as follows −mysql> DESCRIBE test4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ID ...
Read More