relational tables

[grumpyoldtechs]

is there a way of creating a table which directly references to another table for a field?

e.g

items:
itemid (pkey)
other fields

customers:
customerid (pkey)
other fields

orders:
orderid (pkey)
customerid (fkey)
itemid (fkey)

fkey = foreign key
pkey = primary key - obvious but saves a post if someone doesn't get it

basically at creation level can you create relationships/how/is it easy enough?
or do you have to just join at select/update/insert/delete level?

[russell]

it's called a foreign key.

what db are u using?

[grumpyoldtechs]

mysql ill try to find which version (possibly the newest stable one)

[grumpyoldtechs]

does that look right? (it created the table) just wanted to check i wasn't missing something completely obvious (i used to only working with 1 dimensional databases)

CREATE TABLE `orders` (
`order_id` INT(11) NOT NULL auto_increment,
`customer_id` INT(11) NOT NULL,
`fuel_id` INT(11) NOT NULL,
`amount_ordered` TINYINT(3) NOT NULL,
`order_price` VARCHAR(8) NOT NULL,
`order_date` DATE NOT NULL,
`delivery_date` DATE NOT NULL,
`notes` TEXT NOT NULL,
PRIMARY KEY (`order_id`),
FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
FOREIGN KEY (fuel_id) REFERENCES fuels (fuel_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

[russell]

looks ok to me