Month: August 2023

Setting up an environment to practice SQL can be challenging for beginners. Even experienced programmers may want to run queries without setting up a database.

SQL online platforms are the best choice for this. It provides a development environment to run, manipulate, and test SQL queries. These free and interactive platforms provide development environments like that of a database. Here are the best online SQL playgrounds to run and test your SQL queries.

SQL Fiddle is one of the best choices for practicing SQL queries. It has a user-friendly interface that makes it easier to run SQL queries and commands. The interface has panels that separate the workspace and the output. It’s best for running short queries.

First, you must build a schema for the internal database by clicking the Build Schema button on the left panel. Then, write and run your queries on the right panel.

You will see the output at the bottom of the left panel. If there are errors in your code, SQL Fiddle notifies you to edit the code and rerun it.

You can also expand the screens to a preferred size and use the query terminators provided. At the bottom of the screen, you can view the structure of the database schema.

You can run queries for various SQL databases, including Oracle, PostgreSQL, MySQL, and SQLite. You can pick a database by selecting it from the drop-down menu on the navigation bar.

You can use DB Fiddle to test your SQL queries. The playground provides SQLite, MySQL, and PostgreSQL databases to work with.

The interface is simple and easy to use. They have sample queries to show you how to use their workspace. The panels separate the working environments and a navigation bar.

You can create your own schemas and SQL database tables. Then, run the queries with the click of a button. The panel at the bottom of the page displays your results.

You can collaborate with others on the platform in real-time. You can also change your code into Markdown format and fork code from other repositories.

DB Fiddle is free, but you can pay for the pro version to access advanced features like SQL assessments.

You can access the interface without having to sign-up. But you must create an account if you need to save your work. You can convert your workspace into private mode if you want to keep your work private.

DB Fiddle UK provides a simple and easy-to-use interface to run your queries. They support 10+ relational databases, including MySQL, PostgreSQL, and MariaDB. You are free to choose the version of the DB engine you want to work with.

You can quickly create a workspace by clicking the add batch button on the left of the page (with a plus sign on it). Then, you can run as many queries as you want. DB Fiddle UK allows hiding private data or leaving it public.

You can change your data into Markdown format on the interface. Also, you can highlight important parts of your code using their highlighting tool.

You don’t need to sign-up for the platform to interact with it; you can start working on it immediately.

SQLite Online provides a productive workspace for you to run SQL queries. You can work with three databases, namely MariaDB, PostgreSQL, and MySQL database engines. You can quickly write and run queries on the interface.

To work with a specific database, click on the database name provided on the left pane. SQLite Online will then open that workspace for you. If you have private data that you don’t wish to share with the public, you must sign up and save your work on the platform.

You can connect to your DB remotely and run queries in the workspace. You also have the option to connect to other online DBs.

SQLite Online allows you to import data sets to work with and equally export them as CSV, XML, JSON, or SQL schema format.

Paiza provides a dynamic playground to run and test MySQL queries. It supports over 20 programming languages, including PHP, Rust, Python, and Elixir. For beginners, this is a great platform to learn MySQL concepts.

Pick the language you want to run your queries, and the website will provide the workspace for it. The MySQL section provides a database engine to create tables, insert and select data.

You can use the workspace without signing up. But if you need a work record, register and create an account on the platform. You can import code from GitHub and run it on Paiza.

Also, you can collaborate on projects with your team on the platform. You can keep your workspace private or public for others to access. Paiza also provides a cloud platform to create and host your applications.

Programiz is a great platform to learn SQL interactively. The website provides everything you need to learn and practice SQL queries. As a beginner, you will learn from SQL basics to more advanced concepts while practicing on the interactive editor.

You don’t require prior knowledge; you can start learning from scratch. You can use the editor to create tables, insert new data, delete, and run other SQL operations.

Programiz tests your knowledge with sample data sets you can play with on the code editor. As a beginner, you can query the sample data code editor as you learn SQL.

The site has a comprehensive SQL course for which you can sign up and learn detailed SQL concepts. This site provides the guidance you need to begin your career as a database engineer.

How to Use Online SQL Playgrounds

Online SQL playgrounds are great platforms for learning and practicing SQL. These playgrounds might not fully replicate the complexity of real-world scenarios. But they give you an idea of how SQL works.

But you should be careful about the data you share on the platform. Don’t share any sensitive information that may be harmful if they get into the wrong hands. You should also set up a local instance and learn SQL concepts like string functions.

A wealth of first-person shooters from the period’s golden era have seen remasters lately. Now comes one of the true greats: Star Wars: Dark Forces Remaster.

Nightdive Studios, which has been showing everybody how to do justice to classic shooter upgrades recently with its remasters of Quake II and System Shock, is using that same KEX Engine to give just enough modernization, but not too much, to the LucasArts title that was even better than its Doom-but-it’s-Star-Wars promise.

