Differences Between "Foreign Key" and "Constraint Foreign Key"

Differences between foreign key and constraint foreign key

The first option is purely for naming the constraint.

From SQL FOREIGN KEY Constraint

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax

CREATE TABLE Orders
(
  O_Id int NOT NULL,
  OrderNo int NOT NULL,
  P_Id int,
  PRIMARY KEY (O_Id),
  CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
  REFERENCES Persons(P_Id)
)

Also, from CREATE TABLE (Transact-SQL) one can see that [ CONSTRAINT constraint_name ] is optional.

MySQL terminology constraints vs foreign keys difference?

Yes, a foreign key is a type of constraint. MySQL has uneven support for constraints:

PRIMARY KEY: yes as table constraint and column constraint.
FOREIGN KEY: yes as table constraint, but only with InnoDB and BDB storage engines; otherwise parsed but ignored.
CHECK: parsed but ignored in all storage engines.
UNIQUE: yes as table constraint and column constraint.
NOT NULL: yes as column constraint.
DEFERRABLE and other constraint attributes: no support.

The CONSTRAINT clause allows you to name the constraint explicitly, either to make metadata more readable or else to use the name when you want to drop the constraint. The SQL standard requires that the CONSTRAINT clause is optional. If you leave it out, the RDBMS creates a name automatically, and the name is up to the implementation.

Difference between ADD FOREIGN KEY and ADD CONSTRAINT fk_foreign_key

Both queries are more or less the same, though according to the manual the FOREIGN KEY should be present in both queries.

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

The benefit of CONSTRAINT [symbol] is that you can name the constraint rather than MySQL generating the name for you; this symbol must be unique within the database and can be used to later remove the constraint.

Generated symbol names can be seen by checking the table structure after creation:

SHOW CREATE TABLE TABLE_A;

What is the difference between foreign key constraint & referential integrity constraint

Why we are preferring Referential Integrity Constraint:

According to the reference[1], the referential integrity constraint is the state of a database in which all values of all foreign keys are valid.

In your example, the line “ The values of B in R1 must mandatorily exist in R2” indicates--the child table(R2) contains all values of B from the parent table(R1), means all values of B in R2 are valid -- resulting in Referential integrity constraint.

Again why we are not using foreign key constraint:

According to the reference[2], it is not necessary to always have foreign keys to ensure referential integrity constraints. There are other ways of ensuring the integrity constraint.

Back to your example, there is no mention about foreign keys or primary keys. The question only indicates the reference between two tables. Hence, it would be better to choose the referential integrity constraint instead of foreign key constraint.

References:

https://www.toolbox.com/tech/big-data/question/difference-between-foreign-key-and-refrential-integrity-constraint-091703/
https://www.mssqltips.com/sqlservertip/4242/sql-server-referential-integrity-without-foreign-keys/

What is the difference between primary, unique and foreign key constraints, and indexes?

Primary Key and Unique Key are Entity integrity constraints

Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist and no null values are entered.

Unique key constraint is used to prevent the duplication of key values within the rows of a table and allow null values. (In oracle one null is not equal to another null).

KEY or INDEX refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain rows with identical values in all columns of the index. These indexes don't enforce any structure on your data so they are used only for speeding up queries.
UNIQUE refers to an index where all rows of the index must be unique. That is, the same row may not have identical non-NULL values for all columns in this index as another row. As well as being used to speed up queries, UNIQUE indexes can be used to enforce structure on data, because the database system does not allow this distinct values rule to be broken when inserting or updating data. Your database system may allow a UNIQUE index on columns which allow NULL values, in which case two rows are allowed to be identical if they both contain a NULL value (NULL is considered not equal to itself), though this is probably undesirable depending on your application.
PRIMARY acts exactly like a UNIQUE index, except that it is always named 'PRIMARY', and there may be only one on a table (and there should always be one; though some database systems don't enforce this). A PRIMARY index is intended as a way to uniquely identify any row in the table, so it shouldn't be used on any columns which allow NULL values. Your PRIMARY index should always be on the smallest number of columns that are sufficient to uniquely identify a row. Often, this is just one column containing a unique auto-incremented number, but if there is anything else that can uniquely identify a row, such as "countrycode" in a list of countries, you can use that instead.
FULLTEXT indexes are different to all of the above, and their behaviour differs more between database systems. Unlike the above three, which are typically b-tree (allowing for selecting, sorting or ranges starting from left most column) or hash (allowing for selection starting from left most column), FULLTEXT indexes are only useful for full text searches done with the MATCH() / AGAINST() clause.

see Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?

What is the difference between key and constraint in sql?

A key is a single or combination of multiple fields in a table. Its is used to fetch or retrieve records/data-rows from data table according to the condition/requirement. Keys are also used to create relationship among different database tables or views.

SQL constraints are used to specify rules for the data in a table.
If there is any violation between the constraint and the data action, the action is aborted.
Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).

