The Nullity of a Field
|
When performing data entry, you can
expect the user to skip any column whose value is not available and move
to the next. In some cases, you may require that the value of a column be
specified before the user can move on. If you are creating a column and if you want to let the
user add or not add a value for the column, type the NULL keyword
on the right side of the data type. If you want to require a value for the
column, type NOT NULL. Here are examples:
Private Sub btnVideoCollection_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnVideoCollection.Click
Dim catADOX As New ADOX.Catalog
catADOX.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\VideoCollection.mdb';")
MsgBox("A new Microsoft JET database named VideoCollection.mdb has been created")
End Sub
Private Sub btnVideos_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnVideos.Click
Dim conVideos As New ADODB.Connection
conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\VideoCollection.mdb'")
conVideos.Execute("CREATE TABLE Videos(" & _
"VideoTitle STRING NOT NULL, " & _
"Director STRING NULL, " & _
"YearReleased SHORT, " & _
"Rating BYTE NULL);")
conVideos.Close()
End Sub
In this case, when performing data entry, the user
must always provide a value for the VideoTitle column in order to create a
record. If you omit to specify the nullity of a field, it is assumed NULL;
that's the case for the YearReleased column of the above Videos table.
When we study relationships, we will see that, on a
table, each record should be uniquely identified. This should be the case
even if many records seem to have the same values for each column. We saw
already that you can require that the user provide a value for each record
of a certain column. In some cases, the user may not have the right value
for a column but at the time, the record would need to be created, even if
it is temporary. To solve this type of problem and many others, you can
create a column that provides its own value. On the other hand, to create
a special column that can be used to uniquely identify each record, you
can apply an integer data type to it but ask the database engine to automatically
provide a numeric value for the column.
If you are creating the column, you can specify its data type
as either COUNTER or AUTOINCREMENT. Only one column of a table
can have one of these data types. Here is an example:
Private Sub btnVideos_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnVideos.Click
Dim conVideos As New ADODB.Connection
conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\VideoCollection.mdb'")
conVideos.Execute("CREATE TABLE Videos(" & _
"ShelfNumber COUNTER, " & _
"VideoTitle STRING NOT NULL, " & _
"Director STRING NULL, " & _
"YearReleased SHORT, " & _
"Rating BYTE NULL);")
conVideos.Close()
End Sub
By default, when you apply the COUNTER or the
AUTOINCREMENT data type, when the user creates the first record, the field
int the auto-incrementing column receives a number of 1. If the user
creates a second record, the auto-incrementing value receives a number of
2, and so on. If you want, you can make the first record receive a number
other than 1. You can also make it increment to a value other than 1. To
apply this feature, the COUNTER and the AUTOINCREMENT types use a seed as
their parentheses:
COUNTER(x,y)
or
AUTOINCREMENT(x,y)
The x value represents the starting value of the records. The
y value
specifies how much would be added to a value to get the next.
|
Fields Sizes
|
When reviewing the data types available for columns, we saw that
some of them could use a string-based data type, namely TEXT, CHAR,
or VARCHAR. By default, if you create a table and you set a column's
data type to TEXT, CHAR, or VARCHAR, it is made to hold 255 characters.
If you want, you can control the maximum number of characters that would
be allowed in a column during data entry.
To specify
the number of characters of the string-based column, add the parentheses
to the TEXT, the CHAR, or the VARCHAR data types, and in the parentheses,
enter the desired number. Here are examples:
Private Sub btnVideos_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnVideos.Click
Dim conVideos As New ADODB.Connection
conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\VideoCollection.mdb'")
conVideos.Execute("CREATE TABLE Videos(" & _
"ShelfNumber COUNTER, " & _
"VideoTitle STRING(120) NOT NULL, " & _
"Director VARCHAR(80) NULL, " & _
"YearReleased SHORT, " & _
"Rating TEXT(20) NULL);")
conVideos.Close()
End Sub
|
Default Values
|
A default value allows a column to use a value that is
supposed to be common to most cells of a particular column. The default
value can be set as a constant value or it can use a function that would
adapt to the time the value is needed.
To specify a default value, after the name and the
data type of a column, type DEFAULT and assign it the desired value, based
on the data type. Here is an example:
Private Sub btnVideos_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnVideos.Click
Dim conVideos As New ADODB.Connection
conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\VideoCollection.mdb'")
conVideos.Execute("CREATE TABLE Videos(" & _
"ShelfNumber COUNTER, " & _
"VideoTitle STRING(120) NOT NULL, " & _
"Director VARCHAR(80) NULL, " & _
"YearReleased SHORT, " & _
"Rating TEXT(20) NULL Default='PG-13');")
conVideos.Close()
End Sub
No comments:
Post a Comment