composite foreign key linking table-A with table-B and table-C

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)
       )

Answer 1

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

Answer 2

thanks.so you cannot create a composite foreign key but can have multiple independent foreign key for a table,right?

Answer 3

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

Answer 4

>> 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