StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Your example has a number of problems, first of which is that you're linking two non-related auto-incrementing ids. This is a nightmare waiting to happen. If anything happens to get those two ids out of sync, you're dead in the water.</p> <p>In your example, the 'employees' record would be considered the parent record, upon which the 'salary' record is dependent (i.e. - you might have an employee record without a corresponding salary record, but you wouldn't want a salary record that's not associated with an employee).</p> <p>Foreign key constraints belong in the child table, as stated in the MySql documentation. As such, what you need in the 'salary' table is a column that looks like this:</p> <p><code>EmployeeId</code> INT NOT NULL</p> <p>Your foreign key would be </p> <p>ALTER TABLE salary ADD FOREIGN KEY (EmployeeId) REFERENCES employee (id) ON DELETE CASCADE ON UPDATE CASCADE</p> <p>At this point, you still have a many-to-one relationship, as there is nothing to prevent you from inserting multiple entries into the salary table with the same EmployeeId.</p> <p>To make this relationship one-to-one, you have to create a unique index on the salary.EmployeeId column.</p> <p>With this type of relationship, it's important to note that:</p> <ol> <li>you are prevented from inserting a row into salary that doesn't have valid EmployeeId</li> <li>you are prevented from inserting a row into salary that has a duplicate EmployeeId</li> <li>when you delete a salary record, the employee record remains untouched.</li> <li>deleting the employee record results in the deletion of the salary record it is referencing (if you don't want this behavior, change ON DELETE CASCADE to something else)</li> <li>you are prevented from dropping the employee table until the salary table is empty and dropped.</li> </ol>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload