How to DROP Tables with Foreign Key Relationships in PostgreSQL

by

Nothing pulls me out of the “zone” quicker than attempting to drop a table, only to be told “Nope, that table has a dependency!”.

Granted, this usually only happens in my development environments, as I rarely have any need to drop tables in a production environment.

And that’s probably why it “pulls me out of the zone”. I suddenly have to stop and start thinking about what tables contain foreign keys to the one I’m trying to drop. In dev environments, we’re often trying things out and so it’s not out of the question that we might inadvertently try to drop a table without realising it has dependent objects.

Fortunately, when we’re working with PostgreSQL, we have a quick and easy method to overcome this barrier.

Read more

Introduction to the Enum Data Type in SQL

by

The Enum (enumeration) data type in SQL is a specialised string object that allows us to define a list of predefined values for a column. This data type can be particularly useful when we want to restrict the possible values that can be stored in a specific column to a fixed set of options.

Read more

How to TRUNCATE a Temporal Table in SQL Server

by

Temporal tables in SQL Server provide a powerful way to track historical data changes. However, when it comes to clearing out data from these tables, the standard TRUNCATE statement doesn’t work the same as it does on normal (non-temporal) tables.

This article will guide you through the process of truncating a temporal table while maintaining its integrity.

Read more

How to Specify your own Subscript Range when Creating an Array in PostgreSQL

by

By default, PostgreSQL arrays are one-based. This means that we need to use 1 if we want to reference the first element in the array, 2 for the second, and so on.

But we also have the option of specifying our own subscript range for an an array. For example we could create a zero-based array, a ten-based array, or even a negative value such as a negative ten-based array.

We can do this by using subscripted assignment to specify the actual subscript range for the array. Basically, we prefix the array with the subscript range, enclosed in square brackets, and an equals sign (=) between it and the array.

Read more

Understanding the SQL Subquery

by

One of the things I love about SQL is just how easy it is to write a simple query that returns a meaningful result set, without having to a whole bunch of complex code. And I imagine most beginners are relieved when they discover this too.

However, while basic queries can go a long way, there’s a whole world of advanced techniques that can take our SQL development to another level. Somewhere in that world, would be the SQL subquery.

In this article, we look at the SQL subquery. We’ll explore what subqueries are, how they work, and when to use them. We’ll also look at some simple examples to demonstrate their use.

Read more

10 Essential Database Concepts that All Beginners MUST Learn

by

When I first started building websites 25 years ago, everything was static. Our primary building block was HTML, with a bit of CSS and JavaScript sprinkled in for good measure. Our content would be incorporated into the HTML documents, and so there was no separation of functionality and content.

Regardless, I was just happy to be able to build these amazing things that I could publish for the world to see.

But within a few years I had quickly learned about the power of databases and how they could completely transform the websites we were building at the time.

Read more

SQL Truncate Explained

by

In SQL databases, the TRUNCATE statement can be used to quickly remove all data from a table. Unlike the DELETE statement, TRUNCATE is typically faster and uses fewer system resources, especially for large tables.

In this article, we’ll explore the TRUNCATE statement, its usage, and provide some examples.

Read more