How do I make a column not null and be a foreign key in MySQL -

i trying create table foreign key of table , make not null, running trouble making both happen. able foreign keys working did not require not null can't both working.

here line giving me trouble

constraint instructor foreign key (id) references instructor(id) not null 

then error:

constraint instructor foreign key (id) references instructor(id) not null,                                                                  * error @ line 5: ora-00907: missing right parenthesis 

also, getting weird error when trying create table (note, table created after creating table contains above error) fails @ trivial part:

create table enrollment (        constraint class_id foreign key (id) references class(id) not null,        constraint member_id foreign key (id) references reccentermember(id) not null,        cost int not null ); 

then command error:

create table enrollment (                     * error @ line 1: ora-00904: : invalid identifier 

how can fix these 2 errors?

you need create column before try creating constraints on column.

you have:

create table enrollment (        constraint class_id  foreign key (id) references class(id) not null,        constraint member_id foreign key (id) references reccentermember(id) not null,        cost int not null ); 

you need:

create table enrollment (        id int not null constraint class_id references class(id),        constraint member_id foreign key (id) references reccentermember(id),        cost int not null ); 

note in first line, foreign key isn't necessary because column implied. in second line, id identified. write:

create table enrollment (        id int not null,        constraint class_id  foreign key (id) references class(id),        constraint member_id foreign key (id) references reccentermember(id),        cost int not null ); 

it unusual, though not automatically wrong, make single column (id) foreign key of 2 tables simultaneously. isn't clear if want 3 columns in table — , if do, column names in table.

you use appropriate notation automatically allocated type in mysql syntax (serial instead of int not null, or add auto_increment, etc).

maybe you're after:

create table enrollment (        id          serial,        class_id    int not null constraint class_id  references class(id),        member_id   int not null constraint member_id references reccentermember(id),        cost        int not null ); 

this makes more sense in general. you're creating new enrollment record pre-existing class, , pre-existing recreation centre member, , recording cost.

---

syntax diagrams vs actual behaviour

if, michael - sqlbot suggests — , i've no reason whatsoever disbelieve him — mysql recognizes not respond references clause in column definition in create table statement acts on full foreign key clauses, have adjust suggested answers syntactically correct semantically ignored form like:

option 1 (minimally changing sql question):

create table enrollment (        id int not null,        constraint class_id  foreign key (id) references class(id),        constraint member_id foreign key (id) references reccentermember(id),        cost int not null ); 

option 2 (what consider plausible version):

create table enrollment (        id          serial,        class_id    int not null,        constraint  fk_class_id  foreign key (class_id)  references class(id),        member_id   int not null,        constraint  fk_member_id foreign key (member_id) references reccentermember(id),        cost        int not null ); 

or other variant of syntax based on desired table schema ignoring fk constraints, adding constraints along lines shown.

key point

you must define columns before define foreign keys based on columns.