Author: admin yep

Whether you’re conducting market research, building your portfolio as an analyst, or seeking insights to expand your market reach, valuable and reliable data is essential for informed decision-making.

However, searching the internet for free, reliable, and accessible data has some challenges. This article will make your data-hunting quest less challenging by introducing you to some of the top websites to access free data.

Google Trends is a free service developed by Google that provides users with unfiltered data samples of search requests made to Google. While this service displays time series data from 2004 to the present at both global and city-level scales, it doesn’t show the personal details of the search engine users.

You can also restrict the data to focus on categories, languages, entities, or trending searches on Google with Google Trends. Examples of available data include daily search trends and real-time search trends, which show data for the past seven days.

FiveThirtyEight is a data journalism website that has data about poll analysis, sports, pop culture, politics, science, and economic happenings.

The great thing about the website is that you can download the data from their website or their official GitHub repository and use your data visualization tools to create captivating data journalism visuals for your audience. A few examples of interesting data available include the world cup predictions and 2022-23 NHL predictions data.

BuzzFeed News is an American breaking news and original reporting platform that reports everything from journalism, tech, entertainment, celebrity news, culture, and DIY hacks to health and politics.

On its GitHub, BuzzFeed News makes its dataset, tools, and analysis from BuzzFeed’s newsroom open-source, accessible, and available. An example includes the FBI NICS firearm background check data.

Data.gov is the United States government’s open data website that hosts over 250,000 publicly available, well-documented datasets from international and multiple federal government agencies. The idea behind this initiative was to provide an open and transparent government.

You can access data from the website based on topic and agency or organization. Some examples of data you can find on Data.gov is the national student loan data system and electric vehicle population data.

Kaggle is a public data playground acquired by Google that offers a wide range of datasets on various topics. This community platform allows you to share your codes, learn, collaborate with fellow data professionals, and skill up. Kaggle also hosts data science competitions where you can win various prizes.

This guide provides a beginner’s guide on how to get started with Kaggle for data science. An example is the Global YouTube Statistics 2023.

EarthData is a data initiative by NASA serving as a repository of earth data from 1994 to now. You can get data related from the remote satellite information to data about the Earth’s atmosphere, ocean, and Terrestrial Hydrosphere.

You can browse various topics and access data like extreme heat data. However, you will have to explore NASA’s planetary data system for non-earth data.

IMDb provides data about movies, TV series, home videos, podcasts, video games, streaming information, and celebrity content. An example is IMDb non-commercial datasets.

AWS Public Dataset is a website that hosts over 3000 data sets of datasets made publicly available through AWS services. Most of the datasets here are project-based. A few include the cancer genome atlas and Foldingathome COVID-19 Datasets.

Inside Airbnb is a watchdog website launched by Murray Cox. This website sources data publicly available from Airbnb, a platform that offers users budget-friendly rooms worldwide. You can use information from this site to carry out analytics like the rental analytics of Montreal.

Google Dataset Search is a dataset search engine created by Google that hosts over 20 million datasets. Like their search engine, you can get data from almost anything. A good example is the Canadian national long-term water quality monitoring data.

UC Irvine Machine Learning Repository is the home of 624 datasets for the machine learning community in the world. This website has a strong reputation in the community because the datasets are categorized based on the machine learning tasks they are suited for. An example is the Iris dataset, a famous classification and clustering model dataset.

Datahub as a platform has many datasets that cover a wide range of topics like the 10-year US Government Bond Yields (long-term interest rate). Besides the data, they also display data tools and toolkits that can come in for data professionals.

This is the first website on our list for exclusive health data. The Global Health Observatory serves as a data repository displaying health-related statistics for over 1000 indicators for the WHO’s 194 member states. The data are recorded to monitor these member states’ progress toward SDG goals. You can get data by filtering the theme, category, metadata, and indicator of the data.

This platform is really niche based. It shows research data and market intelligence information like the weekend box office figures and related data on the UK film industry.

GitHub is more than just the home of millions of collaborative and open-source projects. The platform also hosts many repositories that aim to hold free, public, and open-source datasets. Even BuzzFeedNews has an open-source GitHub repository.

