How do sql for a field in a non-primary table become foreign in another table?

Question

How do sql for a field in a non-primary table become foreign in another table?

Navigation

#1 by (2 votes)
#2 by (1 votes)

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 PERSON table to be foreign key in FK_CPF of the SIGNATURE table

mysql sql

asked by anonymous 13.05.2016 / 21:01

2 answers

When my class returns a new modified instance of the class itself instead of modifying itself is this some design pattern? PHP 5.5 implemented the foreach functionality with list. What are the benefits of this?

score 2 · Answer 1

In the script that creates the cadastral table you should 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 a single record of cpf for each row of the PERSON table and add the line 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)
)

score 1 · Answer 2

You can make CPF a UniqueKey .

This is to make it easier, because, in fact, it is only necessary that the column be 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 it 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)
)