In production database setups, mysql replication is commonly used to duplicate a single database over many database servers. The primary database is referred to as the master database, while the other replicated databases are referred to as slave databases. To put it another way, we can use it to duplicate a database. Replication is used… Read More »
Database schemas Databases are present in almost all kinds of application that need to store information in some form or the other. Web applications like blogs, cms, social networking sites or business applications etc all have a database along with the code. The database design or schema determines how the tables and their relationships are… Read More »
The autoincrement feature in mysql allows us to create numeric fields that automatically increment by 1 for every row inserted. It does it atomically so we don’t have to worry about any duplicates or collision. However, there are situations when we need an autoincrement field to reset at regular intervals like every month or so…. Read More »
information_schema The following query will list out the foreign keys in mysql. It finds it out from the information_schema database. select concat(table_name, '.', column_name) as 'foreign key', concat(referenced_table_name, '.', referenced_column_name) as 'references' from information_schema.key_column_usage where referenced_table_name is not null; The output is a clean table listing out all foreign keys from all databases +———————–+————-+ |… Read More »
The innodb storage engine supports foreign keys in Mysql. To create foreign keys in phpmyadmin : 1. Convert both tables into innodb, if they are not already. 2. View the structure of the table which will have a foreign key. Make the referencing field an INDEX. 3. Now come back to structure view and click… Read More »
Views and mysqldump A little back I tried to backup a database from my webhost and restore it on my localhost mysql. It had a few views. The import of the sql file seemed to work fine. But on accessing the views in phpmyadmin mysql gave the error : #1449 – There is no 'projects'@'localhost'… Read More »
While creating a table a particular column / field can be made autoincrement as : CREATE TABLE students ( id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), name VARCHAR(24) NOT NULL, address VARCHAR(1024), CONSTRAINT primary_key PRIMARY KEY (id) ) ; The value of an autoincrement column increments automatically with… Read More »
Altering the sequence of fields of a table was a feature I was looking for in OpenOffice Base. It is not directly possible to drag the columns or fields and alter their sequence. We need a sql workaround to change the order of the columns. Lets say the sequence or order of the columns is… Read More »