Where is the Enforce Referential Integrity Option?

[jdanniel]

Hi everyone. I'm pretty new to Base, and have a very simple question:

I'll be darned if I can find the word INTEGRITY anywhere in Base's documentation. I'm having integrity problems with a simple relational database, and could not find any way of ensuring referential integrity.

Perhaps Base uses different nomenclature? Anyway...how do I enforce referential integrity? Where is such an option located in Base?

Thank you!

Jd

[DrewJensen]

Hello jd

On the Base windows you will find the menu item: Tools > Relationships

This open the relationship window.

Here you can add tables and then create Foreign Key relations by either dragging a column from one table to another or via Insert > New relation

One thing to be cautious of - if you have data that will not link then the act of creating the FK will fail. But it would tell you where you problem lies. You need to clean up the data before the create FK constraints would execute.

FYI - you can always do this using standard SQL commands also, via the SQL Window.

HTH

Drew

[jdanniel]

Hi Drew.

Thanks for replying, but I'm not sure if it answered the question.

I didn't see anything listed there that pertains to integrity, and I found nothing in the documentation for it, either. The index doesn't even have a listing for INTEGRITY.

Maybe Open Office Base uses a different name than "Integrity?" I don't believe it would, but who knows?

I'd like to know where, exactly, any tools for integrity are located.

Jd

PS: I know absolutely nothing about SQL.

[DrewJensen]

Ok jd,

well - Base ( when using a Relational database - as in the default file type ) does support 'referential integrity' and that is done in this and any other type of relational database with the Foreign Key constraints.

How about we do it the other way around - can you tell me what you think Integrity means. ( in other words what are you looking for the database to enforce for you? ) I can then tell you how to achieve that.

Thanks

Drew

[jdanniel]

HI Drew.

Well, first I would like you to keep in mind that I'm basically a novice, and this is my very first relational database. I designed it only after a lot of communication with other users.

I made a very simple movie database. Nothing sophisticated.

Table 1 has the following:

MovieID
Title
Director
Location
Running Time

Table 2 has the following:

MovieID
GenreID
Genre Name

Genres include Drama, Comedy, Foreign, Science Fiction, etc. Since a movie can have more than one genre, I decided to make the database relational, joined by MovieID.

Okay....so, let's say I have a record for The Godfather, and the genres include Drama, Action, Gangster, and Classic.

When I tried to delete this record, I got an error about integrity.

As I understand it now, after doing further research, enforcing integrity prevents a primary key from being deleted. Without it, it seems I can delete a movie, which would remove the primary key. However, there'd be a reference to it in Table 2. It is "orphaned," as the book I'm reading calls it.

I don't fully understand it, and perhaps I'm wrong, but I'm assuming enforcing integrity prevents a record from being deleted. I guess I really don't understand it, because I don't understand how Base would prevent an entire record from being deleted.

Am I understanding this correctly?

What I want is to be able to delete records, but apparently I have to design the database in such a way that I can delete records without orphaning anything. So, I'm assuming integrity plays a part in this.

I hope this helps you see what I'm doing a bit more clearly. Thanks for the reply. Jd

[DrewJensen]

Ok I'm with you now. I was not sure if a reference to a basic SQL primer was in order, but it sounds as if you have one already so - let's just talk about specifics with Base.

When you talk about the database not allowing you to delete an entry in Table1 because of Referential Integrity, and ask if that is how it should work the answer is ( remember this is computer software ) Yes, No and Maybe.. It depends on how you set up the relationship in other words.

Before getting into that however - how about a step back to look at your tables. You have 2 currently, but from the looks of them and what you have said it would probably be appropriate to have 3.

Movie ( MovieID, Title, Director, Location, Running Time )

• Primary Key = MovieID

Genre ( GenreID, Genre Name )

• Primary Key = GenreID

MoveGenre ( MoveID, GenreID )

• Primary Key = MovieID and GenreID
  Foreign Key = MovieID -> Movie.MovieID
  Foreign Key = GenreID -> Genre.GenreID

In this "schema" the table Movie has one record for every movie, the table Genre has one record for every Genre and the table MovieGenre is used to relate movies to genres and can have 0 or more entires for every movie and every genre. It is this middle table ( MovieGenre ) where the referential integrity is maintained.

Here then is how that appears in the Base Relationship window:

