Everyone knows that relational databases contain relations that are "described" with foreign keys. I later can use SQL JOINs to obtain Result Sets from those joins.
But what I noticed is that I can make such JOINs without even specifying foreign key CONSTRAINTs in my tables. Doing "simple" JOIN between some tables (that are now not logically adjacent) should work as well. So, why should I always specify constraints when SQL JOIN commands work just as same? I know I am losing/missing something, I just don't know what.
You can absolutely do joins without constraints. You use constraints when you want the database to guarantee something is true. For example with a foreign key, you want to make sure it only has a value that is a primary key in another table.
An old application we inherited uses a database without foreign key constraints.
It's a nightmare. There are so many dangling references to entries that no longer exist, it's a mess.
A nice thing about foreign key constraints is that you can prevent an entry from being deleted if another entry refers to it, or you can let a deletion "cascade", meaning that entries that refer to an entry that is being deleted will also be deleted.
Most relational databases recognize 2 similar language sets. SQL, of course, since that's the Structured Query Language that sets them apart from other, non-SQL DBMS's.
But SQL is just a Data Manipulation Language. It's not a Schema Manipulation Language.
So there's another language that looks like SQL for that, and it's the Data Definition Language (DDL).
DDL contains things like CREATE, DROP, ALTER, and the statements that allow adding and removing keys of all types as well as triggers, and attaching/detaching stored procedures.
DDL doesn't feature in Codd and Date because they treated SQL as an abstract data manipulation language. To do abstract data manipulation you don't need DDL - you don't worry about the performance boost that keys can provide or the limiting factors of constraints. Or even where the tables themselves came from or what database they are in. Just SELECT, INSERT, UPDATE and DELETE.
Sources may include data from the Fakebook Research Foundation with support from Gargle University
Originally based upon relational algebra and tuple relational calculus, SQL consists of many types of statements, which may be informally classed as sublanguages, commonly: a data query language (DQL),[a] a data definition language (DDL),[b] a data control language (DCL), and a data manipulation language (DML).[c] The scope of SQL includes data query, data manipulation (insert, update and delete), data definition (schema creation and modification), and data access control. Although SQL is essentially a declarative language (4GL), it also includes procedural elements.