How to validate columns in mySQL

[DeadSeaSquirrels]

I am a very new user to mySQL and I am trying to learn right now. I am reading about normalization, and Many to many relational databases. My question is this, when I have a many to many relationship between two tables, I need to create an intermediary table that links items from each table correct? In MS Access I can validate the columns such that the column X for a particular table has to be of the type "something" from another table. For example.


Users:
UserID
First Name
Last Name

Major:
MajorID
Science Major

Anyway, if I wanted to link the two I would need to create another table:

Link:
User ID
Major ID

and I was wondering if there was a way, when I am defining the "Link" table such that the column "User ID" has to be of the type specified in the "Users" table - basically like validation in MS Excel.

Also I was wondering if mySQL creates primary key ID #s automatically or if I have to do that MajorID and UserID stuff myself, and if not, how would you guys suggest doing that?

 

[mundane]

Coming from a different side (PostGresql), not entirely MySQL, I think you can, by the SQL standard, include

name type REFERNECES TABLE(COLUMN)

so ...
create table Link (

UserID int references users(UserID),
MajorID int references major(MajorID)
);

I'm not sure about the exact Syntax for MySQL, but it should be damn close.

 

[CTho9305]

As far as I know, mysql still doesn't validate foreign keys. mysql 5 will (?)

 

[DeadSeaSquirrels]

woah, so how do people deal with normalization issues? Do they just assume the administrator to the database will be smart?

 

[manly]

Originally posted by: DeadSeaSquirrels
woah, so how do people deal with normalization issues? Do they just assume the administrator to the database will be smart?

If MySQL doesn't handle referential integrity (or DBMS feature XYZ), then the application developer has to implement it in a higher level.

Until recently, MySQL was often considered no more than a good, high-speed read-only DBMS. That happens to fit in well with the Web.

 

[WildHorse]

 

[WildHorse]

DeadSeaSquirrels:

1) Yes, you set the primary key for each table yourself.

2) Make sure you set all your tables to TYPE=innodb;

You set them to type= innodb because MySQL 4.1 does allow foreign keys for tables of type Innodb, but not for other table types. If you don't set the type, then the default table type is probably MyISAM (unless different defaults were set up for your environment).

3) You can set the pair of columns in your so-called "link table" as together making the primary key for that table, and ALSO SEPARATELY set each one of the same 2 cols in your "link table" as a foreign key referencing the primary key of one of the other tables.

Users (UserID, First Name, Last Name)

Major (MajorID, Science Major)

Link (UserID, MajorID)
FK UserID -> Users
FK MajorID -> Major

It seems to me MySQL is one of the best DBMS available. Getting on top of its administrative stuff is a real challenge, but once you get it to run, it works beautifully, and extremely fast.

 

[DeadSeaSquirrels]

Oh no, my hosting company is running mySQL 3.23.54. So there is no InnoDB option. Does one of these options let me validate with foreign keys?

HEAP
Berkley DB
ISAM
Merge

 

[awal]

Originally posted by: scott
DeadSeaSquirrels:

1) Yes, you set the primary key for each table yourself.

2) Make sure you set all your tables to TYPE=innodb;

You set them to type= innodb because MySQL 4.1 does allow foreign keys for tables of type Innodb, but not for other table types. If you don't set the type, then the default table type is probably MyISAM (unless different defaults were set up for your environment).

3) You can set the pair of columns in your so-called "link table" as together making the primary key for that table, and ALSO SEPARATELY set each one of the same 2 cols in your "link table" as a foreign key referencing the primary key of one of the other tables.

Users (UserID, First Name, Last Name)

Major (MajorID, Science Major)

Link (UserID, MajorID)
FK UserID -> Users
FK MajorID -> Major

It seems to me MySQL is one of the best DBMS available. Getting on top of its administrative stuff is a real challenge, but once you get it to run, it works beautifully, and extremely fast.

Mysql the best DBMS available.... Dont think so! I am not saying Postgresql is the BEST, but at least it tries to stay with in lines of SQL standards. Check out this site.

 

[WildHorse]

DeadSeaSquirrels:
You said, "my hosting company is running mySQL 3.23.54. So there is no InnoDB option."

Solution: Simply run the server on your same computer where you run your client.



You said, "Does one of these options let me validate with foreign keys?

HEAP
Berkley DB
ISAM
Merge."

Reply: As explained in a previous post, "NO" only TYPE=Innodb supports FKs.

 

[WildHorse]

DeadSeaSquirrels:

It is suggested that you read all the links at this page:

http://www.databasejournal.com/features/mysql/