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 92 of 341
How can we emulate CHECK CONSTRAINT by using views?
As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below −ExampleSuppose we have a table named ‘car1’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows −mysql> Create table car1 (number char(9)); Query OK, 0 rows affected (0.32 sec) mysql> Insert into car1 values('AB-235-YZ'); Query OK, 1 row affected (0.10 sec)The above value is a valid one but what ...
Read MoreHow can we emulate CHECK CONSTRAINT by using MySQL GENERATED COLUMN?
As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below −ExampleSuppose we have a table named ‘car’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows −mysql> Create table car2 (number char(9)); Query OK, 0 rows affected (0.32 sec) mysql> Insert into car2 values('AB-235-YZ'); Query OK, 1 row affected (0.10 sec)The above value is a valid one but what ...
Read MoreHow can we emulate CHECK CONSTRAINT by using triggers?
As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below −ExampleSuppose we have a table named ‘car’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows −mysql> Create table car (number char(9)); Query OK, 0 rows affected (0.32 sec) mysql> Insert into car values('AB-235-YZ'); Query OK, 1 row affected (0.10 sec)The above value is a valid one but what ...
Read MoreWhat is the difference between MySQL DATETIME and TIMESTAMP data type?
Both the data types store data in “YYYY-MM-DD HH:MM:SS” format and include date as well as time. In spite of these similarities they are having the following differences −Range − Datetime 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. But 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’.Size − Datetime requires 5 bytes along with 3 additional bytes for fractional seconds’ data storing. On the other hand, timestamp datatype requires 4 bytes along with 3 additional bytes for fractional seconds’ data ...
Read MoreHow many DATE data types are supported by MySQL?
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 MoreHow can I see the constraints which are applied to a table stored in the database I am currently using?
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 MoreWhat is the difference between CHAR and VARCHAR in MySQL?
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 ...
Read MoreHow to disable MySQL foreign key checks and what are the benefits ofndisabling it?
We can disable foreign key checks with the help of the following statement −mysql> Set foreign_key_checks = 0; Query OK, 0 rows affected (0.00 sec)And we can enable it with the help of the following statement −mysql> Set foreign_key_checks = 1; Query OK, 0 rows affected (0.00 sec)Some benefits of disabling foreign key checks are as follows −After disabling foreign key checks we can load data into parent and child table in any order. Otherwise, we must have to load the data first in the parent table and then in the child table.Without disabling foreign key checks we cannot drop ...
Read MoreWhat are the different ways to maintain data integrity in child table when the record is deleted in parent table?
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.
Read MoreHow can we add FOREIGN KEY constraints to more than one fields of a MySQL table?
MySQL allows us to add a FOREIGN KEY constraint on more than one field in a table. The condition is that each Foreign Key in the child table must refer to the different parent table.ExampleSuppose we have a table ‘customer2’ which have a Primary Key constraint on the field ‘cust_unq_id’ as follows −mysql> describe customer2; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | cust_id | int(11) | YES | | NULL | | | First_name | ...
Read More