I am having a problem creating a database table. I have Done Something like:
CREATE TABLE STREAM(
STREAM_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
STREAM_NAME VARCHAR(100) NOT NULL,
TOTAL_SEM INT NOT NULL
)Engine=InnoDB;
and then:
CREATE TABLE SEMESTER(
STREAM_ID INT AUTO_INCREMENT NOT NULL,
SEMESTER INT NOT NULL,
SUB_NAME VARCHAR(100) NOT NULL,
SUB_CODE VARCHAR(100) NOT NULL,
PRIMARY KEY(STREAM_ID,SUB_CODE),
CONSTRAINT STUDENT_STREAM_FK5 FOREIGN KEY (STREAM_ID) REFERENCES STREAM(STREAM_ID)
)Engine=InnoDB;
It creates the Database Tables very well. But When I create another Table referencing the SUB_CODE
in SEMESTER
table it gives me Error: 1215:
CREATE TABLE SUBJECT(
Q_NO INT AUTO_INCREMENT NOT NULL,
SUB_CODE VARCHAR(100) NOT NULL,
SEMESTER VARCHAR(100) NOT NULL,
SET_NO INT NOT NULL,
Q_TYPE VARCHAR(100) NOT NULL,
QUESTION VARCHAR(200) NOT NULL,
CHOICE_1 VARCHAR(30),
CHOICE_2 VARCHAR(30),
CHOICE_3 VARCHAR(30),
CHOICE_4 VARCHAR(30),
CORRECT VARCHAR(30),
ANSWER VARCHAR(30),
MARKS FLOAT,
PRIMARY KEY(Q_NO,SUB_CODE),
CONSTRAINT STUDENT_SEMESTER_FK6 FOREIGN KEY (SUB_CODE) REFERENCES SEMESTER(SUB_CODE)
)Engine=InnoDB;
In that case while creating SUBJECT
table it gives Error:1215. I don't understand, I have created the Table SEMESTER
the same way I am tryng to create SUBJECT
, then why it is not working?
I am using MySQL Server 5.6.
Please Help! Thanks in Advance.
I Solved it anyway. Just made it like:
CREATE TABLE SUBJECT(
Q_NO INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
SUB_CODE VARCHAR(100) NOT NULL REFERENCES SEMESTER(SUB_CODE),
SEMESTER VARCHAR(100) NOT NULL,
SET_NO INT NOT NULL,
Q_TYPE VARCHAR(100) NOT NULL,
QUESTION VARCHAR(200) NOT NULL,
CHOICE_1 VARCHAR(30),
CHOICE_2 VARCHAR(30),
CHOICE_3 VARCHAR(30),
CHOICE_4 VARCHAR(30),
CORRECT VARCHAR(30),
ANSWER VARCHAR(30),
MARKS FLOAT
)Engine=InnoDB;
I ignored making the SUB_CODE
the primary key. And its done. Needed it to be the foreign key of the SEMESTER
table anyway!