Found 4220 Articles for MySQLi

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.

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 More

We can remove FOREIGN KEY constraint from a column of an existing table by using DROP keyword along with ALTER TABLE statement. Syntax ALTER TABLE table_name DROP FOREIGN KEY constraint_name Here constraint name is the name of foreign key constraint which we applied while creating the table. If no constraint name is specified then MySQL will provide constraint name which can be checked by SHOW CREATE TABLE statement. Example The following query will delete the FOREIGN KEY constraint from ‘orders’ table − mysql> Alter table orders DROP FOREIGN KEY orders_ibfk_1; Query OK, 0 rows affected (0.22 sec) ... Read More

On specifying ZEROFILL for a numeric column, MYSQL automatically pads the displayed value of the field with zeros up to the display width specified in the column definition.For example, we create a table showzerofill and insert the values as follows −mysql> Create Table showzerofill(Val1 INT(5) ZEROFILL, Val2 INT(5)); Query OK, 0 rows affected (0.09 sec) mysql> Insert into showzerofill(Val1, Val2) values(1, 1>, , , , ; Query OK, 5 rows affected (0.03 sec) Records: 5 Duplicates: 0 Warnings: 0Now we can easily understand the effect of ZEROFILL on the values of column Val1.ZEROFILL padded zeros in the number up ... Read More

While deleting the row from the parent table, if the data of that row is used in the child table then MySQL will throw an error because of the failure of FOREIGN KEY constraint. It can be understood with the example of two tables named ‘customer’ and ‘orders’. Here, ‘customer’ is the parent table and ‘orders’ is the child table. We cannot delete a row, that is used in child table ‘orders’, from the ‘customer’ table. It can be demonstrated by deleting the values from the parent table as follows −mysql> Select * from Customer; +----+--------+ | id | name ... Read More

Actually, foreign keys enforce referential integrity that helps us to maintain the consistency and integrity of the data automatically. It can be understood with the example of two tables named ‘customer’ and ‘orders’. Here, ‘customer’ is the parent table and ‘orders’ is the child table. We cannot create an order for a non-existent customer. It can be demonstrated by inserting the values in both the tables as follows −mysql> Select * from Customer; +----+--------+ | id | name   | +----+--------+ | 1  | Gaurav | | 2  | Raman  | | 3  | Harshit| | 4  | Aarav  | ... Read More

The relationship between parent and child table is One-to-Many relationship. It can be understood with the example of two tables named ‘customer’ and ‘orders’. Here, ‘customer’ is the parent table and ‘orders’ is the child table. The relationship is one-to—many because a customer can have more than one order. It can be demonstrated by inserting the values in both the tables as follows −mysql> Select * from Customer; +----+---------+ | id | name    | +----+---------+ | 1  | Gaurav  | | 2  | Raman   | | 3  | Harshit | | 4  | Aarav   | +----+---------+ ... Read More

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

We can add a FOREIGN KEY constraint to a column of an existing MySQL table with the help of ALTER TABLE statement. Syntax ALTER TABLE table_name ADD FOREIGN KEY (colum_name) REFERENCES table having Primary Key(column_name); Example Suppose we want to add a FOREIGN KEY constraint on the table ‘Orders1’ referencing to the table ‘Customer’ which have column ‘Cust_Id’ as the Primary Key. It can be done with the help of the following query − mysql> Alter table orders1 add FOREIGN KEY(Cust_id) REFERENCES Customer(Cust_id); Query OK, 0 rows affected (0.21 sec) Records: 0  Duplicates: 0  Warnings: 0   mysql> ... Read More