Database Design Problem with Hibernate Primary Key and Foreign Key
From: Tung Chau (tungchau81_at_yahoo.com)
Date: 09/03/04
- Previous message: Jim Garrison: "Re: Create ResultSet "from scratch" in Oracle Java Stored Procedure?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: 2 Sep 2004 21:08:17 -0700
hi,
I am a newbie to Hibernate. It seems to me that Hibernate does not
allow you to declare both id and composite-id in .hbm.xml mapping.
That was why i have a triple primary key in my ENROLLED table as
following:
CREATE TABLE ENROLLED ( SID INTEGER NOT NULL,
CID INTEGER NOT NULL,
ENROLL_SINCE DATETIME NOT NULL,
EID INTEGER NOT NULL auto_increment,
COURSE_ROLE_ID INTEGER DEFAULT 0,
LAST_LESSON_ID INTEGER,
COMPLETE_BY_DATE DATETIME,
GRADE VARCHAR(10),
STATUS_ID MEDIUMINT(9),
IS_AVAILABLE CHAR(1) DEFAULT 'Y',
CREATION_DATE DATETIME NOT NULL,
LAST_UPDATED DATETIME NOT NULL,
PRIMARY KEY (SID, CID, ENROLL_SINCE),
UNIQUE KEY (EID),
FOREIGN KEY(SID) REFERENCES USERS .....
and my QUIZ_GRADES table need to reference the ENROLLED table. That
was why I had EID as unique key.
CREATE TABLE QUIZ_GRADES (QUIZ_ID INTEGER NOT NULL,
EID INTEGER NOT NULL,
PASS CHAR(1),
CREATION_DATE DATETIME NOT NULL,
PRIMARY KEY(QUIZ_ID, EID),
FOREIGN KEY (QUIZ_ID)
REFERENCES QUIZES
ON DELETE CASCADE,
FOREIGN KEY (EID)
REFERENCES ENROLLED(EID)
ON DELETE CASCADE)TYPE=INNODB;
However, Hibernate complained as following:
"******Foreign key (QUIZ_GRADES [EID])) must have same number of
columns as the r
eferenced primary key (ENROLLED [SID,CID,ENROLL_SINCE])
net.sf.hibernate.MappingException: Foreign key (QUIZ_GRADES [EID]))
must have sa
me number of columns as the referenced primary key (ENROLLED
[SID,CID,ENROLL_SIN
CE])"
I can't decleare both id and composite-id in Enrolled.hbm.xml. My
original design of the ENROLLED table was as following:
CREATE TABLE ENROLLED (EID INTEGER NOT NULL auto_increment PRIMARY
KEY,
SID INTEGER NOT NULL,
CID INTEGER NOT NULL,
COURSE_ROLE_ID INTEGER DEFAULT 0,
LAST_LESSON_ID INTEGER,
ENROLL_SINCE DATETIME NOT NULL,
COMPLETE_BY_DATE DATETIME,
GRADE VARCHAR(10),
STATUS_ID MEDIUMINT(9),
IS_AVAILABLE CHAR(1) DEFAULT 'Y',
CREATION_DATE DATETIME NOT NULL,
LAST_UPDATED DATETIME NOT NULL,
UNIQUE(SID, CID, ENROLL_SINCE),
FOREIGN KEY(SID) REFERENCES USERS(USER_ID)
ON DELETE CASCADE,
FOREIGN KEY(CID) REFERENCES COURSES(ID)
ON DELETE CASCADE,
FOREIGN KEY(COURSE_ROLE_ID)
REFERENCES COURSE_ROLES(ROLE_ID) ON DELETE SET NULL,
FOREIGN KEY(LAST_LESSON_ID)
REFERENCES LESSONS(LESSON_ID)
ON DELETE SET NULL,
FOREIGN KEY(STATUS_ID) REFERENCES STATUS
)TYPE=INNODB;
I couldn't find any "unique" declaration syntax in Hibernate's
mapping. That was why I had to stick with composite-id. I like using
composite-id though.
Do you have any suggestion to go around this problem?
I appreciate any help.
Thanks,
-tungchau
- Previous message: Jim Garrison: "Re: Create ResultSet "from scratch" in Oracle Java Stored Procedure?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
