Can a foreign key refer to a primary key in the same table

Question

I just think that the answer is false because the foreign key doesn t have uniqueness property   But some people said that it can be in case of self joining the table  I am new to SQL  If its true please explain how and why    Employee table   e id   e name    e sala     d id     ----   -------   -----     --------     1       Tom      50K         A        2     Billy      15K         A        3     Bucky      15K         B       department table   d id   d name     ----   -------       A       XXX         B       YYY        Now  d id is foreign key so how it can be a primary key  And explain something about join  What is its use

User · Answer

Sure  why not  Let s say you have a Person table  with id  name  age  and parent id  where parent id is a foreign key to the same table  You wouldn t need to normalize the Person table to Parent and Child tables  that would be overkill   Person   id    name   age   parent id    ---- ------- ----- -----------     1     Tom    50        null      2   Billy    15           1     Something like this   I suppose to maintain consistency  there would need to be at least 1 null value for parent id  though  The one  alpha male  row   EDIT  As the comments show  Sam found a good reason not to do this  It seems that in MySQL when you attempt to make edits to the primary key  even if you specify CASCADE ON UPDATE it won   t propagate the edit properly  Although primary keys are  usually  off-limits to editing in production  it is nevertheless a limitation not to be ignored  Thus I change my answer to - you should probably avoid this practice unless you have pretty tight control over the production system  and can guarantee no one will implement a control that edits the PKs   I haven t tested it outside of MySQL

User · Answer

This may be a good explanation example  CREATE TABLE employees   id INTEGER NOT NULL PRIMARY KEY  managerId INTEGER REFERENCES employees id    name VARCHAR 30  NOT NULL     INSERT INTO employees id  managerId  name  VALUES 1  NULL   John    INSERT INTO employees id  managerId  name  VALUES 2  1   Mike      -- Explanation  -- In this example  -- John is Mike s manager  Mike does not manage anyone  -- Mike is the only employee who does not manage anyone

User · Answer

Eg  n sub-category level for categories  Below table primary-key id is referred by foreign-key sub category id

User · Answer

A good example of using ids of other rows in the same table as foreign keys is nested lists    Deleting a row that has children  i e   rows  which refer to parent s id   which also have children  i e   referencing ids of children  will delete a cascade of rows   This will save a lot of pain  and a lot of code of what to do with orphans - i e   rows  that refer to non-existing ids

User · Answer

I think the question is a bit confusing    If you mean  can foreign key  refer  to a primary key in the same table    the answer is a firm yes as some replied  For example  in an employee table  a row for an employee may have a column for storing manager s employee number where the manager is also an employee and hence will have a row in the table like a row of any other employee    If you mean  can column or set of columns  be a primary key as well as a foreign key in the same table    the answer  in my view  is a no  it seems meaningless  However  the following definition succeeds in SQL Server   create table t1 c1 int not null primary key foreign key references t1 c1     But I think it is meaningless to have such a constraint unless somebody comes up with a practical example   AmanS  in your example d id in no circumstance can be a primary key in Employee table  A table can have only one primary key  I hope this clears your doubt  d id is can be a primary key only in department table

User · Answer

Other answers have given clear enough examples of a record referencing another record in the same table  There are even valid use cases for a record referencing itself in the same table   For example  a point of sale system accepting many tenders may need to know which tender to use for change when the payment is not the exact value of the sale   For many tenders that s the same tender  for others that s domestic cash  for yet other tenders  no form of change is allowed  All this can be pretty elegantly represented with a single tender attribute which is a foreign key referencing the primary key of the same table  and whose values sometimes match the respective primary key of same record   In this example  the absence of value  also known as NULL value  might be needed to represent an unrelated meaning  this tender can only be used at its full value  Popular relational database management systems support this use case smoothly  Take-aways   When inserting a record  the foreign key reference is verified to be present after the insert  rather than before the insert   When inserting multiple records with a single statement  the order in which the records are inserted matters   The constraints are checked for each record separately   Certain other data patterns  such as those involving circular dependences on record level going through two or more tables  cannot be purely inserted at all  or at least not with all the foreign keys enabled  and they have to be established using a combination of inserts and updates  if they are truly necessary

[sql] Can a foreign key refer to a primary key in the same table?

Examples related to sql

Examples related to foreign-keys

Examples related to primary-key