Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
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 More
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 More
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
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 More
The increment (++) and decrement (---) operators should be avoided since it can lead to unexpected results. Here are some of the conditions −ExampleIn an assignment statement, it can lead to unfavorable results −Live Demo var a = 5; var b = ++a; var c = a++; var d = ++c; document.write(a); document.write("\r"+b); document.write("\r"+c); document.write("\r"+d); OutputWhitespace between the operator and variable can also lead to unexpected results −a = b = c = 1; ++a ; b -- ; c;
MySQL allows us to add a FOREIGN KEY constraint on multiple columns 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 | varchar(20) | ... Read More
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 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 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
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.