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.

Weekly #60

Another great week, consistently learning SQL, developing another streak for solving problems on FreeCodeCamp with Javascript and Python, reading instead of doomscrolling.

I have been learning about SQL since maybe 10 weeks now and finding myself in a good position, for the past 4 weeks I have written posts every day and it has helped me get back to the technical writing flow.

On the work side, it was a fun week, tinkering with a system that is already working decently and wanted it to get the most of it, turning the bits and knobs of a system is so cool.

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.

Week #59

Another productive week, a lot shipped, almost all critical bugs fixed, the launch looks great. Wrote daily for another week about SQLite/SQL. Adding up 24 posts. Read it here. Generating a lot of ideas, getting back to journaling, reading instead of consuming videos and doomscrolling. It is getting better day by day. I have completed my yearly goal of reading 12 books. Still 3 more months to go, would be almost at 15-18 books.