Navicat Blog

A Guide to MySQL Foreign Key Constraints Jun 2, 2023 by Robert Gravelle

During the process of normalization, groups of fields that represent a distinct entity are removed from a larger and/or more central table to a separate one. Common fields (usually IDs) are then employed to maintain their relationship. We can see an example below:

film_id_fk (34K)

In relational database, referential integrity between tables is enforced using foreign key constraints.

This blog will cover how foreign keys work as well as how to create a foreign key constraint in MySQL using Navicat 16 for MySQL .

Creating Views in Navicat 16 May 19, 2023 by Robert Gravelle

As part of the process of normalizing database tables, redundant columns are extracted from higher level tables into separate subsidiary ones. This often occurs due to some fields having a one to many relationship with the parent entity. For example, take the following model that was generated using Navicat Data Modeler:

ups_model (189K)

Multi-Version Concurrency Control in PostgreSQL May 12, 2023 by Robert Gravelle

Whereas most database systems employ locks for concurrency control, PostgreSQL does things a little differently: it maintains data consistency by using a multi-version model, otherwise known as Multi-Version Concurrency Control, or MVCC for short. As a result, when querying a database, each transaction sees a snapshot of data as it was some time before, regardless of the current state of the underlying data. This prevents the transaction from viewing inconsistent data that could be caused by other concurrent transaction updates on the same data, and provides transaction isolation for each database session. This blog article will provide a brief overview of how the MVCC protocol works as well as cover some of the pros and cons of the MVCC approach.

Setting Query Timeouts in PostgreSQL May 5, 2023 by Robert Gravelle

At the top of Navicat Monitor 3's Query Analyzer screen, there's a chart that shows queries with the longest wait times:

Screenshot_Navicat_Monitor_LongRunningQueries (102K)

It's essential to identify laggard queries because they can bring everything crashing to a crawl.

Implement Audit Trail Logging Using Triggers Apr 28, 2023 by Robert Gravelle

The idea behind database auditing is to know who accessed your database tables and when, along with what modifications were made to them. It's not only considered to be the standard minimum requirement for any enterprise level application, but is also a legal requirement for many domains such as banking and cybersecurity. Database Audit Trails are essential in investigating all sorts of application issues such as unauthorized access, problematic configuration changes, and many more.

In today's blog, we're going to add logging to the MySQL Sakila Sample Database to audit the rental table. It's a key table because the database represents the business processes of a DVD rental store.

Navicat Blogs
Feed Entries
Blog Archives