Understanding Primary and Foreign Keys in Database Relations

  • MHB
  • Thread starter mathmari
  • Start date
KNr is a foreign key in the table Bestellung, but it should be a primary key. (Nod)What does the second part of the question means ?The second part of the question is asking about the behavior that makes sense when a value of the primary key, to which a foreign key refers, is changed or deleted. For example, if the KNr of a customer in the table Kunde is changed, what should happen to the corresponding KNr in the table Bestellung? Should it be updated as well or should it remain the same? This relates to the concept of referential integrity in database management.
  • #1
mathmari
Gold Member
MHB
5,049
7
Hey! :giggle:

Consider the followingrelations for the database of a mail-order company.
Kunde(KNr, Nachname, Vorname, Strasse, Hausnr, PLZ, Ort)
Artikel(ArtNr, PreisProStuck, Bezeichnung, Gewicht)
Bestellung(KNr, ArtNr, Anzahl, Bestelldatum)

1. Think about which attributes are useful primary keys in the individual relations.
2. Are there foreign keys? If so, indicate these with the attributes to which the foreign key relate. Which behavior makes sense in the individual cases, if a value of the primary key, to which the foreign key refers is changed (update)? Which behavior makes sense at Deleting such values?
3. Are there reasonable conditions for the individual attributes? Give for each attribute at least one property (at least the data type) and possibly a condition that must always be fulfilled. Hint: Conditions refer to the type, key and reference integrity. I have done the following :

1. Primary keys is the column that is characteristic for a table, like the ID number, where each entry is unique, right?
Therefore, at the table "Kunde" the primary key is the attrubute "KNr", at the table "Artikel" the primary key is the attrubute "ArtNr", right?
At the table "Bestellung" which is the primary key? Maybe one of the first two attributes? Or can it not be that one attribute is the primary key for more than one table? The "Anzahl" which gives us the number of items that have been ordered must not be unique, right? The same holds also for the date of order, right?

:unsure: 2. Foreign keys are the primary keys of one table that are also in an other table,or non? That means that in the table "Bestellung" we have "KNr" and "ArtNr" which are the primary keys of the first two tables.
Is that correct? Or have I misunderstood the meaning of foreign keys? :unsure: 3. I haven't really understood what this question asks for. Could you explain that further to me? :unsure:
 
Physics news on Phys.org
  • #2
mathmari said:
1. Primary keys is the column that is characteristic for a table, like the ID number, where each entry is unique, right?
Therefore, at the table "Kunde" the primary key is the attrubute "KNr", at the table "Artikel" the primary key is the attrubute "ArtNr", right?
At the table "Bestellung" which is the primary key? Maybe one of the first two attributes? Or can it not be that one attribute is the primary key for more than one table? The "Anzahl" which gives us the number of items that have been ordered must not be unique, right? The same holds also for the date of order, right?

Hey mathmari!

Indeed. Neither KNr nor ArtNr is unique. And the combination of all columns won't necessarily be unique either.
After all, it is possible that the same customer orders the same number of articles twice on the same day.

Instead we should introduce a new attribute, say BNr, as the first column. 🤔

mathmari said:
2. Foreign keys are the primary keys of one table that are also in an other table,or non? That means that in the table "Bestellung" we have "KNr" and "ArtNr" which are the primary keys of the first two tables.
Is that correct? Or have I misunderstood the meaning of foreign keys?

Correct. KNr and ArtNr are foreign keys in the table Bestellung. (Nod)

mathmari said:
3. I haven't really understood what this question asks for. Could you explain that further to me?

Basically it asks to give CREATE TABLE statements (schemas) "that makes sense".

Let's start with KNr in the table Kunde.
Which properties should we apply to it (at least the data type)? 🤔

The name KNr already implies that it is a number, so the type should be integer.
We want to use it as a primary key, so the condition should be PRIMARY KEY, which implies that it is both NOT NULL and UNIQUE.

