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
MySQL Articles
Page 104 of 355
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 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 MoreData and structural independence
Structural IndependenceStructural independence exists when changes in the database structure do not affect DBMS ability to access data.Structural dependence exists when changes in the database structure do not affect DBMS ability to access data.Data IndependenceThe changes done in the lower level will not affect the upper layers. The two types are −Physical Data IndependenceLogical Data IndependenceLet us begin with Physical Data Independence −Physical Data IndependenceModify physical schema without affecting the schema or logical data. It is easier to achieve.It is achieved by the internal level of the database and mapping from the logical level to the internal level. The Conceptual Schema ...
Read More