Manipulating Records in DatabasesArticle contents:
Adding Data to Tables
Inserting New Data Records (INSERT)
Inserting new records into a relational database is done with the SQL statement INSERT. The general schema of an INSERT statement is shown in figure 32 and is described in detail in table 47.
|Table 47: Elements in the INSERT Statement|
|Elements of INSERT||Description|
|table||Name of the table into which the records are to be inserted. Exactly one table can be changed in each INSERT statement.|
|Optional:(column 1,…,column N)||Lists the names of the columns in which the values are to be entered. At least one column must be specified; the order of the columns can be freely selected.If the list of columns is omitted, the values for all columns of the table must be inserted.|
|(value1,…,valueN)||List of values to be inserted. The order of the values must match those in the column list.If no column list has been defined, the values must be specified for all columns in the table. The order is then given by the order of the columns in the database table specified in the CREATE TABLE statement.|
The following examples illustrate the INSERT statement using the following example table Voucher action, in which data on voucher actions of an online shop are stored:
CREATE TABLE voucher action ( ActionID INTEGER AUTO_INCREMENT, Start action TIMESTAMP NOT NULL, End action TIMESTAMP NOT NULL, Title VARCHAR (500) NOT NULL, Description TEXT, Voucher code VARCHAR(100) NOT NULL UNIQUE, PRIMARY KEY (ActionID) )
An example of inserting a new record into the Voucher Action table is shown in figure 33.
Filling Columns Not Specified with INSERT
Each INSERT command adds a new record to a table. A new line is appended to the bottom of the table. Although not all columns must be specified in the INSERT statement, values must also be entered in these missing columns. SQL provides the following mechanisms for a DBMS to handle missing values:
- AUTO_INCREMENT: The value of the attribute is generated by the DBMS and used for artificial keys.
- Default value: If a default value has been defined for this attribute, this value is saved for the new data record
- NULL value: If no default value has been specified, the DBMS saves a NULL value for this column. Note: Some DBMSs generate a default value instead of a NULL.
- Error message: If no default value has been specified and this column is defined with NOT NULL, an error message is issued and the execution of INSERT is terminated without changes to the DB.
Automatic Type Conversion
If, when inserting values into a table, a transferred value does not have the correct data type for the corresponding column, the data type is automatically converted (if possible). This conversion is lossy, i.e., information may be lost without notification during insertion. For example, character strings are truncated when the maximum length is exceeded, and numerical values are rounded. If the type conversion can be carried out without errors, a warning or error message will be returned by some DBMS but not by others, despite the loss of information. If a loss of information is to be avoided, a type check must take place in the business application before the values are transferred to the DBMS. The following example illustrates an automatic type conversion. After you have created Table t (shown here)
CREATE TABLE t (i INTEGER, d DECIMAL(5,2), f FLOAT, c VARCHAR(3), b BOOLEAN);
and the successful insertion of the two data records (shown here)
INSERT INTO t VALUES (1, 123.45, 78.46723, ’abc’, TRUE); INSERT INTO t VALUES (1.5, 12345.678, 78.46723456789, ’abcdefghij’, 23);
the values shown in figure 34 are stored in the database.
Complex Data Types
Complex data types such as DATE, TIME, and TIMESTAMP are supported by many DBMSs with conversion functions. As shown above, the values of these data types can be inserted into the database either directly or as parameters of the corresponding conversion functions with INSERT. How these functions are implemented and how they behave in the event of an error should be described in the DBMS manual. A NULL value is often generated when the conversion functions fail, while a default decay value is stored when saving without explicitly calling the conversion functions.
For example, the following table is created:
CREATE TABLE KTEST ( dateNull DATE, timeNull TIME, timestampNull TIMESTAMP, dateNotNull DATE NOT NULL, timeNotNull TIME NOT NULL, timestampNotNull TIMESTAMP NOT NULL )
And then the following INSERT statements are called:
INSERT INTO KTEST VALUES (’2014–11–11’, ’12:12:12’,’2014–11–11 12:12:12’, ’2014–11–11’, ’12:12:12’,’2014–11–11 12:12:12’); INSERT INTO KTEST VALUES (’2014’, ’122X’, ’2014 122’,’2014’, ’122’, ’2014 122’);
This is available in the DBMS (MySQL 5.6) of the following database:
|2014–11–11||12:12:12||2014–11–11 12:12:12||2014–11–11||12:12:12||2014–11–11 12:12:12|
|0000–00–00||00:01:22||0000–00–00 00:00:00||0000–00–00||00:01:22||0000–00–00 00:00:00|
However, if the data of the second INSERT statement are explicitly inserted with the following conversion function:
INSERT INTO KTEST VALUES (DATE(’2014’), TIME(’122X’), TIMESTAMP(’2014 122’), DATE(’2014’), TIME(’122’), TIMESTAMP(’2014 122’));
then the DBMS will report an error and will not enter this data record. If the conversion fails, the DBMS will try to save a NULL value here, but this is not possible with NOT-NULL attributes.
Copying Data Records
With SQL, it is possible to create new records by copying existing records. To do so, the INSERT statement is combined with the SELECT statement. As a prerequisite for this, the number of columns and their data types in the result table of the SELECT statement must match the table in which the data records are to be inserted. The general schema for copying records is as follows:
INSERT INTO table1 (column1,...,columnN) SELECT column1,...,column FROM table1 WHERE condition;
The following example creates new data in the table created above by copying:
INSERT INTO t SELECT * FROM t WHERE b=TRUE;
Preservation of Referential Integrity
Data that are related to a subject are often divided into several individual data records and distributed across several tables. When inserting such data records, special attention must be paid to ensuring that no incomplete data are included in the database even in the event of an error. Databases with the transactions presented in Section 6.3 support this requirement.
Changing Existing Records
Data records that already exist in the database can be changed. SQL offers the UPDATE and DELETE commands, which can be used to selectively change attributes in tables and completely delete data records.
Updating Data (UPDATE)
The UPDATE statement is used to change existing data records. The update schema is shown in figure 35.
UPDATE can be used to enter specific values in data records. An example of changing the value in the Availability field to the immediate string of a specific article stored in the Article table follows:
UPDATE Article SET Availability = 'immediately' WHERE ArticleNR='KJNSM7873';
In addition, UPDATE can be used to recalculate values with reference to the old value. Here is an example where the price of all items costing less than 10 EUR is increased by 2 EUR:
UPDATE Article SET price = price+2 WHERE price &lt; 10;
Each record in the table that matches the selection condition is changed. If no selection condition is formulated, all data records of the table are affected.
Deleting Data (DELETE)
The SQL statement DELETE is used to delete data records from the database. The structure of DELETE is very simple:
DELETE FROM table WHERE condition;
Only the table name must be specified along with a selection condition. All data records for which the selection condition applies are irretrievably removed from the dataset. Without a condition, the entire contents of the table are deleted, but the structure of the table is retained.
Changing Tables (ALTER TABLE)
When creating tables in databases and also during the maintenance and further development of applications, it is usually necessary to adapt the current definition of tables. Adding, deleting, and changing columns in the table are typical change requests, as are adjusting primary and foreign keys or constraints. Changes to a table are possible with the SQL statement ALTER TABLE. The elements of ALTER TABLE are presented and described in table 48.
|Table 48: Elements of ALTER TABLE|
|Elements of ALTER TABLE||Description|
|ALTER TABLE table||Defines the table to be changed|
|ADD column definition||Adds a column; syntax as for CREATE TABLE|
|ALTER column definitionin some DBMSs (like MySQL):MODIFY column definition||Changes properties of a column by specifying all properties; syntax as for CREATE TABLE|
|DROP column||Delete a column by specifying the column name|
|ADD CONSTRAINTconstraintdefinition||Adds a constraint: Includes primary and foreign keys as well as NOT NULL and UNIQUE conditions, the latter also for multiple columns.|
|DROP CONSTRAINTconstraintname||Delete a constraint: Includes primary and foreign keys as well as NOT NULL and UNIQUE conditions, the latter also for multiple columns.|
A concrete example of adding and deleting constraints using a foreign key in DBMS MySQL 5.6 is shown in figure 36. However, the concrete syntax of ALTER TABLE varies depending on the DBMS used.
An example of defining of a primary key with ALTER TABLE follows:
ALTER TABLE Address ADD CONSTRAINT pk_1 PRIMARY KEY (AddressID);
Here, a constraint with the ID pk_1 in the Address table determines the column AddressID for the primary key.
Primary keys in the DBMS MariaDB are removed with the keyword DROP PRIMARY KEY. Here is an example for the table Address:
ALTER TABLE Address DROP PRIMARY KEY;
Foreign keys in the DBMS MariaDB are removed with the keyword DROP FOREIGN KEY followed by the constraint ID. Here is an example for the table Address:
ALTER TABLE Address DROP FOREIGN KEY fk_1;
Deleting Tables (DROP)
Complete deletion of a table is very easy with SQL, but has very drastic effects: the contents and structure of the table will be irrevocably removed from the database. The keyword for this is DROP TABLE, where only the name of the table has to be specified. Here is an example of the delete command for the Address table:
DROP TABLE address;
Preservation of Referential Integrity
When formulating SQL statements to modify and delete existing data, the developer must ensure that all technical and business integrity conditions are met. If foreign key relationships have been defined in a database, these must be given special consideration when changing the dataset in order to maintain referential integrity. One possibility is to specify the automatic behavior of the DBMS when updating foreign keys with ON DELETE and ON CASCADE. When formulating the SQL statements, the developer must take into account how the DBMS is to behave when it is changed or deleted. In addition, there are often functional dependencies in the data model that can be automatically ensured by the DBMS. The development team must be aware of these so that they can avoid possible consistency violations when creating SQL commands to manipulate the dataset. As a result, there are often a lot of SQL statements that need to be executed “in one piece,” as partial execution would violate consistency requirements. This is supported in relational database systems with the transaction concept described in the following section.