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
Articles on Trending Technologies
Technical articles with clear explanations and examples
How 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 MoreSpatial Databases
Spatial data is associated with geographic locations such as cities, towns etc. A spatial database is optimized to store and query data representing objects. These are the objects which are defined in a geometric space.Characteristics of Spatial DatabaseA spatial database system has the following characteristicsIt is a database systemIt offers spatial data types (SDTs) in its data model and query language.It supports spatial data types in its implementation, providing at least spatial indexing and efficient algorithms for spatial join.ExampleA road map is a visualization of geographic information. A road map is a 2-dimensional object which contains points, lines, and polygons ...
Read MoreHow to insert NULL keyword as a value in a character type column of MySQL table having NOT NULL constraint?
It is quite possible to insert the NULL keyword as a value in a character type column having NOT NULL constraint because NULL is a value in itself. Following example will exhibit it −ExampleSuppose we have a table test2 having character type column ‘Name’ along with NOT NULL constraint on it. It can be checked from the DESCRIBE statement as follows −mysql> Describe test2\G *************************** 1. row *************************** Field: id Type: int(11) Null: NO Key: Default: NULL Extra: *************************** 2. row *************************** Field: NAME Type: varchar(20) Null: NO Key: ...
Read MoreHow can we remove NOT NULL constraint from a column of an existing MySQL table?
We can remove a NOT NULL constraint from a column of an existing table by using the ALTER TABLE statement.ExampleSuppose we have a table ‘test123’ having a NOT NULL constraint on column ‘ID’ as follows −mysql> DESCRIBE test123; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | ID | int(11) | NO | | NULL | | | Date | date | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.04 sec)Now if we ...
Read MoreHow can we apply a NOT NULL constraint to a column of an existing MySQL table?
We can apply the NOT NULL constraint to a column of an existing MySQL table with the help of ALTER TABLE statement. SyntaxALTER TABLE table_name MODIFY colum_name datatype NOT NULL; Examplemysql> Create table test123(ID INT, Date DATE); Query OK, 0 rows affected (0.19 sec) mysql> Describe test123; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | ID | int(11) | YES | | NULL | | | Date | date ...
Read MoreWhat happens when we apply NOT NULL constraint, with ALTER TABLE statement, to a column contains NULL values?
In this case, MySQL will return an error message regarding data truncated for the column. Following is an example of demonstrating it −ExampleSuppose we have a table ‘test2’ which contains a NULL value in column ‘ID’ at 2nd row. Now, if we will try to declare the column ID to NOT NULL then MySQL will return the error as follows −mysql> Select * from test2; +------+--------+ | ID | Name | +------+--------+ | 1 | Gaurav | | NULL | Rahul | +------+--------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE TEST2 MODIFY ID INT NOT NULL; ERROR 1265 (01000): Data truncated for column 'ID' at row 2
Read More