question on primary and foreign keys ...
lazarus / 16-Databases

----------------------------------------------------------------------
jshand2010 · 2013-02-04 09:22

i am just wondering if the primary and foreign keys created actually
join two or more tables together?

----------------------------------------------------------------------
taazz · 2013-02-04 09:46

primary and foreign keys are restrictions on the table nothing more.
Primary key instructs the table that the values of the key must unique
table wide, and foreign key that the value on the key must exists on
an other table to use it. Anything else is just an interpretation for
you.

----------------------------------------------------------------------
jshand2010 · 2013-02-04 09:57

thanks for your reply.

do you actually need primary keys or/and foreign keys in your database
tables??

----------------------------------------------------------------------
BigChimp · 2013-02-04 10:29

Yes, you do.

----------------------------------------------------------------------
BigChimp · 2013-02-04 10:31

A bit more comprehensive answer:
1. PK/FK is used to maintain referential integrity (RI).
2. If you don't use PK/FK in your relational db, your application must
maintain referential integrity by code, which is annoying.

If you don't need RI, you can just as well use one single big table
.... or easier, one single big file.

However, it's easier to discuss this based on an example...

----------------------------------------------------------------------
jamhitz · 2013-02-04 21:10

Maybe an example you can relate to... On this forum you have users,
and you have posts. Referential Integrity dictates that you cannot
have a post on this forum, unless there is a user who posted it (No
ghosts allowed here   )

Every user is identified by a unique username (eg jshand2010,
JamHitz...).  As such, no two users can ever have the same username.
As such, the USERNAME is a PRIMARY KEY in the USERS(?) table.

Posts  are held in a different table (POSTS maybe?); each post *must*
indicate who posted it by including their USERNAME in the POSTS
table... In the POSTS table the USERNAME is a FOREIGN KEY...

You could then end up with a schema that looks something like this:

Code: [Select]USERS
--------
USERNAME (PK)
REALNAME
JOIN_DATE
.....

POSTS
---------
POST_ID (PK)
POST_DATE
TITLE
USERNAME (FK)
....
This basic example is just the beginning. Try this link for more: http
://publib.boulder.ibm.com/infocenter/soliddb/v6r3/index.jsp?topic=/com
.ibm.swg.im.soliddb.sql.doc/doc/referential.integrity.html

JamHitz

----------------------------------------------------------------------
jshand2010 · 2013-02-18 21:12

