How to reference multiple foreign keys in a table that is a primary key and candidate keys?-CodePudding

i have 2 tables:

CREATE TABLE PERSON(  
    passportNumber VARCHAR(20) NOT NULL,  
    firstName VARCHAR(30) NOT NULL,  
    lastName VARCHAR(30) NOT NULL,  
    dateOfBirth DATE NOT NULL,  
    gender CHAR(1) NOT NULL,  
    CONSTRAINT person_pkey PRIMARY KEY(passportNumber),  
    CONSTRAINT person_ckey1 UNIQUE(firstName, lastName, dateOfBirth)
);

CREATE TABLE CHEF(  
    culinaryCerts VARCHAR(300) NOT NULL,  
    competitionEXpr VARCHAR(300) NULL,  
    passportNumber VARCHAR(20) NOT NULL,  
    firstName VARCHAR(30) NOT NULL,  
    lastName VARCHAR(30) NOT NULL,  
    dateOfBirth DATE NOT NULL,  
    CONSTRAINT chef_pkey PRIMARY KEY(passportNumber),  
    CONSTRAINT chef_ckey1 UNIQUE(firstName, lastName, dateOfBirth),  
    CONSTRAINT chef_fkey1 FOREIGN KEY(passportNumber)
                        REFERENCES PERSON(passportNumber)ON DELETE CASCADE,  
    CONSTRAINT chef_fkey2 FOREIGN KEY(firstName)
                        REFERENCES PERSON(firstName)ON DELETE CASCADE,  
    CONSTRAINT chef_fkey3 FOREIGN KEY(lastName)
                        REFERENCES PERSON(lastName)ON DELETE CASCADE,  
    CONSTRAINT chef_fkey4 FOREIGN KEY(dateOfBirth)
                        REFERENCES PERSON(dateOfBirth)ON DELETE CASCADE);

however, when I run the code, it shows:

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'chef_fkey3' in the referenced table 'PERSON'

CodePudding user response：

You don't need the firstName, lastName and dateOfBirth attributes in your CHEF table since you can retrieve them in PERSON with the passportNumber foreign key in a simple join.

You also do not need to specify NOT NULL when attributes are primary/foreign keys or if there are members of a unique constraint.

You can simplify your script like this :


CREATE TABLE PERSON (  
    passportNumber VARCHAR(20),  
    firstName VARCHAR(30),
    lastName VARCHAR(30),
    dateOfBirth DATE,
    gender CHAR(1) NOT NULL,
    CONSTRAINT person_pk PRIMARY KEY (passportNumber),
    CONSTRAINT person_ckey1 UNIQUE(firstName, lastName, dateOfBirth)
);

CREATE TABLE CHEF (
    culinaryCerts VARCHAR(300) NOT NULL,
    competitionEXpr VARCHAR(300),
    passportNumber VARCHAR(20),
    CONSTRAINT chef_pk PRIMARY KEY (passportNumber),
    CONSTRAINT chef_fk1 FOREIGN KEY (passportNumber) REFERENCES PERSON(passportNumber)
);

then retrieve your data like this :

SELECT *
FROM PERSON AS p
INNER JOIN CHEF AS c
ON p.passportNumber = c.passportNumber

CodePudding user response：

You have a combined unique constraint on firstName , lastName , dateOfBirth so your foreign key needs to include all columns like you see in the example below.

But f you need all three separate, you need to define each column also separately in Persons table

CREATE TABLE PERSON (
    passportNumber VARCHAR(20) NOT NULL,
    firstName VARCHAR(30) NOT NULL,
    lastName VARCHAR(30) NOT NULL,
    dateOfBirth DATE NOT NULL,
    gender CHAR(1) NOT NULL,
    CONSTRAINT person_pkey PRIMARY KEY (passportNumber),
    CONSTRAINT person_ckey1 UNIQUE (firstName , lastName , dateOfBirth)
);

CREATE TABLE CHEF (
    culinaryCerts VARCHAR(300) NOT NULL,
    competitionEXpr VARCHAR(300) NULL,
    passportNumber VARCHAR(20) NOT NULL,
    firstName VARCHAR(30) NOT NULL,
    lastName VARCHAR(30) NOT NULL,
    dateOfBirth DATE NOT NULL,
    CONSTRAINT chef_pkey PRIMARY KEY (passportNumber),
    CONSTRAINT chef_ckey1 UNIQUE (firstName , lastName , dateOfBirth),
    CONSTRAINT chef_fkey1 FOREIGN KEY (passportNumber)
        REFERENCES PERSON (passportNumber)
        ON DELETE CASCADE,
    CONSTRAINT chef_fkey2 FOREIGN KEY (firstName , lastName , dateOfBirth)
        REFERENCES PERSON (firstName , lastName , dateOfBirth)
        ON DELETE CASCADE
);

✓

✓

db<>fiddle here