If you’re new to relational databases, and you’re trying to get your head around this concept of a “relationship” in your database, I hope this article helps.
relationships
Create a Relationship in SQL
In SQL, you create a relationship by creating a foreign key constraint.
More specifically, you have a parent table and a child table. The parent contains the primary key, and the child table contains a foreign key that references the primary key of the parent table.
When you use SQL to create a relationship, you can create the relationship at the time you create the table, or you can create it later (by altering the table). This article covers both scenarios.
How to Drop a Foreign Key in SQLite
Normally if you need to drop a foreign key in SQL, you’d use the ALTER TABLE statement. But if you’re using SQLite, that’s not an option.
Find Foreign Key Violations in SQLite
SQLite includes a PRAGMA statement that allows you to check for foreign key violations on a whole database or a given table.
The statement is PRAGMA foreign_key_check, and it works as follows.
Return a List of Foreign Keys in SQLite
In SQLite, you can use a PRAGMA statement to return a list of foreign keys for a given table.
Add a Foreign Key to an Existing Table in SQLite
SQLite supports a very limited subset of the ALTER TABLE statement. The only things you can do with ALTER TABLE in SQLite is rename a table, rename a column within a table, or add a new column to an existing table.
In other words, you can’t use ALTER TABLE to add a foreign key to an existing table like you can in other database management systems.
Therefore, the only way you can “add” a foreign key to an existing table in SQLite is to create a new table with a foreign key, then transfer the data to the new table.
There’s more than one way to do this, but there is a recommended way.
Create a Foreign Key in SQLite
When you create a table in SQLite, you can also create a foreign key in order to establish a relationship with another table.
This article provides an example of creating a foreign key when creating a table in SQLite.
What’s the Difference between a Graph Database and a Relational Database?
Graph databases have been gaining popularity over recent years as a viable alternative to the relational model. Graph databases are particularly well suited to storing connected data – data with lots of interconnected relationships, especially those that run many levels deep.
This article looks at the main differences between graph databases and relational databases.
Database Tutorial (Part 2) – Relationships, Adding Data, Querying Databases
This article is part 2 of the database tutorial.
Here, we cover:
- Relationships
- The different ways of adding data to a database
- Querying a database