In the notes and footage of its reveal trailer, Nightdive promises 4K/120 fps gameplay, modern gamepad support, trophies and achievements, remastered cutscenes, and, of course, blasting Storm Troopers that have markedly better aim on a monitor than they do on film. The remaster is “coming soon” to PS4/5, Xbox One/X/S, Nintendo Switch, and Steam on PC, with “a release date announcement later this year.”

When LucasArts shut down in 2013, following Disney’s purchase of George Lucas’ empire, Lee Hutchinson offered his remembrance of Dark Forces:

Dark Forces was a fine shooter in its own right and looked amazing, but that Star Wars license rocketed its appeal right up into outer space. Dark Forces promised something irresistible to any geek: the ability to jump into the Star Wars universe and run around. Released in 1995, the game was LucasArts’ first foray into the nascent FPS genre. The company set the bar awfully high.

As Hutchinson noted, and which fans likely remember, there were only hints of Jedi-dom in Dark Forces; you never got your hands on a lightsaber, and you never force-pushed anyone off a ledge. The later Jedi Knight games fixed that. Dark Forces also faced the same memory and texture-resolution challenges as other shooters of its time, but it had the advantage of its setting. Imperial ships and bases had always looked stark, drab, and oftentimes quite empty in the Star Wars films (also due to certain constraints). So when a TIE Fighter hangar challenges you with only a handful of goons in a sterile space that looks like it could hold 300, that’s not a flaw; that’s George Lucas’ budget-minded used-future aesthetic!

Larry Kuperman of Nightdive told IGN that the game should still feel like the original felt, and that means difficult. The title should be “popularly priced,” Kuperman said, which indicates something well below the typical AAA $60/$70 mark.

We’ll keep an eye out for the first signs of a release date on this one. And we’ll bide our time until Jedi Knight II: Jedi Outcast makes it into the industry’s remaster/revenue queue.

Next.js is a powerhouse of a full-stack web framework with features like page-based routing, static-site generation, and dynamic React components, but what if I told you that you could get all of that with just PHP?

Laravel is a PHP framework that’s been in development for almost a decade. In that time it has solidified itself as one of the most-loved options for building web applications powering the needs of both startups and some of the largest companies around. It also has amassed a massive community of individuals and organizations that provide world-class packages and libraries to enhance its already renown developer experience.

Within the last year or two, first-party upgrades to the framework and package developers have added a suite of functionality to make Laravel a powerhouse for developing truely full-stack web applications fast and efficiently. In this article I’m going to go through some of the biggest features that Next.js is known for, and offer up examples of similarities found in the Laravel framework.

This isn’t meant to be a “who’s better” between the two, but instead a showcase of what Laravel can do out of the box. Let’s start!

Page-based routing

Next.js is famous for its simple and intuitive page-based routing system. But did you know that Laravel has a similar functionality now? Up until this year, if you wanted to create a new route in Laravel you’d have to specify it in a main routes file. However, a first-party package called Laravel Folio was recently released that enables purely page-based routing, and it works surprisingly similarly to Next.js.

Let’s take a look at an example file structure:

resources/
└─ views/
   ├─ pages/
   │  ├─ index.blade.php
   │  ├─ uses.blade.php
   │  └─ blog/
   │     ├─ index.blade.php
   │     └─ [slug].blade.php
   ├─ dashboard.blade.php
   └─ welcome.blade.php

In the above, anything under the resources/views/pages follows the convention of page-based routing. So the uses.blade.php file’s content will be visible at our site under /uses .

An index.blade.php file is used to denote the default root view for that particular directory. That means that the blog/index.blade.php content will be visible at /blog. This should seem straightforward and similar to Next.js.

Except, what about dynamic routes? Well, that’s covered too!

In the above example, you’ll see a [slug].blade.php file. This creates a dynamic route where anything visited under the blog route will be resolved by this view file, with a $slug variable being included that contains the data passed through to the route.

For example, visiting /blog/my-example-post will resolve to that [slug].blade.php page, and if you referenced a $slug variable in it, will contain the value “my-example-post”. This can then be used with an internal function to grab a related model, make an API request, or however else you want to use the value passed through.

But we can take dynamic routes one step further.

For our blog, if we were using a Laravel model called Article to hold data for a single blog article, we can actually use route-model binding in our dynamic page-based routes by just renaming that file to [Article].blade.php. Now inside the blade file, the variable $article will be available, containing the entire Article model’s attributes!

Static-site generation

Next.js recommends using static generation whenever possible, and it’s enabled by default on most pages. Basically anything that doesn’t require heavy user interaction can be statically generated on build time and then served up via your server (or better yet, a CDN).

By default Laravel generates every route dynamically. It’s not slow since there is a route cache which helps cut down on a lot of the PHP interpretation, but the fact still remains that a Laravel app relies on dynamic markup generation.

We can change that though!

There’s a package, laravel-export, that comes from a well-respected third party. After installation there’s zero necessary configuration before our entire site can be statically generated with a single command, php artisan export.

