DEFAULT constraint is used set a default value for a column in MySQL table. If it is applied on a column then it will take the default value of not giving any value for that column. Its syntax would be as follows −SyntaxDEFAULT default_valueHere, default_value is the default value set for the column.ExampleThe query below will create a table named workers where we assign the column id a DEFAULT value 1000.mysql> Create table workers(Name Varchar(25), Id INT NOT NULL DEFAULT 1000); Query OK, 0 rows affected (0.47 sec) mysql> Insert into workers(Name, Id) values('Ram', 101); Query OK, 1 row ... Read More

MySQL supports following 5 types of DATE data type −DATE - A date is in the range between 1000-01-01 and 9999-12-31. “YYYY-MM-DD” is the default DATE format. For example, January 17th, 1984 would be stored as 1984-01-17.DATETIME − This data type supports a date along with time in the range between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. “YYYY-MM-DD HH:MM:SS” is the default DATETIME format. For example, 2:20 in the afternoon on January 17th, 1984 would be stored as 1984-01-17 14:20:00.TIMESTAMP − A timestamp data type supports a date along with time in the range between ‘1970-01-01 00:00:01’ to ‘2038-01-19 08:44:07’. It ... Read More

MySQL SHOW CREATE TABLE statement will provide us the constraints applied to a particular table along with some other details about that table stored in another database then I am currently using. Its syntax would be as follows −SyntaxSHOW CREATE TABLE db_name.table_name;Here table_name is the name of the table on which we want to see the constraints.Db_name is the name of the database name in which table is stored.ExampleIn this example we are getting the detail of the table named ‘arena’ stored in MySQL database −mysql> Show Create table mysql.arena\G *************************** 1. row *************************** Table: arena Create Table: CREATE TABLE ... Read More

MySQL SHOW CREATE TABLE statement will provide us the constraints applied to a particular table along with some other details about that table. Its syntax would be as follows −SyntaxSHOW CREATE TABLE table_name;Here table_name is the name of the table on which we want to see the constraints.ExampleIn this example we are getting the detail of the table named ‘employees’ −mysql> Show Create table employees\G *************************** 1. row ***************************        Table: employees Create Table: CREATE TABLE `employees` (    `Id` int(11) NOT NULL AUTO_INCREMENT,    `Name` varchar(35) DEFAULT NULL,    PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 ... Read More

As we know that in MySQL, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. This length prefix points out the number of bytes in the value of data. The data value itself will decide that when VARCHAR data type will use 1-byte and when 2-byte prefix length.A column uses 1-byte length if values require no more than 255 bytes.A column uses 2-byte length if values may require more than 255 bytes.

The following table will provide us the differences between PRIMARY KEY and UNIQUE constraint −PRIMARY KEYUNIQUE Constraint1. Only one Primary key can be created on a table.1. More than one UNIQUE Constraints can be added to a table.2. Primary key creates clustered index by default.2. UNIQUE Constraint creates a non-clustered index by default.3. We cannot insert null values in the column which is defined as PRIMARY KEY.3. We can insert null values in the column having a UNIQUE constraint.

Actually, both of these data types in MySQL store strings and can be set with a maximum length. The use of these data types purely depends on the need. Followings are some points which will make us clear that when one should CHAR and when VARCHAR −Suppose if we have fixed size of data, such as flags of “Y” and “N” then it is better to use CHAR rather than VARCHAR. It is because 1 byte of length prefix also used with VARCHAR. In other words, for the above kind of data, CHAR will store the only 1byte which is ... Read More

CHAR and VARCHAR are both ASCII character data types and almost same but they are different at the stage of storing and retrieving the data from the database. Following are some important differences between CHAR and VARCHAR in MySQL −CHAR Data TypeVARCHAR Data TypeIts full name is CHARACTERIts full name is VARIABLE CHARACTERIt stores values in fixed lengths and are padded with space characters to match the specified lengthVARCHAR stores values in variable length along with 1-byte or 2-byte length prefix and are not padded with any charactersIt can hold a maximum of 255 characters.It can hold a maximum of 65, ... Read More

Both CHAR and NCHAR are fixed length string data types. They have the following differences −CHAR Data TypeNCHAR Data TypeIts full name is CHARACTER.Its full name is NATIONAL CHARACTERIt uses ASCII character setIt uses Unicode character set and data is stored in UTF8 formatIt occupies 1-byte of space for each character.It Occupies 2-bytes of space for each charactermysql>create table hello1(name CHAR(20)); Query OK, 0 rows affected (0.15mysql>create table hello(name NCHAR(20)); Query OK, 0 rows affected (0.61

When two tables are connected with Foreign key and data in the parent table is deleted, for which record exists in child table too, then followings are the ways to maintain data integrity −On Delete CascadeThis option will remove the record from child table too if that value of the foreign key is deleted from the main table.On Delete Null This option will set all the values in that record of child table as NULL, for which the value of the foreign key is deleted from the main table.