Mariano Casanova wrote:
Hi Drew:
Could you please comment on this text? Particularly if something is wrong or
incomplete.
Thanks!
Mariano
On table attributes of not null, unique and key
Base allows you to specify certain attributes for the columns in your tables.
In this tutorial we will consider the following options:
Key: This option tells Base that this column will hold the primary key of the
table. Base will prepare then to see it associated with other tables.
Tells the database that this column is part of the key - it may be the
entire key, or it may one of a set of columns that make up the key. In
the case of Base these compound key columns must also be adjacent.
Unique: When you specify this option, Base will make sure that records in this
column are not repeated. If, for example, you specify that the 'surname' column
be unique, then the second time you try to enter 'Dumas' Base will reject it as
an invalid entry. It makes a lot of sense to make sure that a column set to
KEY is also set to UNIQUE.
Setting a column as part of the Primary Key automatically sets it to
unique, no need to set this separately.
Not null: This option means that records can not be left with this attribute
empty. Base will display an error message if you try to enter a record that
leaves a NO NULL column empty. This forces whomever is using your database to
at least have the information requested in the NOT NULL columns if they want to
entrer the record. For example, if you set the 'surname' and 'date of birth' as
NOT NULL, then a user can not input a new author if he doesn't have at least
the surname and the date of birth. Again, it makes sense that Key columns are
also set to NOT NULL.
This can be a little bit of a problem. For example you can tell the
database engine that a column is NOT NULL and set a DEFAULT value. For
instance a date field can be set as DEFAULT CURRENT_DATE, so that if a
new record is entered and no value is given for this column it is
automatically set to the current date. In all versions of Base up to and
including the coming 2.3.1 release this caused a problem, because the
GUI controls refused to allow you to send a NULL value to a column
marked as NOT NULL, even if there was a default value setup for it. In
version 2.4 this will finally be addressed in that a per database option
will then be available to turn off this check, allowing NULLs to be sent
to a column designated as NOT NULL.
The other problem with this is that under Base there is no mechanism
using the GUI table editor to set these default values for a column. In
the GUI table editor there is a property 'Default Value', but this is a
string only constant that will be used by UI controls in the Base front
end, not the database engine proper. This problem is not addressed in
the 2.4 release, so it is still necessary to enter default values at the
database level via the SQL window.
On the different deletion options and what they mean.
Because being able to relate the object in one class to another object in
another class is so important, and the strength of relational databases,
special care must be placed on the subject or deleting records. Think about
this: Let's say that there is an an author that wrote only one book and you
discover that you no longer have that book in your collection. As you update
your database and erase that title, what will happen to the 'author'
information? Should it be deleted too? Should it be kept as an historical
record or in case you find and buy that book again?
Actually, both options are valid and you can chose the one that reflects the
purpose of your database best. But your application will not know what to do
unless you make explicit what your preference is. For this reason, when you are
developing your application and defining relationships, Base will ask you how
to handle the deletion of records and will offer you the following options.
This is what they mean:
No action: Base will not delete the record but...
Delete Cascade: With this option, Base will delete the record you are requesting to delete and will also delete all other records that have the deleted record's key as a foreign key. This option is called cascade because it elicits the image of a deletion creating further deletions.
Set Null: With this option, Base will delete the record you are requesting but
will not delete the others related to it. Instead it will erase their foreign
keys to reflect that they are no longer associated. Note that this requires
that NUT NULL is not a condition of the foreign key column.
Set Default: When deleting an object, the foreign key column of the associated
tables will be populated with a default parameter that you previously specified.
OK, just to be very clear on this. Let's assume two tables:
Authors( AuthorID, Name, DOB....)
Library( AuthorID, BookID, Title, Published Date,.....)
Library.AuthorID is set as a Foreign Key and references Authors.AuthorID
We have an entry in the Authors table with a PK ( AuthorID ) value of 0
We have two records in the Library table with a FK ( AuthorID ) value of 0
Now if when the FK was created the ON DELETE ACTION was set to DELETE:
Delete either of the books in the Library table with the FK = 0 and the
other entry is unaffected.
Delete the record in the Authors table with the PK = 0 and BOTH records
in the Library table are also deleted - no question, no prompting, no
chance to undue the deletion. So you make this choice very rarely.
That was the easy case actually, now for the others
--------------------------------------------------------------------------------------------
Same setup as before Authors.AuthorsID = 0 and two records in Library
have AuthorID = 0
Relation is set to NO ACTION
Delete the record in the Authors table.- ERROR is generated. Base will
not allow you to delete the record in the Authors table so long as there
are records in the Library table that relate to this entry, as this wold
then break the referential integrity rules. So before you may do so you
must either
A) Delete all the associated book entries in the Library table before hand
or
B) Update the values int eh AuthorID field to other valid entries from
the Authors table.
------------------------------------------------------------------------------------
Next scenario - Set NULL
What happens here depends on the way you created the FK field in the
Library Table.
If the field Library.AuthorID is NOT NULL then an ERROR is generated and
you may not delete the referenced value in the Authors table until the
records in the Library table have been updated to a different valid entry.
If the field Library.AuthorID is NULL allosed, then the deletion of the
record in Authors.AuthorID = 0 is allowed and the values in
Library.AuthorID = 0 are now Library.AuthorID = NULL, again no prompt,
no notice and no way to undue this action.
---------------------------------------------------------------------------------------
ON DELETE = Set to default
Again what happens when you try to delete the record in Authors depends
on how you have setup the tables.
If you HAVE NOT set a default value for Library.AuthorID then deleting
the record in the Authors table acts just like having the relation set
to "Set NULL", including the ERROR state if the Library.AuthorID is also
set to NOT NULL.
Now let's say we did create a default value for Library.AuthorID with
this statement in the SQL window
ALTER TABLE "Library" ALTER COLUMN "AuthorID" DEFAULT -1
OK, we delete the record in Authors with AuthorID = 0
If there is not a record in the Authors table with a AuthorID = -1 then
ERROR - constraint violation again
If there is a record in the Authors table with a AuthorID = -1 then
change all records in Library.AuthorID = 0 to Library.AuthorID = -1
( no message, nor prompt, no undue )
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
