What Is Difference Between Foreign Key and Reference Key

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 an Index and a Foreign Key?

an index on a table is a data structure that makes random access to the rows fast and efficient. It helps to optimize the internal organization of a table as well.

A foreign key is simply a pointer to a corresponding column in another table that forms a referential constraint between the two tables.

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.

What is the difference between REFERENCES with, or without a FOREIGN KEY

The FOREIGN KEY syntax is more flexible than defining it inline in the column definition (e.g., it allows you to define a composite foreign key, where the combination of two or more fields should exist in the referencing columns).

In your case, there is no difference between the two DDL statements. One could say that the inline definition of foreign keys is nothing more than syntactic sugaring.

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.

Difference between foreign key constraint and references in Rails

I found some thing intresting in this page.

http://railsforum.com/viewtopic.php?id=17318

From a comment :

Rails doesn't use foreign keys to perform his backend tasks. This
because some db like sqlite doesn't allow foreign keys on its tables.
So Rails doesn't provide an helper to build a foreign key

Also there is a gem foreigner for adding foreign keys to database table.
What creates the FOREIGN KEY constraint in Ruby on Rails 3?

---

Related Topics