Changing Data

Until now we've been reading data from our database

This section introduces us to the concept of writing to our database and changing data

The first step is creating a table in our database

This script is very verbose so we'll go through an example on the next slide

Tell the server to create a new table to store data

CREATE TABLE <schema_name>.<table_name> (
    <field_name1>
        <data type>
        <NULL | NOT NULL>
        <DEFAULT <0>>
        <GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY>,
    <field_name2>
        <data type>,
    CONSTRAINT <primary_key_name> PRIMARY KEY (<field_name1>)
);

Viewing existing table creation scripts

• Right-click on a table
• Go to 'Scripts' > 'CREATE Script'
• The script that created the table is printed in another Query Tool window

Terms:

• Data Type = The type of data the database server will accept in this column going forward
• NOT NULL = A constraint to not allow any unknown (NULL) values in the column
• DEFAULT = Set a default value if none is specified when data is inserted
• CONSTRAINT <primary_key_name> = Specify the column(s) that ensure the data is uniqified in the table
  • Alternatively we can use GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY on the same line
  • This will add an automatically incrementing number as the key