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

Asked

Viewed 912 times

3

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 PESSOA table to be a foreign key in FK_CPF of the CADASTRO table

mysql sql

  • Out of curiosity, why don’t you use the ID as FK? Are you sure you did the modeling correctly? If the CPF is not PK or UK, it will not be possible to register two people with the same CPF?

  • In this specific case I need the CPF, but it could also be UK, not even thought about it ... how would sql then so that it is UK in the PERSON table and that is referenced in FK_CPF ?

  • CPF is varchar or integer?

  • Cpf is a varchar

2 answers

2

In the script that creates the table register 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 a fk referencing this column because UNIQUE will ensure that there can only be one single Cpf record for each row of the PERSON 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)
)

1

You can make CPF, be a UniqueKey.

This is to facilitate, because, in fact, it is only necessary that the column is indexed.

Source documentation:

Additionally, Mysql requires that the referenced Columns be Indexed for performance reasons.

Example with UniqueKey:

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),
    UNIQUE(CPF)
)

Then just reference normally.

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 UNSIGNED NOT NULL,
    PRIMARY KEY(ID),
    FOREIGN KEY (FK_CPF) REFERENCES PESSOA(CPF)
)

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.