So we should have:
SQL:
CREATE TABLE Kunde (
  KNr integer PRIMARY KEY,
  ...
);
🤔
 
  • #3
Klaas van Aarsen said:
Indeed. Neither KNr nor ArtNr is unique. And the combination of all columns won't necessarily be unique either.
After all, it is possible that the same customer orders the same number of articles twice on the same day.

Instead we should introduce a new attribute, say BNr, as the first column. 🤔

Ah can we just do that, adding a new attribute? :unsure:
Klaas van Aarsen said:
Correct. KNr and ArtNr are foreign keys in the table Bestellung. (Nod)

The new attribute of 1, BNr, is not a foreign key, since we don't use this somewhere else, right? :unsure:

What does the second part of the question means ?
"If so, indicate these with the attributes to which the foreign key relate. Which behavior makes sense in the individual cases, if a value of the primary key, to which the foreign key refers is changed (update)? Which behavior makes sense at Deleting such values?"

I haven't really understood what this means. :unsure:
Klaas van Aarsen said:
Basically it asks to give CREATE TABLE statements (schemas) "that makes sense".

Let's start with KNr in the table Kunde.
Which properties should we apply to it (at least the data type)? 🤔

The name KNr already implies that it is a number, so the type should be integer.
We want to use it as a primary key, so the condition should be PRIMARY KEY, which implies that it is both NOT NULL and UNIQUE.

So we should have:
SQL:
CREATE TABLE Kunde (
  KNr integer PRIMARY KEY,
  ...
);
🤔

So do we have the following ?

CREATE TABLE Kunde (
KNr integer PRIMARY KEY,
Nachname char(225),
Vorname char(225),
Strasse char(225),
Hausnr integer,
PLZ integer,
Ort char(225)
);

CREATE TABLE Artikel (
ArtNr integer PRIMARY KEY,
PreisProStuck float,
Bezeichnung char(225),
Gewicht float
);

CREATE TABLE Bestellung (
KNr integer,
ArtNr integer,
Anzahl integer,
Bestelldatum date,
);:unsure:
 
  • #4
mathmari said:
Ah can we just do that, adding a new attribute?

It's the best that I can think of. :unsure:
mathmari said:
The new attribute of 1, BNr, is not a foreign key, since we don't use this somewhere else, right?

BNr would be a primary key in the table Bestellung.
For the record, KNr is a foreign key in the child table Bestellung, and it is a primary key in the parent table Kunde.
Since BNr is not referred to in other tables, there is no other table in which it is a foreign key. 🤔

mathmari said:
What does the second part of the question means ?
"If so, indicate these with the attributes to which the foreign key relate. Which behavior makes sense in the individual cases, if a value of the primary key, to which the foreign key refers is changed (update)? Which behavior makes sense at Deleting such values?"

I haven't really understood what this means.

Suppose we want to delete a customer.
Then what should happen to their orders that are still in the table Bestellung? 🤔
That table would still refer to the customer through its KNr.
In other words, deletion of the customer would break the so called referential integrity.
What ways can we think of to prevent referential integrity from being broken? 🤔
mathmari said:
So do we have the following ?

CREATE TABLE Kunde (
KNr integer PRIMARY KEY,
Nachname char(225),
Vorname char(225),
Strasse char(225),
Hausnr integer,
PLZ integer,
Ort char(225)
);

CREATE TABLE Artikel (
ArtNr integer PRIMARY KEY,
PreisProStuck float,
Bezeichnung char(225),
Gewicht float
);

CREATE TABLE Bestellung (
KNr integer,
ArtNr integer,
Anzahl integer,
Bestelldatum date,
);

That works. I think we can and should identify a couple more properties though.
Can we mark for instance KNr in Bestellung as a FOREIGN KEY, which will enforce referential integrity? 🤔
 
Last edited:
  • #5
Klaas van Aarsen said:
BNr would be a primary key in the table Bestellung.
For the record, KNr is a foreign key in the child table Bestellung, and it is a primary key in the parent table Kunde.
Since BNr is not referred to in other tables, there is no other table in which it is a foreign key. 🤔

