• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Rob Spoor
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Junilu Lacar
  • Tim Cooke
Saloon Keepers:
  • Tim Holloway
  • Piet Souris
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Frits Walraven
  • Himai Minh

Why should my tables be logically adjacent, when I can use JOINs without doing it?

 
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Marshal
Posts: 26750
81
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
As you have seen, the constraints aren't there so that it's possible to do joins. So your question turns out to be, what are constraints for?

One page I found on the web says "SQL constraints are used to specify rules for data in a table."

And it goes on to be more specific... but I'll let you read it for yourself: SQL Constraints. Or you could look at What Is a SQL Constraint? for a similar take on the question.
 
author & internet detective
Posts: 40726
827
Eclipse IDE VI Editor Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Saloon Keeper
Posts: 13202
286
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Stefan Jankovic
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks a lot, guys. I totally get the picture now
 
Saloon Keeper
Posts: 24207
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
One last thing.

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.
 
Ranch Hand
Posts: 42
Android Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:One last thing.

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).

...



In this I disagree: DDL is a part of SQL - that's how I heart it in my whole professional life.

SQL in wikipedia en

Originally based upon relational algebra and tuple relational calculus, SQL consists of many types of statements,[6] 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][7] 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.



Reference of PostgreSQL SQL has information about compatability with SQL standards
https://www.postgresql.org/docs/current/reference.html

Just one example from there about DROP TABLE
https://www.postgresql.org/docs/current/sql-droptable.html

Compatibility
This command conforms to the SQL standard, except that the standard only allows one table to be dropped per command, and apart from the IF EXISTS option, which is a PostgreSQL extension.

 
Tim Holloway
Saloon Keeper
Posts: 24207
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Unfortunately, my copy of Codd & Date has been hijacked, but I'm pretty certain that it does not cover any DDL - only SQL proper.

In fact, that was for many years a serious problem with SQL DBMS's - their DDLs were often quite incompatible, whereas SQL was SQL - meaning raw SQL, not functions or stored procedures.

Later "SQL Standards" have mitigated a lot of that, but when I refer to SQL, I'm generally referring to the original Codd & Date.
 
Ew. You guys are ugly with a capital UG. Here, maybe this tiny ad can help:
the value of filler advertising in 2021
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic