MySQL Table Relationship problem 3

[Kamikaze2103]

I am a begginer. I used to work with MS Access and now I have to learn MySQL VERY FAST.
I have read about 4 books and I don't understand how to create tables linked by relations. If I want to create for example an E-Shop do I need relations and if yes could anybody show me an example of relationship ?

If I don't need relations between the tables how can I use the tables ?

 

[obadare]

You can use relations but can't enforce referential integrity.You would have to use JOINS in your queries to use multiple tables

 

[QatQat]

You can also enforce referencial integrity if you use InnoDB tables.


Here is an example:

CREATE TABLE users (
userID int(10) unsigned NOT NULL auto_increment,
username varchar(40) NOT NULL default '',
userdateadd datetime default '0000-00-00 00:00:00',
userdatemodified timestamp(14) NOT NULL,
PRIMARY KEY (userID)
) TYPE=InnoDB;
CREATE TABLE login (
loginID int(10) unsigned NOT NULL auto_increment,
userID int(10) unsigned NOT NULL default '0',
loginuser varchar(30) NOT NULL default '',
loginpassword varchar(30) NOT NULL default '',
logindateadd datetime default NULL,
logindatemodified timestamp(14) NOT NULL,
PRIMARY KEY (loginID),
KEY LOGINID_IND (loginID),
KEY USERID_IND (userID),
FOREIGN KEY (`userID`) REFERENCES `users` (`userID`) ON DELETE CASCADE
) TYPE=InnoDB;

If you delete a user the related login details will be deleted.


Bye

Qatqat

Life is what happens when you are making other plans.

 

[Kamikaze2103]

How can I create tables InnoDB ?

Thank you very much (both of you) for taking the time to reply !!!

 

[BNPMike]

Firstly please don't use the word relation. This is commonly thought to mean connections between tables. In fact it is the mathematical term for a table. The term lives on in 'relational' but otherwise nobody uses it(except erroneously) since the term 'table' appeared more-or-less as soon as relational databases started to be built.

Relationships are a confusing feature of Access. The whole point of the Relational Database Model is there are no declared relationships. You can declare such things as referential integrity and assertions but these are not the same. In a relational database like Access or MySQL you make run-time connections using eg SQL which creates dynamic joins. You can join anything to anything as long as the data types are compatible.

Access allows you to declare relationships but what is actually happenning is it is using this information as a hint for itself in certain situations eg when you create a query in the Query screen. Otherwise there is absolutely no difference in the SQL that Access generates for two tables whether or not relationships have been declared.

For normal relational databases like MySQL you create connections using the join elements of SQL.

 

[QatQat]

Just add the

Type = InnoDB

declaration after your sql create table script.


Bye

Qatqat

Life is what happens when you are making other plans.

 

[Kamikaze2103]

Thank you very much for the infos so far. I have only one more question: How do I use joins ???

Using SELECT (querys) ???

 

[QatQat]

well, it gets a bit longer here anyway.

try this out

create table cars (
carID smallint unsigned primary key auto_increment,
CarName varchar (30)
);

create table drivers (
driverID smallint unsigned primary key auto_increment,
DriverName varchar (30),
CarID smallint unsigned NULL
);




insert three drivers with a car and one without (leave the CarID blank)

select drivername, carname from drivers INNER JOIN cars on drivers.carID = cars.carID


select drivername, carname from drivers LEFT OUTER JOIN cars on drivers.carID = cars.carID

Spot the difference?
This is just a simple example but there is a lot more

http://www.mysql.com/doc/en/JOIN.html

Bye

QatQat



Life is what happens when you are making other plans.