Now each relationship ( Foreign Key ) has the options and these can be seen in the relation dialog.( you can get this by double clicking on either of the lines that show the relation.

Here is the dialog when I double click on the line from Movie to MovieGenre:

So now let's look at what each of those options does at runtime as data is added, updated and deleted from the tables.

For this I am going to add some data.
In the Genre tables I will add three records:
Horror
Musical
Comedy

In the movie table I will add three records:
Saw
Chicago
Rocky Horror Picture Show

( sorry small delay - duties here call - but I'll come back today and finish up )

[jdanniel]

Hi.

I'm going to need some time to study your response, because right now I do not yet understand why three tables are needed.

But you did answer my original question...I think....when you said you can double-click on one of the lines. When I do that, I see the menu that includes Update and Delete options.

It's still beyond me why Base doesn't use the term "integrity," because from what I've read and seen, it's a term that's common in database applications.

But my main concern now is understanding why three tables are needed.

I'll return and follow-up soon. Maybe the best way for me to understand three tables is by creating them and then messing with the database.

Thanks for the response and the in-depth explanation, complete with images. Jd

[jdanniel]

I see you have a table with two primary keys. I thought that wasn't possible...only one primary key per table. Am I correct or incorrect? If I'm incorrect, how do you do that, because I tried doing it and couldn't. I'm assuming I wasn't creating the table correctly.

Maybe I should just wait until your follow-up reply.

Jd

[DrewJensen]

Well - I am still pressed for time at the moment - but have a couple of minutes for the last question:

A table may have only one primary key - that primary key can be made up of one or more columns in the table. That is true for any Relational database manager, not just Base.

In the GUI table designer you can select more then one row - in the same way you do that with any grid in a Windows OS - and the context menu that you use to set the primary key is still active, it just sets the key as the compilation of the values for all columns you selected.

Back to the grind - I'll still be back with the rest of the earlier post.. ( what a way to spend St. Patty's day )

[DrewJensen]

Alright - couldn't get back to that last night - but have a little time this afternoon so:

The Foreign Key options:

1. No Action
   • This is the default setting under Base.
     This tells the database engine to make no changes automatically when the referened value changes or the referenecd record is deleted.
2. Update Cascade ( Delete Cascade )
   • This setting tells the database engine to change the value in the referring column to the value in the referenced column whenever the calue in the referenced column changes.
     For the case of 'Delete' it means to delete the record in the referring table if the record in the referenced table is deleted.
3. Set Null
   • If the value in the referenced column is modified set the value in the referring column to null.
     If the record in the referenced table is deleted set the value in the referring column to null.
4. Set default
   • If the value in the referenced column is modified, or the record in the referenced table is delted, set the value in the referring column to the default value for this column, if there is a default value defined
     If no default value is defined for this column then act as if this where set to 'No Action'.

Looking at the Dialog box above again for the Foreign Key relation between Movie and MoveGentre:
The table MovieGenre is the Referring table.
The column MovieGenre.MovieID is the Referring column.
The table Movie is the Referrenced table.
The column Movie.MovieID is the Referrenced column.

Alright - well I added the data to the two tables and then I created a simple form, to assign Genres to Movies.

Here is the databse with the tables, the default relationships and the form:

movies.odb

(14.56 KiB) Downloaded 1611 times

Open the databse, go to the tables section, open the movie table and try to delete a record.

Delete_RI_error_dialog.png (8.35 KiB) Viewed 33879 times

That message can be a little confusing.

• First - you tried to delete a record from the table Movie, and yet it is talking about an error in the table MovieGenre, what's up with that?
  Second - what the hell is SYS_FK_632 anyway?

Back in a few minutes with the reasons for that, but while I'm gone - Remember both FK relationships are set with 'No Action' as the option.

[jdanniel]

Drew,

I need to study everything you've written, because quite frankly, all of it is over my head.

But right now, I do have a question or two.

In the MOVIE table, the primary key is named MoviewID.

In the MovieGenre table, the primary key is MovieID.

Is there a reason why you named it MoviewID, or is it just a typo? If it's just a typo, will that affect the database?

Also: I see the letter N in the relationship windows. Is that what it is supposed to be, because I thought the universal symbol for "many" in a one-to-many relationship is the infinity symbol. Or does that just depend on the database program?

Thanks again. Jd

[Villeroy]

You can name your tables and fields within tables as you like as long they are unique within the respective collection of tables and fields (no equally named fields within the same table and no equally named tables within the same database).
Yes, a 1-N relation is a relation where each unique item of one table has zero, one or more occurrences in the other table.

[jdanniel]

You can name your tables and fields within tables as you like until they are unique within the respective collection of tables and fields (no equally named fields within the same table and no equally named tables within the same database).

Hi. I understand this to a point. I understand that two fields in a table cannot have the same exact name. And I understand that two tables in a database cannot have the same exact name.

But I thought linked fields did have to be the same. Are you saying they don't?

Jd

[Villeroy]

The linked fields don't have to share the same name. My preferred naming convention goes like this:
Table "Movies" with field "ID"(primary key)
Table "MovieGenres" with fields "ID" (primary key) and a "MovieID" (foreign key), indicating the name of the referenced table.
Then create a 1-N relation between Movies.ID and MovieGenres.MovieID

[jdanniel]

Okay...so the fields do not have to have the same name. I was misinformed. Thanks.

Jd

[DrewJensen]

HI,

A few minutes to spare again - Villeroy is quite right the names in the relationship are of no importance. His naming convention is also one that I would normally use - but since you had named your key field MovieID I followed suit for the example.

( ps - MoviewID is just a TYPO and I will update the database with a corrected table in the next few minutes )

As for 'n' versus 'the infinity symbol' that has nothing to do with the database engine and everything to do with the documentation writer. ( there are certain 'standard notations' for describing database schema' but that is beyond this discussion and more about tool selection anyway )

[Villeroy]

However, the data types of two fields have to be the same before you can establish a relation between the two. For instance, you can not link a field of decimals with another field of integer numbers.

[DrewJensen]

FYI - I updtated the movies.odb file with spelling corrections.

MoviewID became MovieID and Ttile became Title.

[jdanniel]

Hi Drew.

I guess the actual field names are of little importance.

As for the Foreign Key options that you explained: I'm still in the process of figuring out the purpose of the third table, so the Foreign Key options are on the back burner in my mind for the moment. Please give me time to try to get the concept to coalesce in my head.

Here's what confuses me right now. Let me explain what I'm thinking, and maybe we can figure out how I can understand it better:

I created a two-table relational database, and when I tried to delete a record, I got the same error dialog box that you got. Well, if not the same, then it was similar.

Your demo has three tables, and there was an error dialog box. You can probably guess what I'm thinking: If a three-table database and a two-table database both have a near-identical error warning when a record is deleted, then why do I need the three-table database?

If I'm going around in circles, I'm sorry. I just need to grasp the concept.

Jd

[DrewJensen]

Yes - well that is why I said the error message box is a bit confusing.

The thing is - There is no error. What it is telling you is that IF it let you delete that record, then there would be an error. In other words the message box is telling your that referential integrity is being enforced and it stopped you from breaking the integrity.

Put is another way.

You told the system that in Table2 there is a field that has a value that must equal the value in a particular field in Table1 for at least one record. Right?

Then you tried to delete a record in Table1. It happens that there is a record in Table2 that has a value in that Foreign key field that points to the record in Table1 that you are trying to delete - so - the database says - Hey buddy sorry, you can't do that...because if you did then the rule, "that you set up", would no longer be true.

Now - if you really want to delete that record in Table1 - then you must go to Table2 first and delete any records that refer to the record in Table1. Then go back to Table1 and delete the record.

Of course if you instead selected the option, under the foreign key dialog box 'Delete Cascade' - then what would happen is that when you deleted the record in Table1 it would automatically delete ALL records in Table2 that refer to that record ( quietly by the way ) and in so doing would still maintain the 'referential integrity' of the database overall.

Does that make it any clearer...( I am sure it could be said better then I just said it )

[jdanniel]

Drew...

Does that make it any clearer

It raised more questions than it answered, I'm afraid, but you might have expected that.

The two questions it raised are this:

1. Where does the third table enter into all of this? I'm still stuck here and deep in the mud.

2. You mentioned Delete Cascade, and explained what it does. Unless I misinterpreted what you wrote, it sounded like this is precisely what I want. If I delete a record in Table 1, then all records in Table 2 that refer to that deleted record are gone.

I interpret this as a good thing. Am I wrong?

Because if I'm right, and if I interpreted this properly, then I'm failing to see the need for a third table. I must be misinterpreting this, though, because:
a) this is not the default setting;
b) your use of the word ALL in "automatically delete ALL records" may mean something other than what I'm thinking.