So at the first part of question 2 ("Are there foreign keys? If so, indicate these with the attributes to which the foreign key relate.") the answer is to say that KNr is a foreign key in the child table Bestellung, and it is a primary key in the parent table Kunde and ArtNr is a foreign key in the child table Bestellung, and it is a primary key in the parent table Artikel, right? :unsure:
For the second part of question 2 we have :
Klaas van Aarsen said:
Suppose we want to delete a customer.
Then what should happen to their orders that are still in the table Bestellung? 🤔
That table would still refer to the customer through its KNr.
In other words, deletion of the customer would break the so called referential integrity.
What ways can we think of to prevent referential integrity from being broken? 🤔

We can delete only a costumer that has no order in the table Bestellung, right?
Respectively, if we can update a costumer only if the costumer has no order in the table Bestellung, right?

Is that correct? Or can we delete or update also a costumer if he has an order and in that case we have to update the respective row in the table Bestellung?

:unsure:
Klaas van Aarsen said:
That works. I think we can and should identify a couple more properties though.
Can we mark for instance KNr in Bestellung as a FOREIGN KEY, which will enforce referential integrity? 🤔

CREATE TABLE Kunde (
KNr integer PRIMARY KEY,
Nachname char(225) NOT NULL ,
Vorname char(225) NOT NULL ,
Strasse char(225) NOT NULL ,
Hausnr integer NOT NULL ,
PLZ integer NOT NULL ,
Ort char(225) NOT NULL
);

CREATE TABLE Artikel (
ArtNr integer PRIMARY KEY,
PreisProStuck float NOT NULL ,
Bezeichnung char(225) NOT NULL ,
Gewicht float NOT NULL
);

CREATE TABLE Bestellung (
BNr integer PRIMARY KEY
KNr integer,
ArtNr integer,
Anzahl integer NOT NULL ,
Bestelldatum date NOT NULL ,
FOREIGN KEY (KNr) REFERENCES Kunde (KNr),
FOREIGN KEY (ArtNr) REFERENCES Artikel (ArtNr)
); Do we need that "NOT NULL" ? Is the rest correct so far? What other properties can we use? :unsure:
 
  • #6
mathmari said:
So at the first part of question 2 ("Are there foreign keys? If so, indicate these with the attributes to which the foreign key relate.") the answer is to say that KNr is a foreign key in the child table Bestellung, and it is a primary key in the parent table Kunde and ArtNr is a foreign key in the child table Bestellung, and it is a primary key in the parent table Artikel, right?

Correct. (Nod)

mathmari said:
For the second part of question 2 we have :

We can delete only a costumer that has no order in the table Bestellung, right?
Respectively, if we can update a costumer only if the costumer has no order in the table Bestellung, right?

Is that correct? Or can we delete or update also a costumer if he has an order and in that case we have to update the respective row in the table Bestellung?

I think there are 2 options here:
1. The deletion of the customer causes an error if there are references to the customer.
2. All corresponding rows in Bestellung are automatically deleted as well if we try to delete a customer.
🤔

In the case of a change of KNr we have more or less the same thing:
1. A change of KNr in Kunde causes an error if there are references to the customer.
2. All corresponding rows in Bestellung are automatically updated as well if we try to change the KNr of a customer.
🤔

mathmari said:
CREATE TABLE Kunde (
KNr integer PRIMARY KEY,
Nachname char(225) NOT NULL ,
Vorname char(225) NOT NULL ,
Strasse char(225) NOT NULL ,
Hausnr integer NOT NULL ,
PLZ integer NOT NULL ,
Ort char(225) NOT NULL
);

CREATE TABLE Artikel (
ArtNr integer PRIMARY KEY,
PreisProStuck float NOT NULL ,
Bezeichnung char(225) NOT NULL ,
Gewicht float NOT NULL
);

