Why Is a Primary-Foreign Key Relation Required When We Can Join Without It

Why is a primary-foreign key relation required when we can join without it?

The main reason for primary and foreign keys is to enforce data consistency.

A primary key enforces the consistency of uniqueness of values over one or more columns. If an ID column has a primary key then it is impossible to have two rows with the same ID value. Without that primary key, many rows could have the same ID value and you wouldn't be able to distinguish between them based on the ID value alone.

A foreign key enforces the consistency of data that points elsewhere. It ensures that the data which is pointed to actually exists. In a typical parent-child relationship, a foreign key ensures that every child always points at a parent and that the parent actually exists. Without the foreign key you could have "orphaned" children that point at a parent that doesn't exist.

What is the purpose of primary and foriegn keys, if I can join without defining them?

There have been some good answers, allow me to extrapolate a little bit.

As "dasblinkenlight" states, speed of the retrieval (the database knows in advance about the relationship between the two tables and can potentially optimize the query), and more importantly, referential/data integrity.

Let's talk about the second one a bit more.

Consider a simple scenario where you have the following three tables (I have simplified this. In reality, you'd have the ability to order many products, but I am keeping it simple for brevity):

CustomerTable
   ID
   FirstName
   LastName

OrderTable
   ID
   CustomerID
   ProductID    

ProductTable
   ID
   Description

Without foreign keys, we are freely able to:

• Create Orders with no Product
• Create Orders with no Customer
• Delete Customers that have Orders
• Delete Orders that have Products
  etc...

This is a recipe for disaster

By using foreign keys, we can redesign the above, thusly:

CustomerTable
   ID
   FirstName
   LastName

OrderTable
   ID
   CustomerID -> References CustomerTable.ID
   ProductID -> References ProductTable.ID

ProductTable
   ID
   Description

We can specify that CustomerID and ProductID are "NOT NULL" (just like other columns). This means to create an order, we have to have an existing customer and an existing product. Excellent!

But it gets better. What happens if I start deleting products? Unless we've specified some cascading options (see this), the database will throw a wobbly and say "Nope. You can't delete that, it's referenced by something".

This is the data integrity part of the equation. We can't point to something that's not there (A foreign key insists that we point it to nothing [if null is allowed], or something that is there. The primary key helps us with this).

Suddenly, the database itself is making sure that everything works well together and ties up nicely. Without it, you could delete all the orders... delete products... delete anything you wanted, and then your application would wonder what the hell was going on.

Use them :)

Establishin a relation between two tables without the primary key - foreign key relation (ORACLE)

If I understand well, you need a unique constraint on the referenced table:

SQL> CREATE TABLE rooms
  2  (
  3      ID_Rooms        NUMBER PRIMARY KEY,
  4      RoomNumber      NUMBER,
  5      COD_RoomType    NUMBER,
  6      RoomPrice       NUMBER,
  7      FLOOR           NUMBER
  8  );

Table created.

SQL> CREATE TABLE D_ROOMTYPE
  2  (
  3      ID_d_RoomType    NUMBER PRIMARY KEY,
  4      RoomType         VARCHAR2(100),
  5      COD_RoomType     NUMBER
  6  );

Table created.

SQL> ALTER TABLE D_ROOMTYPE ADD CONSTRAINT UNIQUE_COD_RoomType UNIQUE  (COD_RoomType);

Table altered.

SQL> ALTER TABLE rooms
  2  ADD CONSTRAINT rooms_FK1 FOREIGN KEY (COD_RoomType) REFERENCES d_RoomType(COD_RoomType) ENABLE;

Table altered.

However, in most cases it would be better to add the FK based on the PK of the referenced table, instead of de-normalizing the code in the referencing table

Foreign Key to non-primary key

If you really want to create a foreign key to a non-primary key, it MUST be a column that has a unique constraint on it.

From Books Online:

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY
KEY constraint in another table; it can also be defined to reference
the columns of a UNIQUE constraint in another table.

So in your case if you make AnotherID unique, it will be allowed. If you can't apply a unique constraint you're out of luck, but this really does make sense if you think about it.

Although, as has been mentioned, if you have a perfectly good primary key as a candidate key, why not use that?

Foreign Keys vs Joins

Foreign keys are just constraints to enforce referential integrity. You will still need to use JOINs to build your queries.

Foreign keys guarantee that a row in a table order_details with a field order_id referencing an orders table will never have an order_id value that doesn't exist in the orders table. Foreign keys aren't required to have a working relational database (in fact MySQL's default storage engine doesn't support FKs), but they are definitely essential to avoid broken relationships and orphan rows (ie. referential integrity).

What is the new primary key when we apply natural join on two tables?

In relational database terms the result should have keys corresponding to every key from one relation paired with every key from the other (i.e. the "natural-join" of the candidate keys, not just one key per relation). So if A1 and B1 are the only keys in your example then the only key in the natural-joined result would be (A1,B1).

However, you tagged your question with MySQL. MySQL is a SQL DBMS, not relational. MySQL doesn't support the relational model concept of key derivation. In MySQL there is no physical implementation of any key in the result of such a query.

Is it possible to create one index for both primary and foreign key?

As I know [foreign] keys use indexes

This is false. I am guessing that your experience with databases is limited to MySQL/MariaDB. These are two databases where a foreign key definition does created an index on the referencing table.

In most databases, a foreign key definition does NOT create an index on the referencing table. Another difference is that most databases (and I'm pretty sure the standard as well) requires that the referenced key be either a primary key or unique key. That doesn't affect you in this case, but it is another deviation from the standard in MySQL in this area.

Filter a join on each primary-foreign key relation only

An example of a correlated sub query in a where clause

drop table if exists t,t1;
create table t(id int);
create table t1(jid int,dt date);
insert into t values
(1),(2),(3);

insert into t1 values
(1,'2018-01-01'),
(1,'2018-02-01'),
(2,'2018-01-01'),
(3,'2018-01-01'),
(3,'2018-02-01'),
(3,'2018-03-01');

select t.id,t1.dt
from t
join t1 on t1.jid = t.id
where t1.dt =(select max(dt) from t1 where t1.jid = t.id);

+------+------------+
| id   | dt         |
+------+------------+
|    1 | 2018-02-01 |
|    2 | 2018-01-01 |
|    3 | 2018-03-01 |
+------+------------+
3 rows in set (0.00 sec)

If you need the latest n records and you are not on version 8.0 or higher you can use row number simulation

select t.id,s.dt
from t
join 
(select t1.jid,t1.dt ,
          if(t1.jid<>@p,@rn:=1,@rn:=@rn+1) rn,
          @p:=t1.jid p
from t1 
cross join (select @rn:=0,@p:=0) r
order by t1.jid ,t1.dt desc
) s on s.jid = t.id
where s.rn <= 2;

+------+------------+
| id   | dt         |
+------+------------+
|    1 | 2018-01-01 |
|    1 | 2018-02-01 |
|    2 | 2018-01-01 |
|    3 | 2018-02-01 |
|    3 | 2018-03-01 |
+------+------------+

---

Related Topics