Question about our ISD3 exercise

markj · Posts: 69 Join date: 2008-09-23

Hi guys,

I did not understand key very well. In our handout, it is said key should not duplicate. but in our exercise, why AccNo can be duplicated and is also a key?

Nico · Posts: 48 Join date: 2008-09-18

You have to distinguish primary key and foreign key.

Primary means, one data of one column (column which is defined as a primary key) or a set of data (one row) cannot be duplicated within this column. for instance, if name is a primary key you can have:

Code:

name is clever?
weij yes
nicolas no

but you cannot have:

Code:

name is clever?
weij yes
weij maybe
nicolas no

I do not have microsoft access right now, but i do not think one account number, let say 00580 appears two times in the same column "account number" of the same table.

After that you have the "foreign key":
For instance, name will be a primary key in the first table "name and clever", and a foreign key in the second table "name and module":

Code:

name(primary key) is clever?
weij yes
nicolas no

name(foreign key) module
weij ISD
weij sequence analysis under unix
nicolas ISD
nicolas sequence analysis under unix

as you can see name is duplicated in the second table because it is a foreign key but not in the first one. However, it is a key (foreign key) because you have put a link between the two tables through the "name" attribute; Hence, name is a key in the second table and is duplicated. Very Happy

if you are still struggling, i found this link, maybe there is a better website, have a look:

http://www.databasedev.co.uk/primary_foreign_key_constraints.html

markj · Posts: 69 Join date: 2008-09-23

Nico,

Thanks very much. I think I've understanded better after read in the website you provided.

In the case which I asked, both columns 'AccNo' and 'CustId' were defined as one primary key

Many thanks again

cheers

Nico wrote:

You have to distinguish primary key and foreign key.

Primary means, one data of one column (column which is defined as a primary key) or a set of data (one row) cannot be duplicated within this column. for instance, if name is a primary key you can have:

Code:

name is clever?
weij yes
nicolas no

but you cannot have:

Code:

name is clever?
weij yes
weij maybe
nicolas no

I do not have microsoft access right now, but i do not think one account number, let say 00580 appears two times in the same column "account number" of the same table.

After that you have the "foreign key":
For instance, name will be a primary key in the first table "name and clever", and a foreign key in the second table "name and module":

Code:

name(primary key) is clever?
weij yes
nicolas no

name(foreign key) module
weij ISD
weij sequence analysis under unix
nicolas ISD
nicolas sequence analysis under unix

as you can see name is duplicated in the second table because it is a foreign key but not in the first one. However, it is a key (foreign key) because you have put a link between the two tables through the "name" attribute; Hence, name is a key in the second table and is duplicated. Very Happy

if you are still struggling, i found this link, maybe there is a better website, have a look:

http://www.databasedev.co.uk/primary_foreign_key_constraints.html