Home / Questions / 72527
Accepted
HeckDan
Asked: 2020-05-24 15:05:58 +0800 CST 2020-05-24 15:05:58 +0800 CST

CREATE SCHEMA or DATABASE has any difference? and placing the CONSTRAINT before a FK is necessary?

  • 772

I have these doubts in SQL... it is a not so strict language and it runs despite the different ways of writing it... What exactly is the difference between CREATE SCHEMA and CREATE DATEBASE when I started to see the code generated by Workbech, it works with SCHEMA and really, that word has me a bit sick... all my short life of studying sql had been DATABASE: S

On the other hand, taking advantage of the question... the Workbench also makes use of the CONSTRAINT in front of the FOREIGNKEY

Here is an example of code generated by Workbench

CREATE TABLE IF NOT EXISTS `tallermecanico_hd`.`e_vehiculos` (
`placa` INT NOT NULL,
`marca` VARCHAR(25) NULL,
`modelo` VARCHAR(25) NULL,
`anio` VARCHAR(4) NULL,
`observ` VARCHAR(255) NULL,
`serial` VARCHAR(25) NULL,
`color` VARCHAR(25) NULL,
`puertas` INT,
`motor` VARCHAR(25) NULL,
`transmision` VARCHAR(25) NULL,
//<<<aqui yo colocaria el campo foraneo cedula_c pero workbench no lo hizo<<<
PRIMARY KEY (`placa`),
CONSTRAINT `cedula_c`
FOREIGN KEY ()
REFERENCES `tallermecanico_hd`.`c_clientes` ()
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;

I was used to just placing the Foreignkey like this:

FOREINGN KEY(cedula_c)REFERENCES c_clientes

and if it works the same but what is the difference D: what does the CONSTRAINT do??? In addition, the Workbench uses the name of the database. name of the table. In addition, I do not put anything inside the parentheses of references and it does not create the foreign field cedula_c

How do I look for the theme of the points... [db].[table] what is that called that point is the same as putting FROM or something similar? that has some name to look for that topic well that nobody gave me >.<

sql

1 Answers

  1. Best Answer

    I answer your first question only. I think that, according to the rules of the site, it is better to ask one question per case, because it is more useful for the future and because answering several questions could require too much effort and the same question could be closed because it is considered too broad.

    Then:

    What exactly is the difference between CREATE SCHEMA and CREATE DATEBASE

    The exact difference is only one, they are called different, but they do the same thing. This is what the MySQL documentation states:

    CREATE DATABASEcreates a database with the given name. To use this statement, you need the CREATEprivilege for the database. CREATE SCHEMAis a synonym for CREATE DATABASE.

    CREATE DATABASE creates a database with the given name. To use this statement, the privilege CREATEto the database is required. CREATE SCHEMAis synonymous withCREATE DATABASE .

    CREATE DATABASEin the MySQL documentation

    Also in the MySQL glossary it says the following:

    scheme

    Conceptually, a schemais an interrelated set of database objects, such as tables, table columns, column data types, indexes, foreign keys, and so on. These objects are connected through SQL syntax, because columns represent tables, foreign keys refer to tables and columns, and so on. Ideally, they are logically connected in that they work together as part of a unified application or flexible framework. For example, in databases INFORMATION_SCHEMAand performance_schema use " schema" in their names to emphasize the close relationships between the tables and columns they contain.

    In MySQL, physically, schemaand databaseare synonyms, so in SQL syntax you can use the keyword SCHEMAinstead of DATABASE, for example, instead of using CREATE SCHEMAyou can use CREATE DATABASE.

    Some other database products make a distinction. For example, in the Oracle database product, a schemarepresents only one part of a database diagram: the tables and other objects owned by a single user.

    schemain the MySQL glossary

    PS: I think the other question should be asked separately, you will get better answers and it will be more useful to the rest of the SO community.

    • 1