I am now completely confused on primary and foreign keys.  with ibm,
it didn't give me very clear understanding on how they work or how the
relate to tables.  i am trying to write a program to manage a small
business.  daily tasks, budgeting, user management, staff management
(inc. disciplinary proceedings, minutes of meetings etc...

Please le me know how i can get this done...i guess i need full
understanding before i write code.

thanks again guys

Quote from: jamhitz on February 04, 2013, 10:10:07 pmMaybe an example
you can relate to... On this forum you have users, and you have posts.
Referential Integrity dictates that you cannot have a post on this
forum, unless there is a user who posted it (No ghosts allowed here 
)

Every user is identified by a unique username (eg jshand2010,
JamHitz...).  As such, no two users can ever have the same username.
As such, the USERNAME is a PRIMARY KEY in the USERS(?) table.

Posts  are held in a different table (POSTS maybe?); each post *must*
indicate who posted it by including their USERNAME in the POSTS
table... In the POSTS table the USERNAME is a FOREIGN KEY...

You could then end up with a schema that looks something like this:

Code: [Select]USERS
--------
USERNAME (PK)
REALNAME
JOIN_DATE
.....

POSTS
---------
POST_ID (PK)
POST_DATE
TITLE
USERNAME (FK)
....
This basic example is just the beginning. Try this link for more: http
://publib.boulder.ibm.com/infocenter/soliddb/v6r3/index.jsp?topic=/com
.ibm.swg.im.soliddb.sql.doc/doc/referential.integrity.html

JamHitz

----------------------------------------------------------------------
goodname · 2013-02-18 23:41

Primary and foreign keys are basic data modelling tools. It appears as
though you want to model a relational database. There are many
introductory tutorials online and in books. Suggest that you find a
tutorial. Covering a large topic like this on a forum would likely be
from many different people an in no logical order.

----------------------------------------------------------------------
BigChimp · 2013-02-19 07:39

What goodname said.

After having read some tutorials about basic relational database
design, you could post a list of the tables (objects/persons) and how
you think they should link using foreign keys.

What db are you going to use? Firebird? PostgreSQL?

----------------------------------------------------------------------
jshand2010 · 2013-02-20 07:23

Quote from: BigChimp on February 19, 2013, 08:39:22 amWhat goodname
said.

After having read some tutorials about basic relational database
design, you could post a list of the tables (objects/persons) and how
you think they should link using foreign keys.

What db are you going to use? Firebird? PostgreSQL?

hi there, i am using postgresql 9.2.3 now, just been upgraded.

my tables are:

create table login(
  user_id serial not null,
  username text not null,
  password text not null,
  primary key (user_id)
);

create table profile(
  user_id serial not null,
  profile_id serial not null,
  firstname text not null,
  middlename text not null,
  lastname text not null,
  address text,
  homephone text,
  workphone text,
  cellphone text,
  pager text,
  emailaddress text,
  personaldetails text,
  primary key (profile_id, user_id),
  foreign key (user_id) references login (user_id)
); //here i am trying to relate the profile_id with the user_id.

create table product(
  user_id serial not null,
  product_id serial not null,
  productname text not null,
  supplier text not null,
  supplieraddress text not null,
  supplierphone text not null,
  supplieremailaddress text not null,
  suppliercontactperson text not null,
  wholesaleprice text,
  retailprice text,
  batchnumber text,
  serialnumber text,
  categorynumber text,
  totalstockonhand text,
  totalstockinstore text,
  totalstockonorder text,
  primary key (product_id, user_id),
  foreign key (user_id) references login (user_id)
); //here i am trying to relate the product_id with the user_id

Please help me, as i'm not sure if i have these tables correctly setup

----------------------------------------------------------------------
BigChimp · 2013-02-20 08:23

Ok, Postgres, fine  (wipes brow: no MySQL!!!)

Comments inline.

In general: are you sure you want all these "not null" constraints?
What if somebody has no middle name? You need not null for the primary
keys, but I would have another look at the others
Code: [Select]create table login(
  user_id serial not null,
  username text not null,
  password text not null, -- as long as you store salted+hashed
passwords, fine...
  primary key (user_id) --looks good
);

create table profile(
  user_id serial not null, --AFAIU this should not be serial as it is
autoincrementing. It shoudl be integer I think (the underlying field
type of serial)
  profile_id serial not null, --ok
  firstname text not null,
  middlename text not null,
  lastname text not null,
  address text, --well, what if one person has 2 addresses? That's a
modelling decision; you could be happy with max 1 address. Otherwise
you'd have a separate table addresses
  homephone text, --likewise for home phone...
  workphone text, -- ...actually, we're seeing a trend...
  cellphone text, --... more phone stuff...
  pager text, --... you could also have a separate table phones with
id,number,type (home, work, etc). Then link to that from profile. This
allows unlimited phone numbers but may require some sorting
  emailaddress text,
  personaldetails text,
  primary key (profile_id, user_id), --nope. A foreign key cannot be
part of a primary key AFAIR. Just use profile_id.
  foreign key (user_id) references login (user_id) --good (as long as
you get rid of the serial as mentioned earlier - there's no chance the
user_id here matches the right user_id in the user table if you let
Postgres pick serial numbers out of a hat.
); //here i am trying to relate the profile_id with the user_id.

create table product(
  user_id serial not null, --no serial...
  product_id serial not null, --ok
  productname text not null,
  supplier text not null,
  supplieraddress text not null,
  supplierphone text not null, --well.... if you had a table persons
(with types, such as employee, supplier, client) that links to a table
phonenumbers... that might be easier from a db modelling standpoint.
Your current setup may work well though.
  supplieremailaddress text not null,
  suppliercontactperson text not null,
  wholesaleprice text, --price as text? Shouldn't this be some kind of
number? See remarks on stock: you might want to break this out if
interested in historical prices
  retailprice text, --price as text? Shouldn't this be some kind of
number?
  batchnumber text,
  serialnumber text,
  categorynumber text, -- This reeks of a foreign key to another table
called categories...
  totalstockonhand text, --ouch ouch ouch. Shouldn't stock be some
kind of numbers?...
  totalstockinstore text, --...also, you might want to break these out
to a separate table with dates if you..
  totalstockonorder text, --... are interested in historical stock
movements etc... but perhaps you are not.
  primary key (product_id, user_id), -- once again, just use
product_id, remove user_id
  foreign key (user_id) references login (user_id) --once again no
serial. Why link products to users? Does a user "own" a product or
something?
); //here i am trying to relate the product_id with the user_id

----------------------------------------------------------------------
jshand2010 · 2013-02-20 08:55

now i'm really confused.  with the login table i am actually wanting
to join to the profile table.  with login and product tables, i am
wanting to connect the current user to the products they actually
sell.

Quote from: BigChimp on February 20, 2013, 09:23:09 amOk, Postgres,
fine  (wipes brow: no MySQL!!!)

Comments inline.

In general: are you sure you want all these "not null" constraints?
What if somebody has no middle name? You need not null for the primary
keys, but I would have another look at the others
Code: [Select]create table login(
  user_id serial not null,
  username text not null,
  password text not null, -- as long as you store salted+hashed
passwords, fine...
  primary key (user_id) --looks good
);

create table profile(
  user_id serial not null, --AFAIU this should not be serial as it is
autoincrementing. It shoudl be integer I think (the underlying field
type of serial)
  profile_id serial not null, --ok
  firstname text not null,
  middlename text not null,
  lastname text not null,
  address text, --well, what if one person has 2 addresses? That's a
modelling decision; you could be happy with max 1 address. Otherwise
you'd have a separate table addresses
  homephone text, --likewise for home phone...
  workphone text, -- ...actually, we're seeing a trend...
  cellphone text, --... more phone stuff...
  pager text, --... you could also have a separate table phones with
id,number,type (home, work, etc). Then link to that from profile. This
allows unlimited phone numbers but may require some sorting
  emailaddress text,
  personaldetails text,
  primary key (profile_id, user_id), --nope. A foreign key cannot be
part of a primary key AFAIR. Just use profile_id.
  foreign key (user_id) references login (user_id) --good (as long as
you get rid of the serial as mentioned earlier - there's no chance the
user_id here matches the right user_id in the user table if you let
Postgres pick serial numbers out of a hat.
); //here i am trying to relate the profile_id with the user_id.

create table product(
  user_id serial not null, --no serial...
  product_id serial not null, --ok
  productname text not null,
  supplier text not null,
  supplieraddress text not null,
  supplierphone text not null, --well.... if you had a table persons
(with types, such as employee, supplier, client) that links to a table
phonenumbers... that might be easier from a db modelling standpoint.
Your current setup may work well though.
  supplieremailaddress text not null,
  suppliercontactperson text not null,
  wholesaleprice text, --price as text? Shouldn't this be some kind of
number? See remarks on stock: you might want to break this out if
interested in historical prices
  retailprice text, --price as text? Shouldn't this be some kind of
number?
  batchnumber text,
  serialnumber text,
  categorynumber text, -- This reeks of a foreign key to another table
called categories...
  totalstockonhand text, --ouch ouch ouch. Shouldn't stock be some
kind of numbers?...
  totalstockinstore text, --...also, you might want to break these out
to a separate table with dates if you..
  totalstockonorder text, --... are interested in historical stock
movements etc... but perhaps you are not.
  primary key (product_id, user_id), -- once again, just use
product_id, remove user_id
  foreign key (user_id) references login (user_id) --once again no
serial. Why link products to users? Does a user "own" a product or
something?
); //here i am trying to relate the product_id with the user_id

----------------------------------------------------------------------
jshand2010 · 2013-02-20 08:58

Also, i am wondering if i can just use username as a primary key
instead of a serial on all tables

Quote from: jshand2010 on February 20, 2013, 09:55:31 amnow i'm really
confused.  with the login table i am actually wanting to join to the
profile table.  with login and product tables, i am wanting to connect
the current user to the products they actually sell.

Quote from: BigChimp on February 20, 2013, 09:23:09 amOk, Postgres,
fine  (wipes brow: no MySQL!!!)

Comments inline.

In general: are you sure you want all these "not null" constraints?
What if somebody has no middle name? You need not null for the primary
keys, but I would have another look at the others
Code: [Select]create table login(
  user_id serial not null,
  username text not null,
  password text not null, -- as long as you store salted+hashed
passwords, fine...
  primary key (user_id) --looks good
);

create table profile(
  user_id serial not null, --AFAIU this should not be serial as it is
autoincrementing. It shoudl be integer I think (the underlying field
type of serial)
  profile_id serial not null, --ok
  firstname text not null,
  middlename text not null,
  lastname text not null,
  address text, --well, what if one person has 2 addresses? That's a
modelling decision; you could be happy with max 1 address. Otherwise
you'd have a separate table addresses
  homephone text, --likewise for home phone...
  workphone text, -- ...actually, we're seeing a trend...
  cellphone text, --... more phone stuff...
  pager text, --... you could also have a separate table phones with
id,number,type (home, work, etc). Then link to that from profile. This
allows unlimited phone numbers but may require some sorting
  emailaddress text,
  personaldetails text,
  primary key (profile_id, user_id), --nope. A foreign key cannot be
part of a primary key AFAIR. Just use profile_id.
  foreign key (user_id) references login (user_id) --good (as long as
you get rid of the serial as mentioned earlier - there's no chance the
user_id here matches the right user_id in the user table if you let
Postgres pick serial numbers out of a hat.
); //here i am trying to relate the profile_id with the user_id.

create table product(
  user_id serial not null, --no serial...
  product_id serial not null, --ok
  productname text not null,
  supplier text not null,
  supplieraddress text not null,
  supplierphone text not null, --well.... if you had a table persons
(with types, such as employee, supplier, client) that links to a table
phonenumbers... that might be easier from a db modelling standpoint.
Your current setup may work well though.
  supplieremailaddress text not null,
  suppliercontactperson text not null,
  wholesaleprice text, --price as text? Shouldn't this be some kind of
number? See remarks on stock: you might want to break this out if
interested in historical prices
  retailprice text, --price as text? Shouldn't this be some kind of
number?
  batchnumber text,
  serialnumber text,
  categorynumber text, -- This reeks of a foreign key to another table
called categories...
  totalstockonhand text, --ouch ouch ouch. Shouldn't stock be some
kind of numbers?...
  totalstockinstore text, --...also, you might want to break these out
to a separate table with dates if you..
  totalstockonorder text, --... are interested in historical stock
movements etc... but perhaps you are not.
  primary key (product_id, user_id), -- once again, just use
product_id, remove user_id
  foreign key (user_id) references login (user_id) --once again no
serial. Why link products to users? Does a user "own" a product or
something?
); //here i am trying to relate the product_id with the user_id

----------------------------------------------------------------------
taazz · 2013-02-20 09:50

Quote from: jshand2010 on February 20, 2013, 09:58:20 amAlso, i am
wondering if i can just use username as a primary key instead of a
serial on all tables

of course you can, there is nothing preventing you to use any string
field on the primary key. The problem of using string fields for
primary keys is one of speed, disk space and useability.

Its an issue of speed because it is faster to compare a 32bit or 64bit
number instead of an array of chars and that does not take in to
account the collation specific code which is usually more complicated
from a simple comparison of greater or smaller.

Its an issue of disk space because using the string type for a primary
and foreign key you will be coping all all the data of that field to
all the foreign key tables so instead of 4byte integer in each table
you have a name which usually is longer than 4 chars and based on the
name language collation etc that 4 chars can be 8 bytes.

Its an issue of useability because every time the user name is changed
all the foreign keys on all the foreign tables must be changed as well
you have to make sure to either change everything or have orphan
records all over the database.

----------------------------------------------------------------------
BigChimp · 2013-02-20 11:06

Quote from: jshand2010 on February 20, 2013, 09:55:31 amnow i'm really
confused.  with the login table i am actually wanting to join to the
profile table. 
In your example, the profile table did attempt to link to the login
table. What's your question exactly?
(Actually, looking at it again, why are the login and profile tables
separate? Do you envision multiple profiles for one login?)

A good way to test data models is to create a db out of them and start
adding data based on some scenarios/use cases, e.g. a new employee
enters the company, a new product is sold, etc.
Try adding an employee and a profile in your db and see if you can.

Quote from: jshand2010 on February 20, 2013, 09:55:31 amwith login and
product tables, i am wanting to connect the current user to the
products they actually sell.
Ok, now what happens if 2 different employees sell a product? Do you
update the products table with the latest userid?
Looks to me like you need something like a new table sales, with
fields like sale date, units sold etc and foreign keys to the login
and product tables.

At the risk of confusing you further, there are a lot of sample
diagrams available at
http://www.databaseanswers.org/data_models/
Some of these will model stock, sales, employees etc.

Quote from: jshand2010Also, i am wondering if i can just use username
as a primary key instead of a serial on all tables
As taazz explained: it is possible, but I wouldn't do it. Not unless
you have some experience in database design.

----------------------------------------------------------------------
goodname · 2013-02-20 18:13

Saw an analysis of using text fields for primary and foreign keys.
Cannot find the article now but the results showed almost no
difference in performance for PostgreSQL between text and serial key
relations.

Text fields use more space of the foreign table. As for usability it
depends on what your using the data for. Cases where text field keys
make sense are for small look up lists. Think of a combo box limited
to a small set of possible values.

In your username case you may want to create a unique index on the
user name if you use serial for the primary key.

----------------------------------------------------------------------
jshand2010 · 2013-02-20 20:06

i have created a table called login with variables below:

create table login(
  username text not null,
  password text not null
);

i have also create a unique index for this table:

create unique index login_id on login (username); //this should be
correct hopefully.

i am also trying to figure out how to code this correctly.  i know the
basics of how to read and write to and from a database.

----------------------------------------------------------------------
goodname · 2013-02-20 20:28

The login table you have made the username can be the primary key.
Primary keys are always unique and not null. You only want a unique
index on username if the primary key is a serial number. There should
always be a primary key unless you have found a good mathematical
provable reason not to use one.

I'm assuming that you would want usernames to be unique on your
system.

----------------------------------------------------------------------
jshand2010 · 2013-02-20 21:36

thanks for your help so far.

now, that i have created login table successfully, i am now wanting to
be able to join the products table to the username.  what i am wanting
to do is keep track of what is sold by username in action.

----------------------------------------------------------------------
jshand2010 · 2013-02-20 21:37

i have tried my best with this table sample here:

create table product(
  product_id serial not null,
  productname text not null,
  supplier text not null,
  supplieraddress text not null,
  supplierphone text not null,
  supplieremailaddress text not null,
  suppliercontactperson text not null,
  wholesaleprice text,
  retailprice text,
  batchnumber text,
  serialnumber text,
  categorynumber text,
  totalstockonhand text,
  totalstockinstore text,
  totalstockonorder text,
  primary key (product_id),
  foreign key (username) references login (username)
);

----------------------------------------------------------------------
jshand2010 · 2013-02-20 21:49

i have also written these tables, but have not put them to the
database yet:

//profile table

create table profile(
  profile_id serial not null,
  fullname text not null, //this is a text box
  address text, //this is a memo box
  homephone text, //the homephone, work phone, cellphone and pager is
controlled in a combo box and a single text box
  workphone text,
  cellphone text,
  pager text,
  emailaddress text, //this is a text box
  personaldetails text, //this is a memo box
  primary key (profile_id),
  foreign key (username) references login (username)
);

//product table

create table product(
  product_id serial not null,
  productname text not null, //text box
  supplier text not null, //text box
  supplieraddress text not null, //memo box
  supplierphone text not null, //text box
  supplieremailaddress text not null, //text box
  suppliercontactperson text not null, //text box
  wholesaleprice float,
  retailprice float,
  batchnumber text,
  serialnumber text,
  categorynumber integer,
  totalstockonhand integer,
  totalstockinstore integer,
  totalstockonorder integer,
  primary key (product_id),
  foreign key (username) references login (username)
);

I have changed a few things in regards to these table.  just not sure
if i have them correct just yet

thanks again for your help

----------------------------------------------------------------------
goodname · 2013-02-20 22:12

May not have been clear earlier. All tables should have a primary key.
Have you created a primary key on the login table?

If the "username" text field is the primary key then you don't need a
separate unique index.
If a integer/serial is the primary key then you should use a unique
index on the "username" field so that you have unique user names in
your system.