SQL How to Set Foreign Keys

Question

I need to create 3 tables which look like this

student(sid: CHAR(12), sname: VARCHAR(50), bdate: DATE, address: VARCHAR(50), scity: VARCHAR(20), year: CHAR(20), gpa: FLOAT)

company(cid: CHAR(8), cname: VARCHAR(20))

apply(sid: CHAR(12), cid: CHAR(8))

(Bolded attributes are primary keys)

But I'm not sure how to set the foreign keys since for instance cid of apply table is both primary key in apply table and company table ( which has the same situation for sid between apply table and student table). Thanks for any help.

These are the codes for creating tables:

myQuery = "CREATE TABLE student "
                + "(sid CHAR(12), sname VARCHAR(50), "
                + "bdate DATE, address VARCHAR(50), "
                + "scity VARCHAR(20), year CHAR(20), "
                + "gpa FLOAT) ENGINE=InnoDB;";
 myQuery = "CREATE TABLE company "
                + "(cid CHAR(8), cname VARCHAR(20), quota CHAR(8))ENGINE=InnoDB;";
myQuery = "CREATE TABLE apply "
                + "(sid CHAR(12), cid CHAR(8)) ENGINE=InnoDB;";

the cid in the apply table is the FK, the cid in the company table is the PK — Randy
Use innodb type tables and your problems will be solved user joins to get data — Muhammad Raheel
Craete primary or unique keys: company(cid) and apply(sid), then add foreign keys, see example - stackoverflow.com/questions/10086210/… — Devart
@raheelshan I am already using InnoDB tables but foreign keys should be specified. — droidata

Jeff Allen Jeff Allen · Accepted Answer · 2012-04-20T13:16:51

It looks like the apply table is a many-to-many join between student and company.

In that case, you'd want to set student and company like you have (though posting the output of SHOW CREATE TABLE student may get you more helpful answers). So for the apply table, you want two foreign keys: one on sid which references student.sid, and one on cid which references company.cid. Maybe something like:

ALTER TABLE apply ADD CONSTRAINT sid FOREIGN KEY (sid) REFERENCES student(sid);
ALTER TABLE apply ADD CONSTRAINT cid FOREIGN KEY (cid) REFERENCES copmany(cid);

Edit: Based on your table creation, you're not setting the primary keys either. Add a PRIMARY KEY identifier to any column you wish to be a primary key.

SQL How to Set Foreign Keys

2 Answers