Many to Many Relation

We saw the basic example of Many-To-Many Relation in the second last post from this, there we just focused on the concept of the relation and not so much on the structure of the junction table.

Let’s take a look at the schema again:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL
);

CREATE TABLE author_post (
    user_id INTEGER NOT NULL,
    post_id INTEGER NOT NULL,
    PRIMARY KEY (user_id, post_id),
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (post_id) REFERENCES posts (id)
);

Let’s now populate the tables.

One to One Relations

I might missed this basic relationship model as I haven’t really found it quite commonly used, but its still used in very specific examples.

That one relationship is one-to-one. As the name suggests, it maps one row to exactly one row.

Unlike the one-to-many relation which had one of the records/rows from the tables or entities connected by multiple ropes, both the records/rows from the tables or entities are connected by only one rope in one-to-one relation.

Many to Many Relations

We have seen one-to-many relationship and also the self-join relationship.

Let’s extend it with many-to-many relationship. Here one row can refer to many other rows, but that other row can also be referred by multiple rows.

That was confusing, let’s take the simple example of users and posts as usual.

• In One-To-Many relation, one post can only be written by one user. However, one user can author multiple posts.
• In Many-To-Many relation, one user can author multiple posts and one post can be written by multiple users.

That is the distinction, it is not about the relation, it is more about the design that your system needs.

Self Join Relations

In the last post, we had defined a one-to-many relation between the users table and the posts table.

Now, let’s create a single table where we can define a one-to-many relationship. Yes, within a single table we can reference the primary key and it can act like a link. Since it isn’t really about the table, it’s more about the row relation form each other, we an connect any rows.

One to Many Relation with Foreign Key

Back to the question that we raised in the previous post, “What would happen if there are more than one row in the primary table that references the same id in the foreign table?”

This is what is precisely called the one-to-many relation, or what the foreign key is used for.

• One row in the foreign(parent/other) table
• that can be referenced by many rows in the primary(child) table

So, taking the example from the previous post of author and posts,

The Foreign KEY Table constraint

Foreign Keys are the fundamentals of any relational databases, as they are the ones that make the connection or the relations among the tables in our database system.

Foreign key as the name suggest, this is a key referencing or pointing to a foreign (other) table, and that key could be a primary key for that table, hence referred to as key.

So, with a foreign key we can connect the data/records/row from other table to the table in which the foreign key is linked.

The PRIMARY KEY Table constraint

The PRIMARY KEY constraint is not actually a column constraint, it is a table constraint. In the previous section we learnt about how to handle single column-level PRIMARY KEY. In this section we’ll understand how to use PRIMARY KEY as table constraint, with that we can use multiple columns to combine the key.

Since, PRIMARY KEY is a table level constraint, it only can be defined once for the table, as it needs to be unique for each record inserted in that table. So, we can define it with the column or define it at the end, doesn’t matter, but it needs to be defined only once.

The PRIMARY KEY column constraint

The PRIMARY KEY constraint is not actually a column constraint, it is a table constraint. But in this section, we’ll only learn about how to handle single column-level PRIMARY KEY.

The PRIMARY KEY column constraint is a constraint that ensures that a column contains unique values and is the PRIMARY way to distinguish between all the rows of that table.

From the documentation:

Each table in SQLite may have at most one PRIMARY KEY. If the keywords PRIMARY KEY are added to a column definition, then the primary key for the table consists of that single column. Or, if a PRIMARY KEY clause is specified as a table-constraint, then the primary key of the table consists of the list of columns specified as part of the PRIMARY KEY clause. The PRIMARY KEY clause must contain only column names — the use of expressions in an indexed-column of a PRIMARY KEY is not supported. An error is raised if more than one PRIMARY KEY clause appears in a CREATE TABLE statement. The PRIMARY KEY is optional for ordinary tables but is required for WITHOUT ROWID tables. If a table has a single column primary key and the declared type of that column is “INTEGER” and the table is not a WITHOUT ROWID table, then the column is known as an INTEGER PRIMARY KEY. See below for a description of the special properties and behaviors associated with an INTEGER PRIMARY KEY. Each row in a table with a primary key must have a unique combination of values in its primary key columns. For the purposes of determining the uniqueness of primary key values, NULL values are considered distinct from all other values, including other NULLs. If an INSERT or UPDATE statement attempts to modify the table content so that two or more rows have identical primary key values, that is a constraint violation. According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or a STRICT table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allows NULLs in most PRIMARY KEY columns.

The CHECK column constraint

The CHECK clause is a column constraint that allows us to define certain conditions that we want to evaluate before inserting the column for the row and populate it accordingly. In this post, we will check, what this check is and when it is performed.

What it checks

The CHECK constraint basically acts a validator, we define the condition and if the condition is not met (is false) then the row is not inserted or updated.

The GENERATED column constraint

We saw certain limitations of the DEFAULT column constraint clause, that it can’t generate dynamic values based on other columns or some other dynamic popularities. For overcomming those kind of constraints, there is one more specific constraint, the GENERATED column constraint.

You can think of it as a DEFAULT but having more than static values, it is evaluated and computed each time a new row is inserted. However they come with one catch, you can’t later update the values, you need to update the values of the dependent columns first, so it will generate ALWAYS a update is made to the row(relevant columns in that row).