you can check more documentation in the links below

Keys

Constraint

What is the Difference between adding Column as Foreign Key and as a Constraint

There is no difference except in your use of the optional "CONSTRAINT" and constraint name clause.

There are two kinds of constraint definition: inline and out of line. The former operates on a column as part of the column definition, and hence does not need to name the DeptID column. The latter is part of the table definition and therefore does.

Both of your examples are out of line constraints, but you have not named the constraint in the former case, which is a bad practice:

http://docs.oracle.com/cd/E18283_01/server.112/e17118/clauses002.htm#g1053592

What is the difference between @ForeignKey and @Relation annotations in Room database?

A @ForeignKey defines a constraint (aka rule) that requires that the child column(s) exist in the parent column(s). If an attempt is made to break that rule then a conflict occurs (which may be handled various ways by the onDelete/onUpdate definition).

An @Relationship is used to define a relationship where a number of child (perhaps Foreign Key children) objects are returned in the parent object.

Underneath it all @Relation automatically (effectively) joins the tables and generates the number of child objects. Whilst a @ForeignKey just affects the schema (with the exception of onDelete/onUpdate handling), it does not result in the respective tables being joined.

Perhaps Consider the following :-

Service Entity

@Entity(
    tableName = "services"
)
class Services {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "services_id")
    var id: Long = 0
    var service_date: String = ""
    var user_mobile_no: String = ""

}

and

ServiceDetail Entity :-

@Entity(
    tableName = "service_detail",
    foreignKeys = [
        ForeignKey(
            entity = Services::class,
            parentColumns = ["services_id"],
            childColumns = ["services_id"],onDelete = ForeignKey.SET_DEFAULT
        )
    ]
)
class ServiceDetail {

    @PrimaryKey
    var id: Long? = null;
    var services_id: Long = 0;
    @ColumnInfo(defaultValue = "1")
    var service_type_id: Long = 0;

    constructor()

    @Ignore
    constructor(services_id: Long, service_type_id: Long) {
        this.services_id = services_id
        this.service_type_id = service_type_id
    }
}

This is saying that in order to add a ServiceDetail then the value of the services_id column MUST be a value that exists in the services_id column of the services Table, otherwise a conflict will happen. And additionally if a row is deleted from the services table then any rows in the service_detail table that reference that row will also be deleted (otherwise the row couldn;t be deleted from the services table).

Now consider this normal class (POJO), which is NOT an entity (aka table) :-

class ServiceWithDetail {

    @Embedded
    var services: Services? = null

    @Relation(entity = ServiceDetail::class,parentColumn = "services_id",entityColumn = "services_id")
    var serviceDetail: List<ServiceDetail>? = null
}

This is roughly saying when you ask for a ServiceWithDetail object then get a services object along with a list of the related service_detail objects

You would have a Dao such as :-

@Query("SELECT * FROM services")
fun getAllServices() :List<ServiceWithDetail>

So it will get all the services from the services table along with the related (i.e. where the services_id in the services_detail is the same as the services_id of the current services row being processed).

onConflictStrategy

REPLACE does the following :-

When a UNIQUE or PRIMARY KEY constraint violation occurs, the REPLACE
algorithm deletes pre-existing rows that are causing the constraint
violation prior to inserting or updating the current row and the
command continues executing normally.

If a NOT NULL constraint
violation occurs, the REPLACE conflict resolution replaces the NULL
value with the default value for that column, or if the column has no
default value, then the ABORT algorithm is used. If a CHECK constraint
or foreign key constraint violation occurs, the REPLACE conflict
resolution algorithm works like ABORT.

When the REPLACE conflict resolution strategy deletes rows in order to
satisfy a constraint, delete triggers fire if and only if recursive
triggers are enabled.

The update hook is not invoked for rows that are deleted by the
REPLACE conflict resolution strategy. Nor does REPLACE increment the
change counter. The exceptional behaviors defined in this paragraph
might change in a future release.REPLACE

Hence, the potential for the behaviour that you have experienced. However, it depends upon what the update is doing. If the value for the ForeignKey(s) differ then they should, assuming there is no Foreign Key conflict replace the foreign key value with the new valid value. If the Foreign Key value(s) is(are) unchanged then replacement row will have the same Foreign Keys.

Differences Between "Foreign Key" and "Constraint Foreign Key"