This will crawl our site, following links on pages, and save the rendered markup to a dist folder in our application’s folder structure. It also works well with the Folio page-based routing mentioned above.

Like with Next.js, we can modify the behavior of this through some small configuration changes in our app.

We can specify paths to exclude from the crawler, programmatically turn it on or off, and write hooks that fire off commands before or after the export runs (for example, if we wanted to deploy the exported site files to a production server).

Unlike Next.js, there’s no equivalent of a getStaticProps method that runs when building the exporter. You can always include a function in the body of the Blade template if you’re using something like Folio, which will run as each page is compiled, although that’ll also occur outside of the exporting as well.

Server-side rendering

While using Next.js you need to include a getServerSideProps method to enable SSR, but PHP comes with server side rendering on by default!

In normal circumstances, PHP is rendered on each request through the server. This means that writing PHP code in your blade files guarantees that the content of your functions will be hidden from the generated markup that comes back to the browser. Those files are never accessed directly, and so this adds a layer of security to your application by default.

Going deeper than just blade files, Laravel is a full MVC framework and so includes things like Models and Controllers out of the box that can be used to organize your server-rendered code. Authentication is also baked in by default, and with first-party packages like Breeze, Sanctum, or Socialite, you can include user registration, login, API-based authentication, social sign-ups, and role-based permissions with near zero configuration.

A similar out of the box piece to look for is a database. Laravel includes an abstraction layer called Eloquent that makes it easy to interact with your database of choice. It’s a full ORM that can be used to create, read, update, and delete records, and can be used to build out more complex relationships between models.

Single page components

Some of the beauty of Next.js comes from the fact that you’re using the React library, and all of the wonderful developer experience that comes with it. This enables you to easily build dynamic user interfaces and responsive views with relative ease.

Everything can also be self-contained inside a single page component. Can this be done inside of our Laravel app since everything is server rendered by default?

Up until recently we’ve had to install and configure a frontend framework to get that functionality, but that requires a separation of concerns and the maintanence of two different codebases. Instead, we can use LiveWire and Volt to give us dynamic, single-page components with just PHP.

Let’s say that in our [Article].blade.php page mentioned way above that after each page’s content we have a subscription form. We can use these two packages to build a dynamic component into our existing page using PHP and Laravel. It might look like this.

<?php

use App\Livewire\Forms\SubscribeForm;
use function Livewire\Volt\{form};

form(SubscribeForm::class);

$save = fn () => $this->form->store();

?>

<form wire:submit="save">
    <input type="email" wire:model="form.email">
    @error('form.title') <span class="error"></span> @enderror

    <button type="submit">Subscribe</button>
</form>

Now when our page is visited or rendered, we’ll have a form at the bottom that the user can fill out and submit without needing to perform a full page refresh!

Unlike Next.js though, this interactivity is dependant on server rendering. LiveWire uses JavaScript on the frontend to hydrate these components and provide client-side interactivity, but the core functionality and reactivity depends on the server to manipulate state and perform the functions requested.

Development environment included

With Next.js, you have an included development server that’s ran with npm. With Laravel, there’s a few different options for a local development environment.

First, if you have PHP installed locally on your system, then it’s as easy as running:

php artisan serve

This boots up a local PHP server instance, included in the actual language itself. It’s a bit limited, blocking by nature and without a database, but it gets the job done and is responsive enough to use for 90% of local development cases.

If you want something more complex with additional features like a MySQL server, Redis instance, or Mailpit box, then you can use the first-party Laravel Sail which comes pre-installed to new Laravel apps. It’s a bash script wrapper for Docker that boots up a network of containers and handles your local dev environment setup.

Ease of deployment

Alright, you’re ready to release your application to a production environment, now what? Let’s assume that we have a mixture of static content and server-side rendered routes, which means we’ll need to have a setup that can run our applications dynamically.

For Next.js, this means that we need to have a server provisioned with Node.js. Something like a basic Amazon EC2 instance or a DigitalOcean Droplet can handle that, and the installation and configuration is pretty straightforward. What about Laravel?

Since Laravel runs on PHP, and PHP has been around for decades, getting a server provisioned with the requirements for a LAMP (or LEMP) stack is not too difficult of a task. There’s a plethora of options available, from shared hosting to VPS providers. Even managed services like Laravel Forge that can handle the provisioning and configuration of your server for you, similarly to how Next.js has managed application instances with Vercel.

What else?

This article wasn’t meant to be a competition between Next.js and Laravel. They’re both fantastic batteries-included, full-stack web frameworks that enable you to build applications and ship awesome features.

However, if you’re looking for an alternative to Next.js, maybe with something that’s a bit more batteries-included, Laravel could be what you’re looking for. With a robust ecosystem, a passionate community behind it, and the ubiquity of a language powering over 70% of the web, it’s definitely an option to keep in mind.

