How to update table with foreign key

Question

How to update table with foreign key

Navigation

#1 by (1 votes)

-1

I have table discipline and teacher table. The teacher contains discipline. How do I change the name of the course and automatically change the tb teachers?

I'm kind of confused about using On update cascade and delete Casdade .. It deletes and modifies all the children, but how do I use the cascade update in the discipline table if it has only the primary key?

public void createTableDisciplina(){
    try {
        conn.createStatement().execute("CREATE TABLE Disciplina(Nome varchar(50) NOT NULL primary key)");
    } catch (SQLException ex) {
        Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
    }
}

public void createTableProfessor(){
    try {
        conn.createStatement().execute("CREATE TABLE Professor(Nome varchar(50) NOT NULL primary key, Nome_Disciplina varchar(50) NOT NULL references Disciplina(Nome) ON DELETE Cascade, NumAulas int NOT NULL, NumFaltas int)");
    } catch (SQLException ex) {
        Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
    }
}

mysql sql java

asked by anonymous 05.07.2016 / 20:24

1 answer

Print Alert PHP [duplicate] How to pass activity values to util?

score 1 · Accepted Answer

Arthur, the ideal is that since there is a relationship between both tables only one of them (the discipline table) contains the name of the discipline.

The relationship is just this ... the teacher table is related to the table discipline through the id of this discipline (immutable) and made the relationship we know the name of the discipline which has the id linked to a teacher

Considering this implementation you would only need to change the name of the discipline in the discipline table and would not have to worry about the rest because the relationship would "translate" the id to the name or other columns you want from the discipline table. / p>

Edited:

Arthur the source of the problem is in the way you are creating the table.

A PRIMARY KEY can never be a value that you can change, it must be an incremental and unique number for each row in your table, so I would create an integer ID field in both tables as the primary key and create an integer field in the class table which would receive the same id of the course you want to link, for example:

CREATE TABLE disciplina (
    /* NOT NULL é dispensável considerando que uma chave primaria não pode ser nula. */
    id INTEGER PRIMARY KEY, 
    nome VARCHAR(100) NOT NULL
);

CREATE TABLE professor (
    id INTEGER PRIMARY KEY,
    nome varchar(100) NOT NULL,
    /*não utilizarei DELETE CASCADE pois se utilizar e um professor for removido a disciplina também será...*/
    id_disciplina INTEGER NOT NULL REFERENCES disciplina(nome),
    numAulas INTEGER NOT NULL,
    numFaltas INTEGER
);

INSERT INTO disciplina VALUES(0 /*id da disciplina*/, 'Português');

INSERT INTO professor(id, nome, id_disciplina, numAulas) 
VALUES(0 /*id do professor*/, 'Zé do Caroço', 0 /*id da disciplina relacionada*/, 0 /*numAulas*/);

/*Este SELECT retornará o nome do professor e de sua disciplina relacionada.*/
SELECT professor.nome, disciplina.nome
FROM professor
INNER JOIN disciplina ON professor.id_disciplina = disciplina.id;