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.
So, taking the example from the previous post of author and posts,
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 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 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.
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.
Link: https://www.infoworld.com/article/2335455/13-reasons-sql-has-got-to-go.html
https://www.infoworld.com/article/2335455/13-reasons-sql-has-got-to-go.html I don’t like this take, nor I think is grounded. Its looking like a AI slop or maybe someone just living in the land of NoSQL. “Tables don’t scale”, what is the meaning of it? If that’s the case, then collections also don’t? Right? I bet PostgreSQL and MySQL aren’t using tables, then it means that MySQL doesn’t scale, come on! I don’t want to react to this clickbait, I already know the pros and cons of SQL vs NoSQL, SQL isn’t going anywhere, and so is the good old MySQL. Relational Databases are the nuts and bolts of web, and without them, industry can fall apart pretty quickly. Unless MongoDB keeps them happy :)
Link: https://www.fredrivett.com/2025/09/10/becoming-the-person-who-does-the-thing/
https://www.fredrivett.com/2025/09/10/becoming-the-person-who-does-the-thing/ Yes, the world right now feels like everything is pushing me in a direction that I wanted, in my favor. For real! Journalling, job searching, embracing boredom, learning and locking in, and the most recent thing since almost a week, building a exercise routine. I also believed physical fitness is not important as mental well being. But I am coming to realize, there is no mental fitness if the body is not fit. At least it should be moving. By working remotely I was sucked into sitting and consuming junk/content. Feeling lethargic and exhausted all the time, last weekend, my brother set me up for an exercise, I felt energetic after that. I have been doing minimum 20 minutes each day since and keeping away from the vicious consumption cycle. Side by side, I am also on a writing streak of 24 on SQL and SQLite on my blog here with sqlog. This all is pushing me to get better and be the person I want to be. This article helps me validate everything I am doing, I don’t need validation for the actions I do, but a human is a social animal, he needs resonance and some level of accountability or validation for what I am heading for is the correct place or not.
Link: https://www.architecture-weekly.com/p/just-use-sql-they-say-or-on-how-accidental
Use tooling around it, there are SQLC tools for go and I think even for Python. It will help you write type safe boilerplate code in the native language as you write the SQL query. I think the author is right, but unaware of the tooling that exists around it.
Source: techstructive-weekly-59
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.
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.
Link: https://www.meetgor.com/sqlog/sqlite-create-table-column-types/
SQLOG Entries for 7th September to 12th September Continuing with some week of Learning SQL probably 8, but 24 days of consistently writing a blog post. Feels refreshing and accomplished. It really feels good to look at the number cracking up and the blog filling up, the sudden increase in likes, followers and sprinkles of curiosity to learn more. Writing about the things we learn is underrated. SQLite: Column Constraints SQLite: NOT NULL Constraint SQLite: UNIQUE Column Constraint SQLite: DEFAULT Column Constraint SQLite: Generated Column Constraint SQLite: Check Constraint