Error in primary and secondary keys

Asked

Viewed 150 times

0

Well, I tried to normalize this data table as much as I could, but I failed to get the code, where I’m not identifying the user error:

Table:inserir a descrição da imagem aqui

Error: inserir a descrição da imagem aqui

Error 2:

 -- Geração de Modelo físico
-- Sql ANSI 2003 - brModelo.



CREATE TABLE Data_Evento (
id_data_evento INTEGER PRIMARY KEY,
data_inicio DATE,
data_fim DATE
);

CREATE TABLE Hora_Evento (
id_hora INTEGER PRIMARY KEY,
id_evento INTEGER,
hora_inicio TIME,
hora_fim TIME
);

CREATE TABLE Usuario (
id_usuario INTEGER PRIMARY KEY,
id_aluno INTEGER,
CPF VARCHAR(),
RG VARCHAR(),
usuario VARCHAR(255),
senha VARCHAR(255),
imagem LONGTEXT
);

CREATE TABLE Controle (
id_controle INTEGER PRIMARY KEY,
id_aluno INTEGER,
CPF VARCHAR(),
RG VARCHAR(),
data_cadastro DATETIME,
data_entrada DATETIME,
data_saida DATETIME
);

CREATE TABLE aluno_curso (
id_aluno_curso INTEGER PRIMARY KEY,
id_aluno INTEGER,
CPF VARCHAR(),
RG VARCHAR(),
id_curso INTEGER
);

CREATE TABLE Noticia (
id_noticia INTEGER PRIMARY KEY,
titulo VARCHAR(255),
autor VARCHAR(255),
descricao LONGTEXT,
data DATE,
fonte LONGTEXT,
imagem LONGTEXT,
imagem_2 LONGTEXT,
imagem_3 LONGTEXT
);

CREATE TABLE Telefone_Professor (
id_telefone_professor INTEGER PRIMARY KEY,
id_professor INTEGER,
telefone VARCHAR(9),
celular VARCHAR(10)
);

CREATE TABLE Evento (
id_evento INTEGER PRIMARY KEY,
id_data_evento INTEGER,
id_hora INTEGER,
titulo VARCHAR(255),
descricao LONGTEXT,
preco VARCHAR(10),
imagem LONGTEXT,
FOREIGN KEY(id_data_evento) REFERENCES Data_Evento (id_data_evento),
FOREIGN KEY(id_hora) REFERENCES Hora_Evento (id_hora)
);

CREATE TABLE Professor (
id_professor INTEGER PRIMARY KEY,
id_especialidade INTEGER,
id_telefone_professor INTEGER,
id_aula INTEGER,
nome VARCHAR(255),
sobrenome VARCHAR(255),
email VARCHAR(255),
Imagem LONGTEXT,
descricao LONGTEXT,
FOREIGN KEY(id_telefone_professor) REFERENCES Telefone_Professor (id_telefone_professor)
);

CREATE TABLE Aula (
id_aula INTEGER PRIMARY KEY,
tipo VARCHAR(255),
data DATE,
duracao TIME
);

CREATE TABLE Promocao (
id_promocao INTEGER PRIMARY KEY,
id_curso INTEGER,
titulo VARCHAR(255),
promocao VARCHAR(255),
validade DATE,
descricao LONGTEXT,
imagem LONGTEXT
);

CREATE TABLE Curso (
id_curso INTEGER PRIMARY KEY,
titulo VARCHAR(255),
descricao LONGTEXT,
carga VARCHAR(255),
imagem LONGTEXT
);

CREATE TABLE Aluno_Evento (
id_aluno_evento INTEGER PRIMARY KEY,
id_aluno INTEGER,
id_evento INTEGER,
CPF VARCHAR(),
RG VARCHAR(),
FOREIGN KEY(id_evento) REFERENCES Evento (id_evento)
);

CREATE TABLE Endereco (
id_endereco INTEGER PRIMARY KEY,
id_aluno INTEGER,
CPF VARCHAR(),
RG VARCHAR(),
rua LONGTEXT,
numero VARCHAR(10),
cep VARCHAR(14),
pais VARCHAR(255),
estado VARCHAR(2),
cidade VARCHAR(255)
);