Other examples are the Awesome Public Datasets repository and do you even lift the dataset. You can also contribute to these open-source projects on GitHub.

Data.world is a data community and collaborative platform that hosts data projects and datasets. While a few datasets are paid, majorly of the data on the platform, like Makeover Monday’s 2021/W16: monthly air passengers in America, are free and can be easily downloaded locally or accessed via their API.

World Bank Open Data is a catalog of global economic and development data. You can browse and filter the data, like the global statistics on the cost and affordability of healthy diets by indicator and country.

Nasdaq Data Link is for everything financial, economic, and alternative data. You can access data like the US federal reserve data releases via a spreadsheet like Excel or an API.

NYC Taxi and Limousine Commission data platform records and hosts information such as yellow and green taxi trip records across New York City. The great thing about this website is that it shows information about everything, from the pick-up/drop-off to the taxicab zone and trip fares.

Academic Torrents is a data catalog of over 127.15 TB of research data. It was built, as they say, for researchers and by researchers.

Explore and Learn

Hopefully, with this list, you can get data that can shape your business landscape, drive your market research, gain a competitive edge, and help you build that unique data portfolio free of charge. So embrace the opportunities, explore, and have a less challenging data-hunting quest.

Right on cue, Meta has shared its latest AI drop with the world, and this time, the company is letting anybody get their hands on a bot that will write, debug, and describe code in a multitude of coding languages.

As reports last week first hinted, Meta’s Code Llama is a full-on code-generating AI that uses natural language to create and describe code in multiple coding languages. Like most of the AI products Meta’s released as of late, the model is open source and is free to use both personally and commercially.

Meta tried to imply it wouldn’t completely replace programmers, instead calling it a tool to “make workflows faster” and “lower the barrier to entry for people who are learning to code.” The program can both create and debug code, it also can comprehend and provide text explanations for questions regarding different programming languages.

It supports languages including C++, Java, PHP, Typescript, Bash, and C#. There is also a specialized version of the model called Code Llama – Python, which is custom-designed for programming in what’s become one of the most commonplace coding languages.

There’s also The Code Llama – Instruct model which is better at comprehending natural language instructions. Meta said those looking to generate code should use the Instruct model since it’s “fine-tuned to generate helpful and safe answers in natural language.” That emphasis on safety is interesting, as previous coding bots have had mixed results creating workable code, and that’s not even mentioning researchers who have proved other bots like ChatGPT and Bard have been manipulated to create malicious code. Meta does have an acceptable use policy for its AI about generating malicious code, malware, or computer viruses.

As for the dangers of using the AI to produce harmful content, Meta said it red-teamed the program in an attempt to force it to produce malicious code and found “Code Llama answered with safer responses” compared to ChatGPT running on GPT-3.5 Turbo.

The model is based on the framework of Meta’s Llama 2 language model. The company said it further trained the LLM on “code-specific datasets.” According to Meta’s blog post, the model accepts both code and language prompts. Users are also able to tell the model more about its existing codebase, which should turn out more personalized responses.

Code Llama’s three different versions of the AI scaled with more parameters, with 7 billion, 13 billion, and 34 billion parameter versions available. Parameters are usually a marker for an AI’s overall capabilities of producing accurate results. The smaller the model, the more easily it can run on single GPUs. Meta also mentioned that the smaller models are faster and may be better for “real-time code completion.”

According to the blog post, the Code Llama 34B parameter version scored similarly to OpenAI’s GPT-3.5 on several tests like HumanEval that evaluate the capabilities of LLMs. The AI was far below what GPT-4 could do on HumanEval, but it did better than some other open source coding-centric models like Palm-Coder and StarCoder.

The model is akin to the Microsoft-owned GitHub Copilot and Amazon’s CodeWhisperer, though Copilot costs money after a 30-day trial and CodeWhisperer is only free for individual use. These kinds of models are reportedly popular among programmers, with Microsoft claiming that 92% of programmers at large companies are using AI to some extent.

It’s not all gravy. Meta is specifically stopping short of saying what’s in Llama 2’s training data, and for good reason. Some developers have already sued Microsoft and GitHub alleging the company trained the AI on their code, ignoring their licenses.

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.

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.

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.