I really want to focus on the third table. What am I missing? What am I misunderstanding that I need to understand?

Jd

[kabing]

jdanniel:

To chime in here, the third table (Genre) forms the basis for the listbox used in the data entry form. This keeps the data entry consistent. It's the same idea I was pursuing in another thread. There are probably other reasons for it, too. I didn't impliment the tables in quite the same way, because I'm not as conversant with the right way to do things when it comes to databases. Always take Drew's word over mine when it comes to databases.

kabing

[jdanniel]

Hi, and thanks for coming over to this thread.

I've been doing a considerable amount of reading, and I'm going to throw both you and Drew a curve ball here...

In the other thread, you tossed a couple of SQL commands at me, and I responded by saying I knew nothing about SQL, and therefore didn't feel comfortable working with it.

I'm beginning to wonder, however, if I should.

If SQL can help maintain consistent data entry, AND if it can help delete a record "cleanly" (for lack of a better term), than maybe I should dabble with it? It's not something I'll learn overnight, but I do know there is a DELETE command.

Maybe I'm being a little too ambitious. I need to discipline myself...if both of you recommend a third table, then I have to focus on that. My goal right now is to understand its importance in this database. I'm wondering if I'm overemphasizing this.

Jd

[DrewJensen]

Ah no don't always take anyone's word - but thanks.