If you’ve been searching for remote tech freelance jobs without success, it’s possible you haven’t explored the right job portals. Freelance tech jobs that pay well are a niche category of jobs, and you need to know where to look for them to ensure success.

Some well-known job portals facilitate and offer well-paying freelance tech jobs for aspiring candidates. You can check out some of these job portals and apply for suitable projects.

Toptal, an acronym for Top Talent, advertises itself as a recruitment board catering to the top 3% of candidates applying for freelance technical jobs. The portal’s screening process entails multiple rounds, making hiring suitable talent for the open positions easier.

The rounds are split into multiple categories, as follows:

1. Language and Personality: The first round checks the candidate’s English language and communication prowess.
2. In-Depth Skill Review: Since you are applying for a technical role, your problem-solving and technical skills will move you to the next round.
3. Live Screening: If you pass the first two rounds, you move to the Live Screening round, wherein Toptal screeners test your skills on live exercises to gauge your expertise for the role.
4. Test Projects: Test projects are the fourth stage of the evaluation process and can take one to three weeks. During this test, you must demonstrate competence, professionalism, and thoroughness within your projects to impress the screeners.
5. Selection: Finally, if you complete all the steps, you move on to the possibility of applying for the available jobs on the network.

As the name aptly suggests, Authentic Jobs is a targeted job board for web and creative professionals looking for remote jobs. The company has been headquartered in Hingham, Massachusetts, ever since its inception in 2005. Since Authentic Jobs is a global job platform, it connects IT designers and developers with interested employers worldwide.

As an interested freelancer, you can apply for various jobs, such as freelance, contractual, full-time, and internship positions. Some additional features include a job monitoring dashboard, flexible payment options, and a 30-day full refund policy. If you have the basic skills of a freelance tech analyst, you can succeed in all your endeavors.

Since the pandemic, the remote working option has become an ever-increasing hit with freelancers and the public. We Work Remotely is one of the largest remote working websites, with a whopping 4.5 million visitors.

If you are looking for a technical job, you can choose from the following list of technological niches:

• Programming
• Design
• DevOps
• Product

The list is not exhaustive, as the job board offers many other options for non-technical groups. There are a few job categories, such as contractual and full-time positions. As a job seeker, you should choose a futuristic tech job with high earning potential

Remote OK offers plenty of jobs for Developers, Designers, Sales Professionals, and Project Managers. The job platform is rapidly becoming one of the top-notch job boards for aspiring freelancers looking for a steady freelance gig.

Its tech focus and job flexibility strongly focus on its Dutch roots. Remote OK has tie-ups with well-known brands, including ESPN, easyJet, Amazon, Microsoft, Stripe, etc. While Remote OK offers tech jobs, some non-tech jobs are also available to ensure everyone benefits equally from the portal’s offerings.

Many remote tech job seekers are familiar with freelance platforms like Upwork, Freelancer.com, and Fiverr. These websites are online marketplaces where employers update their jobs, and freelancers can pitch for their favorite positions.

Once an employer identifies a suitable candidate and agrees on the proposed rate, they can hire the freelancer and establish milestones for the task. Each pitch differs in its monetary value, so there is no set amount for each technical job.

However, what stands out is that these platforms charge a hefty fee from the freelancers, making them a little expensive to be associated with.

Like many freelancing portals, Flexiple connects freelancers with employers seeking technical expertise. Freelancers have to undergo elaborate proficiency tests. As a freelancer, your profile and skills are shared with employers once you clear your tests. This process helps bridge the gap between talents and job requirements.

Some common job profiles revolve around the following language requirements:

• JavaScript
• LAMP
• ReactJS
• AWS
• Vue.js
• C#
• Swift
• C++
• Go
• Kotlin
• PHP

There are plenty more languages on this list. Flexiple advertises tech jobs that require coding and programming skills only. So make sure you have a solid background in the required languages before investing your time and effort in completing your application.

If you are not getting high-paying tech jobs, you are not on the right platform. As a freelancer, targeting the most searched and high-performing websites, such as Arc(), is always advisable, which promises to be better than their competitors.

Arc vets developers basis their capabilities by putting them through various screening rounds. For example, your application goes through the following stages:

1. Manual profile screening
2. Self-introduction video
3. Technical Interview & Pair Programming
4. Final Candidate Review
5. Candidate Onboarding
6. Ongoing Review

Arc proudly states that only 1.1% of developer applications meet their standards, which proves that only the best are given some of the highest-paying remote tech jobs in the market.

LinkedIn is a famous job engagement and enrichment platform which caters to all technical and non-technical jobs for freelancers and full-time professionals. You can connect with different hiring companies, follow people from a network, or apply for your favorite job; the choice is yours.

You can always do a little extra on LinkedIn via its job portal. The global platform has become the hub for people looking to hire and collaborate with people from all professional realms, making it one of the most revered job portals on the internet.

Technical Jobs and Their Associated Remote Working Mistakes

