mysql – How to make sql for a field of a table that is not primary become foreign in another table?

Question:

CREATE TABLE PESSOA
(   
    ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,   
    CPF VARCHAR(15) NOT NULL,
    RG VARCHAR(10) NOT NULL,
    NOME VARCHAR(128) NOT NULL,
    DATA_NASCIMENTO DATE,
    PRIMARY KEY (ID)
)

CREATE TABLE CADASTRO
(
    ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    RA INTEGER UNSIGNED NOT NULL,
    NOME VARCHAR(128),
    NOTA_TEORICA NUMERIC(10,2),
    NOTA_LAB NUMERIC(10,2),
    MEDIA NUMERIC(10,5),
    FK_CPF INTEGER UNSIGNED NOT NULL,
    PRIMARY KEY(ID)
)

I need the CPF field of the PERSON table to be a foreign key in FK_CPF of the CADASTRO table

Answer:

In the script that creates the registration table, you must change the cpf column of the person table to unique as follows:

CPF VARCHAR(15) UNIQUE NOT NULL e na tabela de cadastro 

FK_CPF VARCHAR(15) NOT NULL

This will allow other tables to have an fk referencing this column because UNIQUE will ensure that there can only be a single cpf record for each row of the PESSOA table and add the row to FK

FOREIGN KEY FK_KEY_CPF (FK_CPF) REFERENCES PESSOA(CPF)

as specified below

 CREATE TABLE PESSOA
(   
    ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,   
    CPF VARCHAR(15) UNIQUE NOT NULL,
    RG VARCHAR(10) NOT NULL,
    NOME VARCHAR(128) NOT NULL,
    DATA_NASCIMENTO DATE,
    PRIMARY KEY (ID)
    UNIQUE(CPF)
)

CREATE TABLE CADASTRO
(
    ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    RA INTEGER UNSIGNED NOT NULL,
    NOME VARCHAR(128),
    NOTA_TEORICA NUMERIC(10,2),
    NOTA_LAB NUMERIC(10,2),
    MEDIA NUMERIC(10,5),
    FK_CPF VARCHAR(15) NOT NULL,
    PRIMARY KEY(ID),

    FOREIGN KEY FK_KEY_CPF (FK_CPF) REFERENCES PESSOA(CPF)
)
Post Views: 0
Scroll to Top