Trying to Create Table with Self-Referencing Relationships
| User, date | Message |
|---|---|
|
Written by tpclark
6 years ago Category: General 5 posts since Wed, 04 Apr 07 |
Can someone tell me if this error is caused by the constraint statements at the bottom of the query? create table Faculty ( FacSSN char(11) not null, FacFirstName varchar(30) not null, FacLastName varchar(30) not null, FacCity varchar(30) not null, FacState char(2) not null, FacZipCode char(10) not null, FacRank char(4), FacHireDate datetime, FacSalary decimal(10,2), FacSupervisor char(11), FacDept char(6), CONSTRAINT FacultyPK PRIMARY KEY (FacSSN), CONSTRAINT SupervisorFK FOREIGN KEY (FacSupervisor) REFERENCES Faculty ) /* SQL Error: Can't create table '.\university\faculty.frm' (errno: 150) */ |
|
Written by siMKin
6 years ago 104 posts since Sun, 01 Apr 07 |
That is indeed the cause. See: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html a foreign key is always a reference to another table (hence the name foreign) |
|
Written by tpclark
6 years ago 5 posts since Wed, 04 Apr 07 |
So Self Referencing relationships have to be created using Alter Table? |
|
Written by siMKin
6 years ago 104 posts since Sun, 01 Apr 07 |
a reference to it's own field? don't you just mean to create an index? otherwise i don't really understand what you're after... |
|
Written by tpclark
6 years ago 5 posts since Wed, 04 Apr 07 |
Yes, table can refer to themselves. In this case faculty can supervise faculty. I just tryed some other Create Table sql statements that follow the the rules that are spelled out in the reference you gave me. They use standard SQL syntax and they don't work either. For instance, create table offering( OfferNo INTEGER not null, CourseNo char(6) not null, OffTerm char(6) not null, OffYear INTEGER not null, OffLocation varchar(30), OffTime varchar(10), FacSSN char(11), OffDays char(4), CONSTRAINT OfferingPK PRIMARY KEY (OfferNo), CONSTRAINT CourseFK FOREIGN KEY (CourseNo) REFERENCES Course, CONSTRAINT FacultyFK FOREIGN KEY (FacSSN) REFERENCES Faculty ); According to the link you gave me, this should work but I still get an Errno: 150!! |
|
Written by tpclark
6 years ago 5 posts since Wed, 04 Apr 07 |
BTW, I have Faculty and Course already created. |
|
Written by siMKin
6 years ago 104 posts since Sun, 01 Apr 07 |
a reference on the same table is of course possible what you're forgetting though, is to specify the table name. it should be FOREIGN KEY (column_of_table_you're_creating) REFERENCES some_other_table_or_own_table (column_of_that_table_you_want_to_refer_to) i said 'another' table before - which might have been a bit misleading; i just meant you go outside of the scope (if you want to call it that), of your own table .. but of course it can be the same table again. just as you can also join another table on a table, which can be the same table... |
|
Written by tpclark
6 years ago 5 posts since Wed, 04 Apr 07 |
Thank you for the help!! It worked! |
|
Please login to leave a reply, or register at first. |