SQL join concept and constraints of keys-CodePudding

I have question about conceptual difference between JOIN and constraints. We are using primary key and foreign key relationship in building models.

But if we can use JOINs to build any kind of left/full/inner relationships, why do we need constraints? Adding reference for further reading will be appreciated.

CodePudding user response：

JOINs lets you construct queries for retrieving data from related tables. For example, if you have a Teachers table and a Classes table like this

CREATE Teachers (TeacherId INT NOT NULL PRIMARY KEY, TeacherName VARCHAR(100) NOT NULL);
CREATE Classes (ClassID INT NOT NULL PRIMARY KEY, TeacherId INT NOT NULL,
 ClassName VARCHAR(100));

a query that JOINs them

SELECT T.TeacherId, T.TeacherName, C.ClassId, C.ClassName 
FROM Teachers T
INNER JOIN Classes C on T.TeacherId=C.TeacherId

will let you find the Classes that each Teacher is teaching.

JOINs can be used to join any two tables, even if there is no meaningful relationship between them:

SELECT T.* FROM Teachers 
INNER JOIN Reptiles R
  On R.ReptileId = T.TeacherId

Foreign Key CONSTRAINTs let you prevent insertion of data that violates the constraint. Or prevent deletion of data that would result in violation of a constraint. For example, a foreign key constraint between the Teachers and Classes tables

ALTER TABLE Classes
   ADD CONSTRAINT FK_Teacher_Class FOREIGN KEY (TeacherId)
      REFERENCES Teachers (TeacherId)

would prevent you from deleting a Teacher if they still are registered as teaching at least one Class; it would also prevent you from Inserting a class that had no Teacher, or modifying a Class to have a TeacherId with no matching Teacher row.

Also, there are other kinds of constraints, for example CHECK constraints control what values a particular column is allowed to have.

In modern DBMSes, the existence of constraints can be used to optimize a SQL query so it is far more efficient.

CodePudding user response：

I think that constraints are used to not allow the deletion of a data in an array knowing that it is related to another. Basically, it's data protection.