CREATE TABLE Aluno (
id_aluno INTEGER,
CPF VARCHAR(14),
RG VARCHAR(12),
id_usuario INTEGER,
id_controle INTEGER,
id_aluno_promocao INTEGER,
nome VARCHAR(255),
sobrenome VARCHAR(255),
email VARCHAR(255),
nacionalidade VARCHAR(255),
PRIMARY KEY(id_aluno,CPF,RG),
FOREIGN KEY(id_usuario) REFERENCES Usuario (id_usuario),
FOREIGN KEY(id_controle) REFERENCES Controle (id_controle)
);

CREATE TABLE Telefone (
id_telefone INTEGER PRIMARY KEY,
id_aluno INTEGER,
CPF VARCHAR(),
RG VARCHAR(),
telefone VARCHAR(9),
celular VARCHAR(10),
FOREIGN KEY(RG,,,) REFERENCES Aluno (id_aluno,CPF,RG)
);

CREATE TABLE Modulo (
id_modulo INTEGER PRIMARY KEY,
id_curso INTEGER,
id_professor INTEGER,
titulo VARCHAR(255),
descricao LONGTEXT,
conteudo LONGTEXT,
imagem LONGTEXT,
imagem_2 LONGTEXT,
preco VARCHAR(100),
FOREIGN KEY(id_curso) REFERENCES Curso (id_curso),
FOREIGN KEY(id_professor) REFERENCES Professor (id_professor)
);

CREATE TABLE Especialidade (
id_especialidade INTEGER PRIMARY KEY,
especialidade LONGTEXT,
formacao LONGTEXT
);

CREATE TABLE Aluno_Promocao (
id_aluno_promocao INTEGER PRIMARY KEY,
id_aluno INTEGER,
CPF VARCHAR(),
RG VARCHAR(),
id_promocao INTEGER,
FOREIGN KEY(RG,,,) REFERENCES Aluno (id_aluno,CPF,RG),
FOREIGN KEY(id_promocao) REFERENCES Promocao (id_promocao)
);

ALTER TABLE Hora_Evento ADD FOREIGN KEY(id_evento) REFERENCES Evento (id_evento)
ALTER TABLE Usuario ADD FOREIGN KEY(/*erro: ??*/) REFERENCES Aluno (id_aluno,CPF,RG)
ALTER TABLE aluno_curso ADD FOREIGN KEY(id_curso) REFERENCES Curso (id_curso)
ALTER TABLE Telefone_Professor ADD FOREIGN KEY(id_professor) REFERENCES Professor (id_professor)
ALTER TABLE Professor ADD FOREIGN KEY(id_especialidade) REFERENCES Especialidade (id_especialidade)
ALTER TABLE Professor ADD FOREIGN KEY(id_aula) REFERENCES Aula (id_aula)
ALTER TABLE Promocao ADD FOREIGN KEY(id_curso) REFERENCES Curso (id_curso)
ALTER TABLE Aluno ADD FOREIGN KEY(id_aluno_promocao) REFERENCES Aluno_Promocao (id_aluno_promocao)

You are indicating a user error

database

  • Could post error message for better identification?

  • Of course, bruno101.

  • 1

    Sorry, just a minute I change now. How can I put the photo danieltakeshi without having to put the image code?

  • To post the code as text just copy and paste here the SQL code select all of it and click on the Code option. or give 4 spaces on each line that is code.

  • Okay, thank you Will.

  • I edited now, that’s right... Correct? Or I didn’t understand very well the statement of danieltakeshi. It was just edit an image or all?

  • that there, I believe that only the image that contained the code the other has no way to write.

  • ps: tries to define the varchar() that is empty to varchar(20)

  • To avoid a long discussion about solving the problem, we better open the chat, please call me there, I don’t know how it works. PS: The varchar has no way to change, because it is from the foreign key and already comes empty (it is pulling from the student)

Show 4 more comments

2 answers

0

The foreign key must have the referenced fields that are the primary key in the referenced table.

In this case you are referencing the table key Pupil and this key consists of 3 fields: id_student, CPF and RG. Thus all tables referencing the table Pupil shall contain the 3 fields id_student, CPF and RG.

On the table Aluno_promocao, for example, it would look like this:

