FOREIGN KEY

FOREIGN KEY

1. FOREIGN KEY ALWAYS DEMANDS PRIMARY KEY

   • WITH OUT A PRIMARY KEY FOREIGN KEY CANNOT EXIST

2. FOREIGN KEY AND PRIMARY KEY CAN BE THERE IN SAME TABLE OR DIFFERENT TABLES

3. PRIMARY KEY NEVER DEMANDS A FOREIGN KEY

4. ALSO CALLED AS REFERENTIAL INTEGRITY CONSTRAINTS

5. PRIMARY KEY TABLE----PARENTAL TABLE

6. FOREIGN KEY TABLE ----CHILD TABLE

7. FOREIGN KEY --- LINK MASTERS

8. PRIMARY KEY ----MASTER TABLE

9. CORRESPONDING COLUMN OR COLUMNS OF THE FOREIGN KEY AND THE REFERENCED KEY MUST MATCH IN ORDER AND DATATYPE

10. NAME OF THE FK AND PK NEED NOT BE SAME

    • EXITS IN SOME CASES BECAUSE THERE IS A CHANCE OF COLUMNS NAMES BEING SAME IN DIFFERENT TABLES
      • NOT POSSIBLE IN SAME TABLE BECAUSE TWO COLUMN NAMES IN A TABLE CANNOT BE SAME

11. ESTABLISHMENT

    • TWO IMPORTANT RULES
      • RULE 1 : THE DATATYPE OF BOTH THE COLUMNS SHOULD BE SAME
      • RULE 2 : PRIMARY KEY SHOULD BE DECLARED FIRST AND THEN FOREIGN KEY SHOULD BE DECLARED

12. SIZE OF A PK AND FK

    • IF SAME VERY GOOD
    • IF FK (SIZE) IS MORE THAN PK (SIZE) THEN IT WILL BE A PROBLEM AT RUN TIME.
      • SCENARIO : PK : 4 DIGIT & YOUR FK : 6 DIGIT
        • BECAUSE IF PK : 4 DIGIT & YOUR FK : 6 DIGIT THEN WHILE INSERTING YOUR RECORDS INTO FK COLUMN, IT WILL NOT ACCEPT 6 DIGIT BECAUSE PRIMARY KEY WILL NOT ACCEPT 6 DIGIT VALUE

13. ONE TABLE : ONLY 1 PRIMARY KEY ONE TABLE : MULTIPLE FOREIGN KEYS

14. DECLARATION : CAN BE THERE AT COLUMN LEVEL AND TABLE LEVEL BOTH

    • IF IT IS COMPOSITE FOREIGN KEY THEN IT SHOULD BE DECLARED AT TABLE LEVEL

15. WE CAN DESIGNATE A COLUMN OR COMBINATION OF COLUMNS BOTH AS FOREIGN KEY AND UNIQUE.