locked
composite foreign key linking table-A with table-B and table-C RRS feed

  • Question

  • hi there, i have 3 tables zones,branches and employee . i want to link the branch table to the other two using foreign key.

    can this be done.thanks

    create table zone
          (
           ZoneID tinyint constraint pk_znid primary key
           )
    create table employee
           (
            EmployeeID int identity constraint pk_emp primary key
           )

    create table branch
           (
            BranchZoneID tinyint not null,
            BranchHeadID smallint,
           constraint fk_br foreign key (BranchZoneID,BranchHeadID)
           references zone(ZoneID), employee(EmployeeID)
           )

    Wednesday, October 17, 2012 9:14 AM

Answers

All replies

  • Here is one way:

    create table zone
           (
            ZoneID tinyint constraint pk_znid primary key
            )
     create table employee
            (
             EmployeeID int identity constraint pk_emp primary key
            )
     create table branch
            (
             BranchZoneID tinyint not null  references zone(ZoneID),
             BranchHeadID int references employee(EmployeeID),
    	constraint pk_br primary key (BranchZoneID,BranchHeadID)
             ) 


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    • Edited by Kalman Toth Wednesday, October 17, 2012 9:22 AM
    • Marked as answer by sqlcrazy123 Wednesday, October 17, 2012 9:40 AM
    Wednesday, October 17, 2012 9:21 AM
  • thanks.so you cannot create a composite foreign key but can have multiple independent foreign key for a table,right?
    Wednesday, October 17, 2012 9:29 AM
  • A froeign key can be over more than one column (composite), but it can only refer to one table.

    Tibor Karaszi, SQL Server MVP | web | blog


    • Edited by TiborKMVP Wednesday, October 17, 2012 9:51 AM Spelling
    Wednesday, October 17, 2012 9:43 AM
  • >> I have 3 tables Zones, Branches and Personnel. I want to link [sic] the Branches table to the other two using foreign keys. << 

    Proper Table names are collective to plural nouns because they model sets of entities. Links are a term from old network databases; they are not like the references we use in RDBMS. IDENTITY is never a key; why do think the physical insertion count to one table on one machine is an attribute of an employee? Let's use a real identifier which will be some kind of tag number. 

    CREATE TABLE Zones
    (zone_id TINYINT NOT NULL PRIMARY KEY
      CHECK (zone_id IN (<list of zones>));

    CREATE TABLE Personnel
    (emp_id CHAR(10) NOT NULL PRIMARY KEY
      CHECK (emp_id LIKE '<reg expr>'));

    Now we have no specs for the rest of this problem. I will guess that you meant to have an employee in role of branch manager; is this required or optional? Do you update or delete with a DRI action? Here is my guesses. You just have a manager to have a branch; if you close down a zone, you close the branches in it. 

    CREATE TABLE Branches
    (branch_nbr INTEGER NOT NULL PRIMARY KEY,
     branch_mgr_emp_id CHAR(10) NOT NULL
     REFERENCES Personnel (emp_id)
     ON UPDATE CASCADE,
     zone_id TINYINT NOT NULL
     REFERENCES Zones(zone_id)
     ON DELETE CASCADE
     ON UPDATE CASCADE);

    The FOREIGN KEY is in the referencing table and it references [not link!!] a referenced table's UNIQUE or PRIMARY KEY. A multi-column FOREIGN KEY has to be “union compatible” with its PRIMARY KEY/UNIQUE. Oh, the referenced and referencing tables can be the same table.   See if this helps: 

     FOREIGN KEY (longitude, latitude)  
      REFERENCES Pub_Locations (longitude, latitude)

    but this will fail:
     FOREIGN KEY (longitude, latitude)  
      REFERENCES Pub_Locations (latitude, longitude) -- wrong order!

    this will fail:
     FOREIGN KEY (longitude, latitude)  
      REFERENCES Pub_Locations (longitude, shoe_size) -- not the PK/UNIQUE! 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Naomi N Friday, October 26, 2012 12:55 AM
    Wednesday, October 17, 2012 4:02 PM