przemek dzikowski <przemekdzikowski@stripped> wrote on 09/15/2005 05:16:15
AM:
> 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.
> Please help!!
>
> Thanks in Advance
> Przemek
I think it's a problem in your field definitions:
CREATE TABLE IF NOT EXISTS sampDB.student (
from1 DATETIME not null,
from2 DATETIME not null,
PRIMARY KEY (from1)
)
You probably created some very strange field names by leaving the "student
." at the beginning of the field names in your definition of student. (You
are aware that DATETIME data is only accurate to the nearest second, don't
you? You will not be able to create two records in student that start in
the same second.) The strange field names would not let the table exist so
that could explain the failure to define foreign keys from the
student_module table.
Your data structure itself seems odd, can you please explain why you don't
have any other fields in your tables except for dates? They seem to serve
no useful purpose.... If you were trying to simplify the problem by
simplifying your table designs, you failed. Due to the extreme lack of
information or due to cut and paste errors, you didn't adequately describe
your real issue. Please, in the future, include the actual output of "SHOW
CREATE TABLE" statements and we will be able to give you more accurate
advice.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
