Rows

Rows in a database are similar to rows in a spreadsheet. You could compare rows in a database to any sort of table row or an entry in an array. Database rows are the most restrictive as a cell in a row is locked to the column type.

In a spreadsheet column, you can mark the column with a default display format but not limit the content entered. A decimal column can have text. In the most commonly used databases, the data entered into a column is limited to the format of a column. There is one database where the data is always stored as a string and can accept data that does not fit the column definition.

Databases also offer more options for limiting the content of a column. In a spreadsheet, a cell can always be empty. In a database, you can usually require entry of a value in each column before the row can be inserted. You can usually specify null as an alternative in columns that are not part of a unique index. The effect on a row insert varies.

You can specify default values for columns and the default value can be a null. Without a default value, the column value has to be specified for every column in a row for an insert. Supplying a default may be better or worse. Look at some examples.

Look at a row for a product in a shoe shop. Shoes have sizes. When you set a default size, a row insert can be made for a shoe that does not have a size defined. A supplier sends you a list of their shoes and some are missing the size. All the rows from the list will insert into your database. You now have problem or an advantage.

Assume you enter a default value of 9 for the show size column. The supplier has a size 10 shoe that does not have the size specified in the data sent to you. The shoe ends up listed on your size as a size 9. This is a mistake.

Now assume you have the shoe size default set to null. All the supplier data is entered, which means you can sell all the shoes with valid sizes. Your Web site would ignore the shoes with a size of null. You can also list the shoes with a size of null and send the list to the supplier. The supplier can then fix the data.

What might happen with a "description" field? The supplier might default to a zero length string for the description field or might leave the field out. You could use a null default to highlight missing descriptions or set the default to a zero length string.

Insert a row

When you insert a row into a table, you have to supply a value for every column that does not have a default value. Every value has to fit the format defined for that column. The values have to be unique for columns used in indexes that are defined as unique. If an index is unique and contains multiple columns, it is the combination of the column values that has to be unique.

Update a row

When you update a row, you do not have to supply every column, only the columns you want to change and the columns in the primary key. The supplied columns follow the same rules as an insert.

An update can be relative for things like numbers. You can specify something like "add 1 to the current quantity" using the syntax of the database. You can also make the change absolute, something like "if the column value is 3, set the value to 4".

In the following example, the table is names shoes and a shoe is identified by the primary key column named shoe. We update a shoe identified in the primary key as sneaker. We change the quantiti to 4 only if the current quantity is 3.

update shoes set quantity = 4 where shoe = 'sneaker' and quantity = 3

Now think about a table where the primary key is brand and model. We have the following table.

brandmodelquantity
Footcoversslipper5
Footcoverssneaker3
Nikkysneaker4
Nikkysandal7

Note there are two brands where the model is sneaker. The following line of SQL would update two rows.

update shoes set quantity = 4 where shoe = 'sneaker'

Either of the following lines of SQL would update only one row.

update shoes set quantity = 4 where shoe = 'sneaker' and brand = 'Nikky'
update shoes set quantity = 4 where shoe = 'sneaker' and quantity = 3