distal-attribute
distal-attribute
distal-attribute
distal-attribute

Trying to Create Table with Self-Referencing Relationships

User, date Message
Written by tpclark
7 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
7 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
7 years ago
5 posts since Wed, 04 Apr 07
So Self Referencing relationships have to be created using Alter Table?
Written by siMKin
7 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
7 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
7 years ago
5 posts since Wed, 04 Apr 07
BTW, I have Faculty and Course already created.
Written by siMKin
7 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
7 years ago
5 posts since Wed, 04 Apr 07
Thank you for the help!!happy
It worked!
 

Please login to leave a reply, or register at first.