i have univarsity sechema as mention following and now i want to add new table but it shows erroe? help me

Question

//University DDL statements

create table classroom
    (building       varchar(15),
     room_number        varchar(7),
     capacity       numeric(4,0),
     primary key (building, room_number)
    );

create table department
    (dept_name  varchar(20), 
     building   varchar(15), 
     budget numeric(12,2) check (budget > 0),
     primary key (dept_name)
    );

create table course
    (course_id      varchar(8), 
     title          varchar(50), 
     dept_name      varchar(20),
     credits        numeric(2,0) check (credits > 0),
     primary key (course_id),
     foreign key (dept_name) references department
        on delete set null
    );

create table instructor
    (ID         varchar(5), 
     name           varchar(20) not null, 
     dept_name      varchar(20), 
     salary         numeric(8,2) check (salary > 29000),
     primary key (ID),
     foreign key (dept_name) references department
        on delete set null
    );

create table section
    (course_id      varchar(8), 
         sec_id         varchar(8),
     semester       varchar(6)
        check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
     year           numeric(4,0) check (year > 1701 and year < 2100), 
     building       varchar(15),
     room_number        varchar(7),
     time_slot_id       varchar(4),
     primary key (course_id, sec_id, semester, year),
     foreign key (course_id) references course
        on delete cascade,
     foreign key (building, room_number) references classroom
        on delete set null
    );

create table teaches
    (ID         varchar(5), 
     course_id      varchar(8),
     sec_id         varchar(8), 
     semester       varchar(6),
     year           numeric(4,0),
     primary key (ID, course_id, sec_id, semester, year),
     foreign key (course_id,sec_id, semester, year) references section
        on delete cascade,
     foreign key (ID) references instructor
        on delete cascade
    );

create table student
    (ID         varchar(5), 
     name           varchar(20) not null, 
     dept_name      varchar(20), 
     tot_cred       numeric(3,0) check (tot_cred >= 0),
     primary key (ID),
     foreign key (dept_name) references department
        on delete set null
    );

create table takes
    (ID         varchar(5), 
     course_id      varchar(8),
     sec_id         varchar(8), 
     semester       varchar(6),
     year           numeric(4,0),
     grade              varchar(2),
     primary key (ID, course_id, sec_id, semester, year),
     foreign key (course_id,sec_id, semester, year) references section
        on delete cascade,
     foreign key (ID) references student
        on delete cascade
    );

create table advisor
    (s_ID           varchar(5),
     i_ID           varchar(5),
     primary key (s_ID),
     foreign key (i_ID) references instructor (ID)
        on delete set null,
     foreign key (s_ID) references student (ID)
        on delete cascade
    );

create table time_slot
    (time_slot_id       varchar(4),
     day            varchar(1),
     start_hr       numeric(2) check (start_hr >= 0 and start_hr < 24),
     start_min      numeric(2) check (start_min >= 0 and start_min < 60),
     end_hr         numeric(2) check (end_hr >= 0 and end_hr < 24),
     end_min        numeric(2) check (end_min >= 0 and end_min < 60),
     primary key (time_slot_id, day, start_hr, start_min)
    );

create table prereq
    (course_id      varchar(8), 
     prereq_id      varchar(8),
     primary key (course_id, prereq_id),
     foreign key (course_id) references course
        on delete cascade,
     foreign key (prereq_id) references course
    );

My DML statement:

create table assistant
    (ID varchar(5), 
     course_id varchar(8),
     sec_id varchar(8), 
     semester varchar(6),
     year numeric(4,0),
     primary key (ID, course_id, sec_id, semester, year),
     foreign key (course_id,sec_id, semester, year) references    
     section(course_id,sec_id, semester, year)on delete cascade,
     foreign key (ID) references student(ID)on delete cascade);

tell me what is wrong in my DML statement? it shows error as " ERROR 1215 (HY000): Cannot add foreign key constraint"

This is a faq. You will be downvoted because you have shown no effort and your question is not useful. Please read the downvote arrow/triangle mouseover text and [ask]. Eg google your error message. Eg google 'mysql more info about foreign key error' and read many hit. Eg search & read the official documentation. Eg read and act on [mcve]. PS You have not even made the effort to spell nouns correctly. — philipxy, Oct 04 '17 at 20:57
Possible duplicate of [MySQL Error 1215: Cannot add foreign key constraint](https://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint) — philipxy, Oct 04 '17 at 21:03

score 0 · Accepted Answer · answered Oct 04 '17 at 09:55

You don need to put the column names after the name of the table you are referencing:

   create table assistant
        (ID varchar(5), 
         course_id varchar(8),
         sec_id varchar(8), 
         semester varchar(6),
         year numeric(4,0),
         primary key (ID, course_id, sec_id, semester, year),
         foreign key (course_id,sec_id, semester, year) references    
             section on delete cascade,
         foreign key (ID) references student on delete cascade);

What you had wrong it's the part in bold:

foreign key (course_id,sec_id, semester, year) references    
     section

(course_id,sec_id, semester, year)

on delete cascade

i have univarsity sechema as mention following and now i want to add new table but it shows erroe? help me

1 Answers1