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

Trying to Create Table with Self-Referencing Relationships

tpclark posted 8 years ago in General
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) */
siMKin posted 8 years ago
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)
tpclark posted 8 years ago
So Self Referencing relationships have to be created using Alter Table?
siMKin posted 8 years ago
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...
tpclark posted 8 years ago
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!!

tpclark posted 8 years ago
BTW, I have Faculty and Course already created.
siMKin posted 8 years ago
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...
tpclark posted 8 years ago
Thank you for the help!!happy
It worked!

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