When looking for good, well-paying remote tech jobs, nothing can beat the internet. There is no dearth of options, from varied job portals to fancy marketplaces.

However, even if you have the required skill sets to land a good gig, you must avoid some of the most common tech career mistakes to make the most of the available opportunities. When you heed the job requirements, you can harvest the opportunity and create a spot for yourself in the technical world.

Teaching children to code at a young age helps them thrive in today’s technological world by developing skills in critical thinking, creativity, and problem-solving.

The Raspberry Pi, with its low price and ease of use, is a fantastic device for introducing young people to computing and programming. With applications in robotics, computer programming, and even home automation, it’s ideal for kids to learn to code.

1. Get Started With the Raspberry Pi

In an effort to make computers and digital creation accessible to everyone, the Raspberry Pi Foundation created the inexpensive and widely available Raspberry Pi computer. Several models and revisions have been made available since the first Raspberry Pi was introduced in 2012.

If you do not already have a Pi, you can get one for as little as $35. Be sure to get the flagship Raspberry Pi 4, although the older Raspberry Pi models have some merit. You can also purchase the Raspberry Pi 400, which offers much the same features as the Pi 4 and comes in a keyboard form factor.

The official Raspberry Pi 4 desktop kit costs more money but comes with most of the components you need to use the Raspberry Pi, including a keyboard, mouse, case, power supply, and microSD card. After that, all you will require is a compatible HDMI display that you can connect to the computer.

The microSD in the official kit comes preloaded with the Raspberry Pi OS, a Linux distribution created specially for Raspberry Pi computers. You can also follow our guide on how to install an operating system on a Raspberry Pi.

Your children can begin their coding journey as soon as the initial setup is complete. A good place to start is at the Raspberry Pi Foundation’s projects site.

2. Choosing a Kid-Friendly Programming Language

It is essential to choose a kid-friendly programming language in order to make programming enjoyable and interesting for your children. Scratch, Python, and Ruby are just a few of the most well-known choices.

Scratch

Scratch is an interactive programming environment that uses a block-based visual interface. It is one of the few languages designed to be used by children. Due to the visual programming style, Scratch is straightforward and intuitive to learn. Concepts introduced in Scratch can be applied to more advanced languages such as Python and Java.

The Scratch website has numerous tutorials dedicated to teaching you how to use the language. It is also possible to remix user-created projects on the website.

Aside from learning programming, Scratch can also serve as a springboard for your children to explore their artistic ideas in other areas of interest such as visual art and music.

Python

Python’s syntax is simple and intuitive for newcomers. The language is widely used: arguably, it’s the most popular programming language for the Raspberry Pi. For this reason, there are many resources available to help beginners get started.

It provides access to libraries of pre-written code that may be integrated into personal applications. This is a key benefit of Python as it makes it easier to interface with physical electronic components connected via the Raspberry Pi’s GPIO header and enables you to create advanced projects.

Ruby

Yukihiro “Matz” Matsumoto created Ruby in the 1990s in Japan. It’s a general-purpose language that’s been called “a programmer’s best friend” and has a lot in common with Python. It is also one of the easiest languages to begin programming with.

Like Python, Ruby’s syntax is both straightforward and expressive. It also does not rely on indentation to separate code blocks. It however places a greater emphasis on object-oriented programming than Python. It has waned in popularity over the years, but it is still an amazing language that is well-loved by developers and programmers.

Apart from the languages listed above, other excellent options for teaching your child coding include Lua, JavaScript, Swift, and Java (for older children).

3. Hands-On Projects and Challenges

To solidify their programming skills, it is important to encourage your kids to work on hands-on projects and challenges using the Raspberry Pi. Here are some ideas:

Create a Simple Game

A video game is a project that holds a lot of appeal for most children. There are several ways to make a gaming machine using your Raspberry Pi. You can start by creating a retro gaming console with a Raspberry Pi with no coding involved and then move on to the more engaging projects in our list of the best Raspberry Pi gaming projects.

Build an RC Car

This can be a good project if your child already has an RC car. It is possible to hotwire it so that it is controlled from the Raspberry Pi rather than the remote that came with it. The process is outlined in an Instructables guide that uses a Traxxas remote control car and a long-range Wi-Fi USB antenna.

If you would rather not go through the stressful but rewarding process of hacking an RC car, you can buy a Raspberry Pi RC car kit such as the PiCar-V from Sunfounder that already comes with programming functionality in the box.

Security Camera With Motion Detection

If your kid is interested in home security and surveillance technology, they can also find installing a motion-activated security camera to be an engaging pastime.

Using a Raspberry Pi and a Camera Module, kids can create a surveillance camera that records still images or video and can also detect motion. The Raspberry Pi can be programmed in Python to trigger a recording of every motion it detects and save the files to a USB drive or an online cloud storage service.

Our tutorial on how to create a multi-camera CCTV system with a Raspberry Pi and motionEyeOS is a great place to start.