Kabring is correct and I kind of mixed the message by introducing the third table. It really isn't about Referential Integrity directly but regards a concept called Normalization - or Normalizing your data.

Looking at your original two tables again:

Table 1 has the following:

MovieID
Title
Director
Location
Running Time

Table 2 has the following:

MovieID
GenreID
Genre Name

You have a column "Genre Name" that is a repeating value, not part of a key. This is considered a bad thing, the reason- as Kabring touched on, is consistency.

What would stop you from entering three records of:

1,1, Horror
1,2, HORRor
1,3, Horrer

Nothing would stop you from doing that.

Now by going to three tables, where the the table Genre is a forieng key in the MovieGenre table you are guaranteed that every time you want the genre to be Horror it will be just that. Even if you made a typo in the Genre table and spelled it 'Horrer' and proceeded to use it 100 times in the MovieGenre table you could fix it by editing the single record in the Genre table.

[jdanniel]

It really isn't about Referential Integrity directly but regards a concept called Normalization - or Normalizing your data.

I think we've made a breakthrough, to a point.

If you recall earlier in this thread, I asked why I couldn't find the word "integrity" in the documentation, and wondered about nomenclature.

This is, to an extent, a nomenclature issue--I don't know the correct nomenclature.

Yes, I am definitely concerned about consistency, and it's a big issue for me. At first, I didn't understand it, but I think it's much clearer.

Yes...I want to ensure the genre names are consistent. As Kabring mentioned in another thread I created, what if I type Science Fiction, but someone else types SciFi or Sci-Fi?

That's not good. I want to ensure consistency.

But I still....still...am failing to understand the third table. Fortunately, I'm now a little closer to understanding. I'm getting closer, so perhaps a few more messages back and forth might break the concept through.

With two tables, there can be 100 different spellings or variations of a genre name. To normalize it, I'd have to look through the entire table, and manually make corrections.

Or, do a query with wild cards, and look for every listing beginning with H, if I want to normalize HORROR.

Is this correct so far?

Okay...so we need to find a way to streamline that.

What you are saying, then, is that with a third table, I can do that.

Now by going to three tables, where the the table Genre is a foreign key in the MovieGenre table you are guaranteed that every time you want the genre to be Horror it will be just that. Even if you made a typo in the Genre table and spelled it 'Horrer' and proceeded to use it 100 times in the MovieGenre table you could fix it by editing the single record in the Genre table.

I have a feeling that once I understand this quote, that's the breakthrough I need to get working with the third table. I'll try to focus on this. I'm not there yet.

First, a question:

You said the table Genre is a foreign key? Can an entire table be a foreign key?

Jd

[DrewJensen]

Sorry - fast and loose with the terms there.

NO a table can not be a Foreign Key - it participates in one though.

What I should have said.

By using a foreign key to refer to the Genre table for the value 'Horror' you ensure consistency in your data.

In this example the foreign key field is in the table MovieGenre and is the GenreID field. The Foreign Key relationship is from MovieGenre.GenreID to Genre.GenreID.

Also - back to normalizing the data. Now the table MovieGenre has no repeating values, that are not part of a key. Since the field MovieGenre.MovieID and MovieGenre.GenreID have no meaning as individual fields - because they together form the 'compound' Primary Key for the table.

OK - off to eat supper. I;ll check back afterwards.