FOREIGN KEY(id_aluno,CPF,RG) REFERENCES Aluno (id_aluno,CPF,RG)

  • This includes tables N:N?

  • think a possible solution not to be necessary the replication of the 3 fields for the tables, are to transform CPF and RG in single indices and id_student be the only primary key.

  • Same user error occurred again.

0

Errors found:

  • Student Table With more than 1 Primary Key, you can only have 1 Primary key by table.
  • Foreign key statement missing parameters

ps: it was also declared the size of the Varchar for CPF’s and RG’s that were without, if you want you can change the value as long as you do not leave them blank.

Test SQL Now to see any questions I’m available.

-- Geração de Modelo físico
-- Sql ANSI 2003 - brModelo.



CREATE TABLE Data_Evento (
id_data_evento INTEGER PRIMARY KEY,
data_inicio DATE,
data_fim DATE
);

CREATE TABLE Hora_Evento (
id_hora INTEGER PRIMARY KEY,
id_evento INTEGER,
hora_inicio TIME,
hora_fim TIME
);

CREATE TABLE Usuario (
id_usuario INTEGER PRIMARY KEY,
id_aluno INTEGER,
CPF VARCHAR(20),
RG VARCHAR(20),
usuario VARCHAR(255),
senha VARCHAR(255),
imagem LONGTEXT
);

CREATE TABLE Controle (
id_controle INTEGER PRIMARY KEY,
id_aluno INTEGER,
CPF VARCHAR(20),
RG VARCHAR(20),
data_cadastro DATETIME,
data_entrada DATETIME,
data_saida DATETIME
);

CREATE TABLE aluno_curso (
id_aluno_curso INTEGER PRIMARY KEY,
id_aluno INTEGER,
CPF VARCHAR(20),
RG VARCHAR(20),
id_curso INTEGER
);

CREATE TABLE Noticia (
id_noticia INTEGER PRIMARY KEY,
titulo VARCHAR(255),
autor VARCHAR(255),
descricao LONGTEXT,
data DATE,
fonte LONGTEXT,
imagem LONGTEXT,
imagem_2 LONGTEXT,
imagem_3 LONGTEXT
);

CREATE TABLE Telefone_Professor (
id_telefone_professor INTEGER PRIMARY KEY,
id_professor INTEGER,
telefone VARCHAR(9),
celular VARCHAR(10)
);

CREATE TABLE Evento (
id_evento INTEGER PRIMARY KEY,
id_data_evento INTEGER,
id_hora INTEGER,
titulo VARCHAR(255),
descricao LONGTEXT,
preco VARCHAR(10),
imagem LONGTEXT,
FOREIGN KEY(id_data_evento) REFERENCES Data_Evento (id_data_evento),
FOREIGN KEY(id_hora) REFERENCES Hora_Evento (id_hora)
);

CREATE TABLE Professor (
id_professor INTEGER PRIMARY KEY,
id_especialidade INTEGER,
id_telefone_professor INTEGER,
id_aula INTEGER,
nome VARCHAR(255),
sobrenome VARCHAR(255),
email VARCHAR(255),
Imagem LONGTEXT,
descricao LONGTEXT,
FOREIGN KEY(id_telefone_professor) REFERENCES Telefone_Professor (id_telefone_professor)
);

CREATE TABLE Aula (
id_aula INTEGER PRIMARY KEY,
tipo VARCHAR(255),
data DATE,
duracao TIME
);

CREATE TABLE Promocao (
id_promocao INTEGER PRIMARY KEY,
id_curso INTEGER,
titulo VARCHAR(255),
promocao VARCHAR(255),
validade DATE,
descricao LONGTEXT,
imagem LONGTEXT
);

CREATE TABLE Curso (
id_curso INTEGER PRIMARY KEY,
titulo VARCHAR(255),
descricao LONGTEXT,
carga VARCHAR(255),
imagem LONGTEXT
);

CREATE TABLE Aluno_Evento (
id_aluno_evento INTEGER PRIMARY KEY,
id_aluno INTEGER,
id_evento INTEGER,
CPF VARCHAR(20),
RG VARCHAR(20),
FOREIGN KEY(id_evento) REFERENCES Evento (id_evento)
);

CREATE TABLE Endereco (
id_endereco INTEGER PRIMARY KEY,
id_aluno INTEGER,
CPF VARCHAR(20),
RG VARCHAR(20),
rua LONGTEXT,
numero VARCHAR(10),
cep VARCHAR(14),
pais VARCHAR(255),
estado VARCHAR(2),
cidade VARCHAR(255)
);