CREATE TABLE Bestellung (
BNr integer PRIMARY KEY
KNr integer,
ArtNr integer,
Anzahl integer NOT NULL ,
Bestelldatum date NOT NULL ,
FOREIGN KEY (KNr) REFERENCES Kunde (KNr),
FOREIGN KEY (ArtNr) REFERENCES Artikel (ArtNr)
);Do we need that "NOT NULL" ? Is the rest correct so far? What other properties can we use?

It looks correct to me. (Nod)

Do we need NOT NULL? That depends. Does it make sense if we leave it out?
If we have a customer, then it seems that the least we need is a nachname, so I think that one should be NOT NULL.
Is it a problem if we don't have a vorname? Maybe not. So perhaps we shouldn't have it there. :unsure:

As for other properties, if we want to, we can add a CHECK on the value of an attribute.
For instance Gewicht should probably be greater than zero if it is specified.
Then again, we may be overthinking it if we start specifying things like that. 🤔
 
Last edited:
  • #7
Klaas van Aarsen said:
I think there are 2 options here:
1. The deletion of the customer causes an error if there are references to the customer.
2. All corresponding rows in Bestellung are automatically deleted as well if we try to delete a customer.
🤔

In the case of a change of KNr we have more or less the same thing:
1. A change of KNr in Kunde causes an error if there are references to the customer.
2. All corresponding rows in Bestellung are automatically updated as well if we try to change the KNr of a customer.
🤔

So you mean either the deletion/change of KNr in Kunde (ArtNr in Artikel respectively) would cause an error or the corresponding row in Bestellung is deleted/updated automatically? :unsure:
Klaas van Aarsen said:
Do we need NOT NULL? That depends. Does it make sense if we leave it out?
If we have a customer, then it seems that the least we need is a nachname, so I think that one should be NOT NULL.
Is it a problem if we don't have a vorname? Maybe not. So perhaps we shouldn't have it there. :unsure:

As for other properties, if we want to, we can add a CHECK on the value of an attribute.
For instance Gewicht should probably be greater than zero if it is specified.
Then again, we may be overthinking it if we start specifying things like that. 🤔

So should it be as follows ?

CREATE TABLE Kunde (
KNr INTEGER PRIMARY KEY,
Nachname CHAR(225) NOT NULL,
Vorname CHAR(225),
Strasse CHAR(225),
Hausnr INTEGER,
PLZ INTEGER,
Ort CHAR(225)
);

CREATE TABLE Artikel (
ArtNr INTEGER PRIMARY KEY,
PreisProStuck FLOAT CHECK (PreisProStuck > 0),
Bezeichnung CHAR(225),
Gewicht FLOAT CHECK (Gewicht > 0)
);

CREATE TABLE Bestellung (
BNr INTEGER PRIMARY KEY
KNr INTEGER,
ArtNr INTEGER,
Anzahl INTEGER NOT NULL,
Bestelldatum DATE,
FOREIGN KEY (KNr) REFERENCES Kunde (KNr),
FOREIGN KEY (ArtNr) REFERENCES Artikel (ArtNr)
); Or could we add/improve something ? :unsure:
 
  • #8
I think it is fine. (Nod)
 

FAQ: Understanding Primary and Foreign Keys in Database Relations

What is a primary key?

A primary key is a unique identifier for a specific record in a database table. It is used to ensure that each record in the table is distinct and can be easily referenced.

Why are primary keys important?

Primary keys are important because they help maintain data integrity and prevent duplicate records in a database. They also make it easier to search and retrieve specific records.

What is a foreign key?

A foreign key is a field in a database table that refers to a primary key in another table. It is used to establish relationships between different tables in a database.

What is the purpose of foreign keys?

The purpose of foreign keys is to establish and maintain relationships between different tables in a database. They help ensure data consistency and integrity by linking related records together.

Can a primary key be a foreign key?

Yes, a primary key can also be a foreign key. This is often seen in database tables that have a one-to-one relationship, where the primary key of one table is also used as the foreign key in another table.

Similar threads

Replies
5
Views
930
Back
Top