SQL Srver foreign key error

Question

I have this assignment for beginning sql server to add foreign keys to these tables. The last ALTER TABLE command always throws an error

There are no primary or candidate keys in the referenced table 'TCases' that match the referencing column list...

and I really can't figure out why it's giving this error. Any insight is appreciated.

CREATE TABLE TCourtRooms
(        intCourtRoomID             INTEGER         NOT NULL
        ,strCourtRoomNumber         VARCHAR(50)     NOT NULL
        ,strJudgeLastName           VARCHAR(50)     NOT NULL
        ,strJudgeFirstName          VARCHAR(50)     NOT NULL
        ,CONSTRAINT TCourtDockets_PK PRIMARY KEY (intCourtRoomID)
)

CREATE TABLE TCases
(        intCourtRoomID             INTEGER         NOT NULL
        ,intCaseIndex               INTEGER         NOT NULL    
        ,strCaseNumber              VARCHAR(50)     NOT NULL
        ,strDescription             VARCHAR(50)     NOT NULL
        ,CONSTRAINT TCases_PK PRIMARY KEY (intCourtRoomID, intCaseIndex)
)

CREATE TABLE TPersons
(        intCourtRoomID             INTEGER         NOT NULL
        ,intCaseIndex               INTEGER         NOT NULL
        ,intPersonIndex             INTEGER         NOT NULL
        ,strLastName                VARCHAR(50)     NOT NULL
        ,strFirstName               VARCHAR(50)     NOT NULL
        ,strPersonRole              VARCHAR(50)     NOT NULL        --Options are plaintiff or defendant
        ,CONSTRAINT TPlaintiffs_PK PRIMARY KEY (intCourtRoomID, intCaseIndex, intPersonIndex)
  )


CREATE TABLE TLawyers
(        intLawyerID                INTEGER         NOT NULL
        ,intCaseIndex               INTEGER         NOT NULL
        ,intPersonIndex             INTEGER         NOT NULL
        ,strLastName                VARCHAR(50)     NOT NULL
        ,strFirstName               VARCHAR(50)     NOT NULL
        ,strLawyerRole              VARCHAR(50)     NOT NULL  --plaintiff or defendant
        ,CONSTRAINT TLawyers_PK PRIMARY KEY (intLawyerID, intCaseIndex, intPersonIndex)
        ,CONSTRAINT TLawyers_intLawyerID_strLawyerRole_UN   UNIQUE (intLawyerID, strLawyerRole)
)

Problem 3.2 Identify and create foreign keys

-- Child                    Parent          Column(s)
-- -----                    ------          ---------
-- TCases                   TCourtRooms     intCourtRoomID
-- TPersons                 TCases          intCourtRoomID, intCaseIndex
-- TLawyers                 TCourtRooms



ALTER TABLE TCases
    ADD CONSTRAINT TCases_TCourtRooms_FK
    FOREIGN KEY (intCourtRoomID) REFERENCES TCourtRooms (intCourtRoomID)

ALTER TABLE TPersons
    ADD CONSTRAINT TPersons_TCases_FK
    FOREIGN KEY (intCourtRoomID, intCaseIndex) REFERENCES TCases (intCourtRoomID, intCaseIndex)

ALTER TABLE TLawyers
    ADD CONSTRAINT TLawyers_TCases_FK
    FOREIGN KEY (intCaseIndex) REFERENCES TCases (intCaseIndex)</code>

Answer 1

This is the primary key for TCases:

   CONSTRAINT TCases_PK PRIMARY KEY (intCourtRoomID, intCaseIndex)

It is a composite primary key, with two parts. You need to include both keys in the declaration:

ALTER TABLE TLawyers
    ADD CONSTRAINT TLawyers_TCases_FK
    FOREIGN KEY (intCaseIndex) REFERENCES TCases (intCourtRoomId, intCaseIndex)

But, alas, you cannot because the field TLawyers.intCourtRoomId does not exist. I'm not sure what you should do:

• Add the field to TLawyers.
• Fix the primary key definition to reference only one column.
• Do something else.

But that is your problem.