Creating a simple database with NOT EXISTS

Question

Every time I create a DER(Entity-Relationship Diagram) in a database, and then type this diagram in the Entity-Relationship Model, to scan the created diagram and also to perform the insertion of records and their respective queries in the database. Whenever I start creating a table, I type the following commands:

USE db_evento;
CREATE TABLE tb_aluno(
	id_aluno INT(6) NOT NULL PRIMARY KEY,
	cd_rm_aluno INT(5),
	nm_aluno VARCHAR(100) NOT NULL,
	dt_nascimento_aluno DATE NOT NULL
);

When I put this code to run on Mysql, the first line is executed without any exception, but in the second line, the program recognizes a logical error by saying that the table tb_aluno already exists. An error code of nº 1050.

To solve this problem, I put the IF NOT EXISTS next to the CREATE TABLE to eliminate the 1050 error and create an existing table in the template, only I typed the following code and the console display returned these results:

CREATE TABLE IF NOT EXISTS tb_aluno( // CRIAR TABELA SE NÃO EXISTIR tb_aluno
	id_aluno INT(6) NOT NULL PRIMARY KEY,
	cd_rm_aluno INT(5),
	nm_aluno VARCHAR(100) NOT NULL,
	dt_nascimento_aluno DATE NOT NULL
);

Result in the code snippet above in Mysql:

---

Result in checking all tables I’ve created within Mysql:

This way I got confused when using this IF NOT EXISTS when it comes to implementing this operator in all the tables I’ve done. There is another way to create tables without this type of alert in the console, so that all lines of code are checked without any error?

Answer 1

The Error Code 1050 is given by the account that there is already a table with the same name that you are trying to execute in your query.

There is another way to create tables without this kind of alert on console, so that all lines of code are checked without any mistake?

The result of its verification in other tables already created in Mysql is not considered an error, but an alert(Warning) which by default, your SGBD informs you that there is a table already created. Your query is not executed, because, just as you mentioned, only // CREATE TABLE IF IT DOES NOT EXIST, as it already existed, so no code was executed.

Solutions to your problem

1. If you want to keep the records that have already been registered in your table, I advise you to use the command ALTER TABLE and add the new modifications to your existing table.
2. If the data already registered is not so relevant to you, I advise you to execute DROP TABLEIF EXISTS 'suatabela' ; and perform his query rearing.

Answer 2

This way there is some problem in using DROP before CREATE?

cd_rm_aluno I used it as an assumption, I don’t know which key reference you use, so you should change.

ALTER TABLE `tb_outra`
  ADD CONSTRAINT `sua_restricao` FOREIGN KEY (`cd_rm_aluno`) 
      REFERENCES `tb_aluno` (`cd_rm_aluno`);

DROP TABLE IF EXISTS `tb_aluno`;

CREATE TABLE IF NOT EXISTS `tb_aluno` (
  `id_aluno` int(11) NOT NULL AUTO_INCREMENT,
  `nm_aluno` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
COMMIT;