4. Join Coding Communities and Clubs

Apart from choosing a programming language and working on projects, coding clubs allow your child to develop essential skills such as collaboration and communication faster.

You should encourage your children to join coding groups or other offline platforms that cater to young programmers in order to keep the momentum going and to build a sense of community. They can usually find collaborators and get advice from professionals in these settings. These groups also offer constant encouragement and help those new to coding develop their skills.

You can also try to look for a Code Club or CoderDojo near you. These organizations are supported by the Raspberry Pi Foundation, but they are not limited to Raspberry Pi hardware.

Help Your Kids Learn Coding Using a Raspberry Pi

The original intention of the Raspberry Pi was to make computers affordable and get more young people interested in coding. If your kids are eager to explore coding as a way to express their creativity and intuition, a Raspberry Pi can be a great investment to help them bring that vision to life.

With the aid of online resources, offline communities, and hands-on projects, the single-board computer can be a perfect tool for guiding your kids into the world of coding.

Key Takeaways

• Use SQL constraints to maintain data integrity and ensure uniformity in your database. The NOT NULL constraint forces columns to reject NULL values.
• Implement primary key constraints to ensure uniqueness of values in a column or set of columns. This prevents duplicate records in a database table.
• Foreign key constraints establish relationships between tables and prevent actions that would destroy links between them. They ensure that a record in the child table references the parent table.

A database is essential for many applications, but it can get messy if you don’t have guidelines for storing and processing data.

SQL constraints specify rules for storing data in a table. When you set constraints, the database will throw an error if you try to store data that violates these rules. Constraints help to maintain data integrity and ensure uniformity in your database.

There are several types of SQL constraints that you can use; here are some of the most useful.

1. NOT NULL Constraint

Database columns, by default, accept data with NULL values. NULL essentially means that no value exists. The NOT NULL constraint forces a column to reject NULL values.

This constraint ensures that each column must contain a value. You cannot add a record to the database without supplying data for any columns with the NOT NULL constraint.

Take the example of a Customers table. There are some necessary details about each customer that you want on record, like their name. Add the NOT NULL constraint to the mandatory fields to ensure that customers provide this information.

Here’s an example showing how you can use the NOT NULL constraint in a PostgreSQL database:

 CREATE TABLE Customers (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Mobile_No int NOT NULL,
    Age int
); 

If you try inserting a customer record without the Age field, the database will accept it with no errors:

 INSERT INTO Customers (ID, LastName, FirstName, Mobile_No)
VALUES (123456, 'Dior', 'Christian', 0723000000); 

However, if you try inserting a record without the FirstName field, the database will reject it with an error message:

 ERROR: null value in column "firstname" violates not-null constraint
Detail: Failing row contains (123456, Dior, null, 723000000, null). 

2. PRIMARY KEY Constraint

A KEY is a unique attribute set to a column or field that identifies a table’s tuple (or a record) in database systems. A PRIMARY KEY constraint ensures the uniqueness of values in a column or set of columns. It acts as a unique identifier in a row, preventing duplicate records in a database table.

Primary keys contain unique values and cannot contain NULL values. Each SQL database table must have only one primary key. The PRIMARY KEY can have single or multiple columns.

For example, you are creating a database of customer records. You need each customer to enter their ID numbers differently from everybody else. You can apply the primary key constraint to ensure that none of your customers have the same ID number.

The following code shows how you can introduce a primary key constraint in a MySQL database:

 CREATE TABLE Customers (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
   PRIMARY KEY (ID)
); 

The database will not accept that value if a user enters another record with a similar ID. Instead, it will throw an error indicating duplication. The user tries to insert two records with the same ID in the following example:

 INSERT INTO Customers (ID, LastName, FirstName, Age)
VALUES (1, 'John', 'Doe', 35 ); 

INSERT INTO Customers (ID, LastName, FirstName, Age)
VALUES (1, 'Mary', 'Jane', 35 ); 

The database will show an error message:

 Duplicate entry '1' for key 'PRIMARY' 

But if you change the ID of the second customer, the database accepts the entry. Therefore, the primary key ensures no duplicate IDs in your customer records.

3. FOREIGN KEY Constraint

Foreign keys establish relationships between two tables. You can add a foreign key to a field/column in one table that references the primary key in another table.

The table with the primary key is the parent table, while the table with the foreign key is the child table. A record cannot then exist in the child table without a reference to the parent table.

The foreign key constraint prevents actions that would destroy links between tables. For example, you cannot DROP one table if it links to another with a foreign key. You will have to drop both tables at once.

Unlike a primary key, you can duplicate a foreign key and have more than one in a single table. Foreign key values can also be NULL. In the following example, you must use the customer_id to create an order.

 CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

INSERT INTO Customers(customer_id, first_name, last_name)
VALUES (1, 'Christian', 'Dior');

INSERT INTO Orders(order_id, customer_id, order_date)
VALUES (1, 1, '2023-08-07'); 

