problem with foreign keys to datetime field

[heroe]

Hello *,

i write you with an urgent problem. I would like to create forign keys
to datetime field, but it doesn't seem to work. I get [Error Code:
1005, SQL State: HY000] Can't create table
'.\sampleDB\student_module.frm' (errno: 150) error.

the tables look like
CREATE TABLE IF NOT EXISTS sampleDB.student (
student.from1 DATETIME not null,
student.from2 DATETIME not null,
PRIMARY KEY (from1))

CREATE TABLE IF NOT EXISTS gruppex.student_module(
student_module.to1 DATETIME not null,
student_module.to2 DATETIME not null,
PRIMARY KEY (to1),
FOREIGN KEY (to1) REFERENCES student (from1),
FOREIGN KEY (to2) REFERENCES student (from2))

I know that i could do this with use of ids fields, but in this case i
need to use of datetime. It works when i use only first foreign key
statemetnt. In case of two it doesn't work even when i make
student.from2 also as primary key
Please help!!

Thanks in Advance
Przemek

[Bill Karwin]

heroe wrote:

Hello *,

i write you with an urgent problem. I would like to create forign keys
to datetime field, but it doesn't seem to work. ...
It works when i use only first foreign key
statemetnt. In case of two it doesn't work even when i make
student.from2 also as primary key

You can make a foreign key reference only to an indexed column in
another table, and if the referenced column is part of a multi-column
index, it must be the first column in that index.
See http://dev.mysql.com/doc/mysql/en/in...nstraints.html

You can only have one primary key in a table, but you can have multiple
indexes defined for different columns. Creating a primary key
implicitly creates an index.

So you can have your primary key on student.from1, but you should define
a simple index for from2. See
http://dev.mysql.com/doc/mysql/en/create-index.html

Then you should be able to define the foreign key to reference the
indexed column.

Regards,
Bill K.

[heroe]

Hello,

I don't agree that you can have only one key in the table.
i create f.e.
CREATE TABLE IF NOT EXISTS sampleDB.student (
student.from1 DATETIME not null,
student.from2 DATETIME not null,
PRIMARY KEY (from1,from2 )) and it creates the table when both columns
are signed as primary key.

I actually have found that the second create is possible but it has to
look like

create table sampleDB.student_module(
to1 DATETIME not null,
to2 DATETIME not null,
primary key (datum1),
FOREIGN KEY (to1, to2) REFERENCES student (from1, from2))

Now it comes howewer problem for more tables for example, the trd table
student_exam with same statement as the first one.
when i now try to create with
create table sampleDB.student_exam(
to1 DATETIME not null,
to2 DATETIME not null,
primary key (datum1),
FOREIGN KEY (to1) REFERENCES student (from1),
FOREIGN KEY (to2) REFERENCES student (to2))

i get the same error like before...

Regards
Przemek

[Bill Karwin]

heroe wrote:

Hello,

I don't agree that you can have only one key in the table.
i create f.e.
CREATE TABLE IF NOT EXISTS sampleDB.student (
student.from1 DATETIME not null,
student.from2 DATETIME not null,
PRIMARY KEY (from1,from2 )) and it creates the table when both columns
are signed as primary key.
That's called a "compound key," when it is defined over multiple
columns. It still counts as one key, and it is permitted. What I meant
was that it is _not_ permitted to do this:

CREATE TABLE IF NOT EXISTS sampleDB.student (
student.from1 DATETIME not null,
student.from2 DATETIME not null,
PRIMARY KEY (from1),
PRIMARY KEY (from2))

That would constitute two primary keys, and it is illegal (and
nonsensical according to relational database theory).
Now it comes howewer problem for more tables for example, the trd table
student_exam with same statement as the first one.
when i now try to create with
create table sampleDB.student_exam(
to1 DATETIME not null,
to2 DATETIME not null,
primary key (datum1),
FOREIGN KEY (to1) REFERENCES student (from1),
FOREIGN KEY (to2) REFERENCES student (to2))

Again, read:
http://dev.mysql.com/doc/mysql/en/in...straints.html:

"In the referenced table, there must be an index where the referenced
columns are listed as the first columns in the same order."

In your student_exam table, the to2 column illegally references
student(from2) (I assume your usage of `to2` as the referenced column is
an error in typing, since no column by that name exists in the student
table).

Anyway, the from2 column does not qualify as the first column in any
index, so you can't make a foreign key reference to it. You can
reference student(from1,from2), or student(from1). The only way you can
reference student(from2) is if you create an additional index with from2
as the first (or only) column.

Regards,
Bill K.