It is certainly important to understand that the post Insert database records will deal with content about SQL language – INSERT INTO, more specifically how to insert records into a database table. Firstly, when pressing to insert records in a Database (DB) it is important to have the concepts of Entity Relationship Model (ER model) and its transformation into Database tables (DB).
Concept Entity x Table
Therefore, it is essential to remember some concepts related to data modeling, where one of its main objects are entities, containers that are responsible for storing the data of a specific entity. However, these entities are transformed into tables with their fields, these are the objects used by a database to store its records.
Figure 1: Entity to table transformation.
According to Figure 1, the entity PESSOA, which presents the attributes ID, NOME, GENERO e DATA NASCIMENTO, in this type of artifact there is no concern in which the database will be implemented, being just a logical representation.
In the transition to the physical representation, it is important to define the database, in this case ORACLE, because the fields must have the types defined when transformed into tables.
In fact, the representation that matters is the table, which is implemented in the DB and which will suffer the action of the INSERT INTO command.
In the meantime, note that the symbology (PK), *, o; in front of the table fields represent respectively: Primary Key, does not allow null (NOT NULL) and allows null (NULL).
Syntax INSERT INTO
Finally, we can now move on to the syntax of the INSERT INTO command. Just to illustrate, as shown below, it has as essential components besides its syntax, the table_name; the name of, or the name of the columns and the respective values for each defined column.
INSERT INTO table_name
[(column_name[, column_name_2])]
VALUES
(column_value [, column_value]);
On the other hand, it is important to point out some aspects about the application of this command:
- On the other hand, it is important to point out some aspects about the application of this command: An INSERT INTO command applies the insertion of the record in a single table.
- An INSERT INTO command inserts one record line at a time.
- It is possible to omit columns when applying an INSERT INTO command, but the values for each column must appear in the same order as the table was created; in this case, even columns that allow null must be given values.
- Problem of the situation above, one can unintentionally invert compatible values between columns, by passing values in the order different from the creation of the table – a situation difficult to fix after being put into production.
- Thus, this practice of omitting columns in the application of the INSERT INTO command is not advisable, because whenever the structure of a table is modified, all applications of this command will have to be reviewed.
Practical Experiment
To generate the practical experiment, the PESSOAtable presented previously in Figure 1 will be created. Command to create the table PESSOA:
CREATE TABLE PESSOA(
ID NUMBER(10) PRIMARY KEY,
NOME VARCHAR2(120) NOT NULL,
GENERO CHAR(1) NOT NULL,
DATA_NASCIMENTO DATE NULL,
CONSTRAINT CK_GENERO_PESSOA CHECK (GENERO IN ('M', 'F'))
);
Just to exemplify, this practical experiment can be practiced in a virtual machine, or a normal machine that has a version of ORACLE installed (it can be the Express Edition version). Or, you can use Oracle Live SQL, an online version via browser for testing provided by Oracle itself (you just need to have a valid account on their website). This article will use the Oracle Live SQL online tool to carry out practical experiments. The table has already been created PESSOA, it’s time to apply the commands to insert records.
Applying INSERT INTO Commands
INSERT INTO PESSOA
VALUES (1, 'MARIA', 'F', TO_DATE('12/03/1989','DD/MM/YYYY'));
-- 1 row(s) inserted
INSERT INTO PESSOA
VALUES (2, 'PEDRO', 'X', TO_DATE('05/10/1972','DD/MM/YYYY'));
-- erro relacionado a constraint de CHECK - CK_GENERO_PESSOA
INSERT INTO PESSOA
VALUES ('PEDRO', 2, 'M', TO_DATE('05/10/1972','DD/MM/YYYY'));
-- erro relacionado a inversão de valores entre ID e NOME
INSERT INTO PESSOA
VALUES (2, 'CLAUDIA', 'F', '12/03/2001');
-- erro nem todas as instalação do ORACLE
-- (depende da regionalização) aceita a
-- data direta
INSERT INTO PESSOA
VALUES (2, 'CLAUDIA', 'F');
-- ORA-00947: not enough values
INSERT INTO PESSOA
(ID, NOME, GENERO, DATA_NASCIMENTO)
VALUES (3, 'PAULO', 'M', TO_DATE('12/03/2006','DD/MM/YYYY'));
-- 1 row(s) inserted
Namely, all values related to text fields, such as columns NOME and GENERO must be between apostrophes. On the other hand, to accurately pass the date to ORACLE, it is important to use the TO_DATE function. Function that converts text that is enclosed in apostrophes into a date in the following format, as in the field DATA_NASCIMENTO.
Explanation of SCRIPT above
The INSERT INTO of the line 1 insert record into table:
- Because regardless of not having the name of the columns, it passes the values in the correct order, noting that it is not mandatory to inform the columns, as long as it respects the order of creation of the table.
The INSERT INTO of the line 4 does not insert record into table:
- Because it breaks a rule (constraint) that was applied in the construction of the table that allows only the values M or F to the field GENERO.
The INSERT INTO of the line 7 does not insert record into table:
- Because it violates the order of the fields, there is an inversion in the value, the value of the NOME (PEDRO) came before the value of ID (2).
The INSERT INTO of the line 10 does not insert record into table:
- Because not all installations accept to pass the date as a text without using the TO_DATE function.
- Because the date format is not always recognized automatically, it is the first month (American format), or the first day (Latin format).
The INSERT INTO of the line 15 does not insert record into table:
- Because when the columns are not informed in VALUES, you must have values for all columns in the order of creation of the table columns. In this case, the value for the DATA_NASCIMENTO and will return the error ORA-00947: not enough values.
The INSERT INTO of the line 18 insert record into table:
Because it defines the order of the columns (ID, NOME, GENERO, DATA_NASCIMENTO) and respects this order in passing the values, where the first value enters the first column, the second value in the second column and so on.
Just to exemplify, when you define the columns they don’t need to be in the order of creation, it can be a different order, as long as they change the order of the values as well. However, when informing the columns, you must obligatorily place the NOT NULL and PK columns, that is, you can only omit those that allow NULL. In fact, it is important to note that the practical experiments will be applied to a single table, in this case the PESSOA. And it will not deal with the topic of insertion in related tables, which will be part of another separate article.
Features
Reinforcing the features of the command:
- Inserts only one record at a time into a single table.
- It may or may not inform all columns.
- If you inform the columns you have to put all the mandatory ones.
- In transactional databases, INSERTS are only effective using the commit, if you don’t want it to be effective, you can cancel with rollback.
- It is not necessary to inform the columns, you can follow the values according to the order of creation of the fields.
- Critical point, if the table is recreated in a different order, you can put value in swapped columns (tip always put the columns with the respective values);
- Date field, or date time, use the to_date function to inform value (this prevents date inversion due to American or Latin format).
Conclusion
Finally, the insertion of records in the tables of a database is the step after creating the data model that will be applied. Thus, being an important activity to the process. Therefore, the focus of this article (post) was to present in a simple and practical way the subject of inserting records in an ORACLE database table. However, the use of a methodology based on practical experiments aimed at a problem helps in understanding and facilitates further deepening of the topic. To see more articles related to Database, click here.