If you try creating an order without an existing customer_id, the database shows an error message:

 Cannot add or update a child row: a foreign key constraint fails
(`db_9_4ee205c`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY
(`customer_id`) REFERENCES `customers` (`customer_id`)) 

4. UNIQUE Constraint

This constraint ensures that no two rows can have the same values for a specific column. Like primary keys, a unique constraint maintains data integrity and prevents duplicate entries. If you work with a poorly designed database without the UNIQUE constraint, you may end up having to find and delete the duplicates.

Unlike primary keys, you can have many UNIQUE constraints on one table. For example, when creating a Customers table, you may want to have unique IDs and phone numbers. To add such a constraint using MySQL server, use this syntax:

 CREATE TABLE Customers (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Mobile_No BIGINT UNIQUE 
); 

If you insert records with the same mobile number in the database, it will then show an error message.

 INSERT INTO Customers (ID, LastName, FirstName, Mobile_No)
VALUES (123456, 'Dior', 'Christian', 254000000 );

INSERT INTO Customers (ID, LastName, FirstName, Mobile_No)
VALUES (7891011, 'Dedan', 'Kimathi', 254000000 ); 

The error message will be something like this:

 Duplicate entry '254000000' for key 'Mobile_No' 

This UNIQUE constraint ensures the database won’t have customers with the same IDs or mobile numbers.

5. CHECK Constraint

The CHECK constraint limits the value range put in a column. Adding a CHECK constraint on a column will allow only specified values for that column. It enforces data integrity by ensuring a user only inserts valid data in a table.

The CHECK constraint must evaluate a value as TRUE or UNKNOWN for each specified row or table entry. If the value is FALSE, the database shows an error message.

For example, in the Customers table, you may want only to serve customers over 18 years. You can add a CHECK constraint to ensure you don’t serve underage customers. You may add the constraint in a PostgreSQL database, as shown in the following code:

 CREATE TABLE Customers (
    ID int NOT NULL,
    Age int CHECK(Age>=18),
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Mobile_No BIGINT UNIQUE 
); 

Now if you try to insert a customer’s age below the age of 18:

 INSERT INTO Customers (ID, Age, LastName, FirstName, Mobile_No)
VALUES (123456, 15, 'Dior', 'Christian', 1254000000 ); 

The database will show an error message like this:

 ERROR: new row for relation "customers" violates check constraint
"customers_age_check"
Detail: Failing row contains (123456, 15, Dior, Christian, 1254000000) 

How to Add and Remove SQL Constraints From Databases

SQL constraints are not cast in stone. You can add or remove constraints on existing tables using the ALTER TABLE statement. The ALTER statement allows you to work with constraints to suit your data needs.

There are many more SQL constraints you can learn to design a database to your liking. You can start with the ones listed in this article.

Key Takeaways

• Use SQL constraints to maintain data integrity and ensure uniformity in your database. The NOT NULL constraint forces columns to reject NULL values.
• Implement primary key constraints to ensure uniqueness of values in a column or set of columns. This prevents duplicate records in a database table.
• Foreign key constraints establish relationships between tables and prevent actions that would destroy links between them. They ensure that a record in the child table references the parent table.

A database is essential for many applications, but it can get messy if you don’t have guidelines for storing and processing data.

SQL constraints specify rules for storing data in a table. When you set constraints, the database will throw an error if you try to store data that violates these rules. Constraints help to maintain data integrity and ensure uniformity in your database.

There are several types of SQL constraints that you can use; here are some of the most useful.

1. NOT NULL Constraint

Database columns, by default, accept data with NULL values. NULL essentially means that no value exists. The NOT NULL constraint forces a column to reject NULL values.

This constraint ensures that each column must contain a value. You cannot add a record to the database without supplying data for any columns with the NOT NULL constraint.

Take the example of a Customers table. There are some necessary details about each customer that you want on record, like their name. Add the NOT NULL constraint to the mandatory fields to ensure that customers provide this information.

Here’s an example showing how you can use the NOT NULL constraint in a PostgreSQL database:

 CREATE TABLE Customers (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Mobile_No int NOT NULL,
    Age int
); 

If you try inserting a customer record without the Age field, the database will accept it with no errors:

 INSERT INTO Customers (ID, LastName, FirstName, Mobile_No)
VALUES (123456, 'Dior', 'Christian', 0723000000); 

However, if you try inserting a record without the FirstName field, the database will reject it with an error message:

 ERROR: null value in column "firstname" violates not-null constraint
Detail: Failing row contains (123456, Dior, null, 723000000, null). 

2. PRIMARY KEY Constraint

A KEY is a unique attribute set to a column or field that identifies a table’s tuple (or a record) in database systems. A PRIMARY KEY constraint ensures the uniqueness of values in a column or set of columns. It acts as a unique identifier in a row, preventing duplicate records in a database table.