CREATE TABLE Aluno (
id_aluno INTEGER,
CPF VARCHAR(14),
RG VARCHAR(12),
id_usuario INTEGER,
id_controle INTEGER,
id_aluno_promocao INTEGER,
nome VARCHAR(255),
sobrenome VARCHAR(255),
email VARCHAR(255),
nacionalidade VARCHAR(255),
PRIMARY KEY(id_aluno),
FOREIGN KEY(id_usuario) REFERENCES Usuario (id_usuario),
FOREIGN KEY(id_controle) REFERENCES Controle (id_controle)
);

CREATE TABLE Telefone (
id_telefone INTEGER PRIMARY KEY,
id_aluno INTEGER,
CPF VARCHAR(20),
RG VARCHAR(20),
telefone VARCHAR(9),
celular VARCHAR(10),
FOREIGN KEY(id_aluno,CPF,RG) REFERENCES Aluno (id_aluno,CPF,RG)
);

CREATE TABLE Modulo (
id_modulo INTEGER PRIMARY KEY,
id_curso INTEGER,
id_professor INTEGER,
titulo VARCHAR(255),
descricao LONGTEXT,
conteudo LONGTEXT,
imagem LONGTEXT,
imagem_2 LONGTEXT,
preco VARCHAR(100),
FOREIGN KEY(id_curso) REFERENCES Curso (id_curso),
FOREIGN KEY(id_professor) REFERENCES Professor (id_professor)
);

CREATE TABLE Especialidade (
id_especialidade INTEGER PRIMARY KEY,
especialidade LONGTEXT,
formacao LONGTEXT
);

CREATE TABLE Aluno_Promocao (
id_aluno_promocao INTEGER PRIMARY KEY,
id_aluno INTEGER,
CPF VARCHAR(20),
RG VARCHAR(20),
id_promocao INTEGER,
FOREIGN KEY(id_aluno,CPF,RG) REFERENCES Aluno (id_aluno,CPF,RG),
FOREIGN KEY(id_promocao) REFERENCES Promocao (id_promocao)
);

ALTER TABLE Hora_Evento ADD FOREIGN KEY(id_evento) REFERENCES Evento (id_evento)
ALTER TABLE Usuario ADD FOREIGN KEY(id_aluno,CPF,RG) REFERENCES Aluno (id_aluno,CPF,RG)
ALTER TABLE aluno_curso ADD FOREIGN KEY(id_curso) REFERENCES Curso (id_curso)
ALTER TABLE Telefone_Professor ADD FOREIGN KEY(id_professor) REFERENCES Professor (id_professor)
ALTER TABLE Professor ADD FOREIGN KEY(id_especialidade) REFERENCES Especialidade (id_especialidade)
ALTER TABLE Professor ADD FOREIGN KEY(id_aula) REFERENCES Aula (id_aula)
ALTER TABLE Promocao ADD FOREIGN KEY(id_curso) REFERENCES Curso (id_curso)
ALTER TABLE Aluno ADD FOREIGN KEY(id_aluno_promocao) REFERENCES Aluno_Promocao (id_aluno_promocao)

Some tips:

  • Never declare variables with the first letter uppercase, as far as I know changes practically nothing, but for maintenance and organization of the code is better.

    Ex: [Incorrect] CPF VARCHAR(255) [correct] Cpf VARCHAR(255)

  • Well, it’s not wanting a more technical answer like this, but how do I apply a composite key to a table? Because what I really wanted was to apply the composite key to this example. http://www.vbweb.com.br/forum_resp.asp?Forum=VB&Codigo=221035 has another syntax or something like??! So, about the number and ID are in white he doesn’t let me change the table, he calls directly already the relationship without I can touch. Now the CPF and the RG maisculos I will change as you said. Thank you very much Will, if you can clarify further thank you.

  • Look quickly, I answer better when I get home, would you use in Cpf and rg like this: Cpf VARCHAR(255) UNIQUE, with the attribute UNIQUE vc says that this value should be unique would be a type Primary key, IE, can not have another with the same value in the same table.

  • Got it, thanks.

Browser other questions tagged database

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