Ciao or Chow, as the case may be.

[jdanniel]

I still don't understand the third table. But let's deal with that in a moment.

I went ahead and wrote my own database, based on your three tables. I wrote them exactly like you did, and assigned relationships exactly as you did.

Here's the problem: When I try to design a form based on the tables, I can't figure out how to add the GENRE field to the subform. The only table that I'm offered when I try to do that is MovieGenre.

Why does the Form Wizard not offer the other two tables when I try to create a subform?

[kabing]

You don't want/need the Genre table on the form. While Drew has named the column in the subform "Genre," it is actually based on the MovieGenre table. (right-click on it with the form open for editing and choose "control" and then click on the Data tab to see the table it relates to.)

The Wizard only lets you choose the MovieGenre table for a subform because:
-the Genre table does not relate directly to the Movie table
-the Movie table is already in use as the main form.

The Genre table is a reference table; It is used by the column labeled "Genre" of the subform. (remember, the column is linked to the GenreID field in the MovieGenre table). Right-click on the the "Genre" heading in the form (while in edit mode) and choose Column.... Click on the Data tab. You will see an SQL statement in the List Content field of the data tab. That SQL statement pulls data from the Genre table to populate the drop-down. The user sees a list of Movie Genres, but the value that is stored in the MovieGenre table is the corresponding GenreID.

kabing

[jdanniel]

The user sees a list of Movie Genres, but the value that is stored in the MovieGenre table is the corresponding GenreID.

I'm still confused.

I have absolutely no idea now how to get a Genre subform on the form.

I tried using MovieGenre/GenreID as the field in the subform. However, when I tried to enter genre names in the subform, I got a warning box.

So let's keep this simple...in order to get a GENRE subform on the form, what do I do?

[kabing]

You can't get a Genre subform on the form, at least not through the Wizard, and even if you could get a Genre subform on the form, it doesn't get you where you want to go.

Let's back up for a minute. Take a look again at the the tables and fields as Drew created them. I've reorganized how the list appears, but it's the same structure Drew specified above:

• Movie table: one record per movie
  • MovieID (integer, autoincrement, primary key)
    Title (text)
    Director (text)
    Location (text)
    Running Time (Decimal)
  Genre table: list of genres to pick from; one record per genre
  • GenreID (integer, autoincrement, primary key)
    Genre Name (text)
  MovieGenre table: each record links a particular movie to a particular genre; you may have more than one record per movie, and more than one record per genre, but only one record for any given movie/genre combination. (i.e. Star Wars: Return of the Jedia can only have one record identifying it as Science Fiction, but it can have another record identifying it as Adventure).
  • MovieID (integer, part of combined primary key; relates--as foreign key--to the MovieID field in the Movie table)
    GenreID (integer, part of combined primary key; relates--as foreign key--to the GenreID field in the Genre table)

Notice that both fields in the Movie Genre table are integer type. That is, no where in the MovieGenre table is there a field that actually allows you to enter "Science Fiction" as the genre. Nor can you enter "Star Wars: Return of the Jedi" in the MovieGenre table. Instead, the MovieGenre table stores the GenreID and the Movie ID. If record #1 of your Genre table is Science Fiction, and the Movie table has Star Wars: Return of the Jedi as record #15, then to assign Jedi the Science Fiction genre, and entry is made in the Movie Genre table as follows:

Movie ID=15 Genre ID=1

This is all part of the normalization process, I don't entirely understand why it's better to do it this way than to use text fields, but it is standard procedure. I think it has to do with strictly controlling consistent data entry and reducing file sizes, but I"m not sure.

Now, ordinary humans can't be expected to remember the ID numbers of all the Movies and Genres in a database. The link with the Movie ID number is taken care of with the defined relationships and in the way the subform is created; Base automatically enters the Movie ID into the corresponding MovieGenre record because this link has been specified when creating the form.

But now we need a way for the user to choose from a list of genres (the text entries from the Genre table), but for Base to store the corresponding GenreID number in the MovieGenre table. That's where the list box comes in; a list box displays the list of genres (from the Genre table). But when the user chooses a genre, it is the corresponding GenreID number that is stored in the MovieGenre table. So in the example above, I don't have to remember that Science Fiction is genre #1; I just pick it from the listbox in the form, and Base knows to save 1 as the Genre ID for that record.

Does that make any more sense?

kabing

Edit: added formatting to make the table/field list easier to read; fixed a few typos.