Primary keys contain unique values and cannot contain NULL values. Each SQL database table must have only one primary key. The PRIMARY KEY can have single or multiple columns.

For example, you are creating a database of customer records. You need each customer to enter their ID numbers differently from everybody else. You can apply the primary key constraint to ensure that none of your customers have the same ID number.

The following code shows how you can introduce a primary key constraint in a MySQL database:

 CREATE TABLE Customers (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
   PRIMARY KEY (ID)
); 

The database will not accept that value if a user enters another record with a similar ID. Instead, it will throw an error indicating duplication. The user tries to insert two records with the same ID in the following example:

 INSERT INTO Customers (ID, LastName, FirstName, Age)
VALUES (1, 'John', 'Doe', 35 ); 

INSERT INTO Customers (ID, LastName, FirstName, Age)
VALUES (1, 'Mary', 'Jane', 35 ); 

The database will show an error message:

 Duplicate entry '1' for key 'PRIMARY' 

But if you change the ID of the second customer, the database accepts the entry. Therefore, the primary key ensures no duplicate IDs in your customer records.

3. FOREIGN KEY Constraint

Foreign keys establish relationships between two tables. You can add a foreign key to a field/column in one table that references the primary key in another table.

The table with the primary key is the parent table, while the table with the foreign key is the child table. A record cannot then exist in the child table without a reference to the parent table.

The foreign key constraint prevents actions that would destroy links between tables. For example, you cannot DROP one table if it links to another with a foreign key. You will have to drop both tables at once.

Unlike a primary key, you can duplicate a foreign key and have more than one in a single table. Foreign key values can also be NULL. In the following example, you must use the customer_id to create an order.

 CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

INSERT INTO Customers(customer_id, first_name, last_name)
VALUES (1, 'Christian', 'Dior');

INSERT INTO Orders(order_id, customer_id, order_date)
VALUES (1, 1, '2023-08-07'); 

If you try creating an order without an existing customer_id, the database shows an error message:

 Cannot add or update a child row: a foreign key constraint fails
(`db_9_4ee205c`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY
(`customer_id`) REFERENCES `customers` (`customer_id`)) 

4. UNIQUE Constraint

This constraint ensures that no two rows can have the same values for a specific column. Like primary keys, a unique constraint maintains data integrity and prevents duplicate entries. If you work with a poorly designed database without the UNIQUE constraint, you may end up having to find and delete the duplicates.

Unlike primary keys, you can have many UNIQUE constraints on one table. For example, when creating a Customers table, you may want to have unique IDs and phone numbers. To add such a constraint using MySQL server, use this syntax:

 CREATE TABLE Customers (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Mobile_No BIGINT UNIQUE 
); 

If you insert records with the same mobile number in the database, it will then show an error message.

 INSERT INTO Customers (ID, LastName, FirstName, Mobile_No)
VALUES (123456, 'Dior', 'Christian', 254000000 );

INSERT INTO Customers (ID, LastName, FirstName, Mobile_No)
VALUES (7891011, 'Dedan', 'Kimathi', 254000000 ); 

The error message will be something like this:

 Duplicate entry '254000000' for key 'Mobile_No' 

This UNIQUE constraint ensures the database won’t have customers with the same IDs or mobile numbers.

5. CHECK Constraint

The CHECK constraint limits the value range put in a column. Adding a CHECK constraint on a column will allow only specified values for that column. It enforces data integrity by ensuring a user only inserts valid data in a table.

The CHECK constraint must evaluate a value as TRUE or UNKNOWN for each specified row or table entry. If the value is FALSE, the database shows an error message.

For example, in the Customers table, you may want only to serve customers over 18 years. You can add a CHECK constraint to ensure you don’t serve underage customers. You may add the constraint in a PostgreSQL database, as shown in the following code:

 CREATE TABLE Customers (
    ID int NOT NULL,
    Age int CHECK(Age>=18),
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Mobile_No BIGINT UNIQUE 
); 

Now if you try to insert a customer’s age below the age of 18:

 INSERT INTO Customers (ID, Age, LastName, FirstName, Mobile_No)
VALUES (123456, 15, 'Dior', 'Christian', 1254000000 ); 

The database will show an error message like this:

 ERROR: new row for relation "customers" violates check constraint
"customers_age_check"
Detail: Failing row contains (123456, 15, Dior, Christian, 1254000000) 

How to Add and Remove SQL Constraints From Databases

SQL constraints are not cast in stone. You can add or remove constraints on existing tables using the ALTER TABLE statement. The ALTER statement allows you to work with constraints to suit your data needs.

There are many more SQL constraints you can learn to design a database to your liking. You can start with the ones listed in this article.

Apple has added a view to Reminders which shows you tasks in the Kanban column style. Here’s how to use it on the Mac, and also the iPad, plus why not to bother on the iPhone.

How to setup a Kanban list in Reminders on macOS Sonoma

Using the Kanban list

Where Reminders scores and falls short