StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<h1>Changing your table scheme</h1> <h2>Adding Unique constraint</h2> <p>this is your insert query:</p> <pre><code>INSERT INTO salary (name,sex,state,country) VALUES (?,?,?,?) </code></pre> <p>I will assume your table has the following schema</p> <pre><code>salary (name, sex, state, country) </code></pre> <p>You don't want records to be duplicated. Therefore you must have some method of checking wether a record is already inserted or not, which means one (or a combination of) column must be unique. My guess is that your xml files do not contain any <code>unique identifiers</code> per <code>employee</code>.</p> <p>We should then consider adding a constraint to the table that gathers enough info so that the number of collisions is low.</p> <pre><code>ALTER TABLE salary ADD UNIQUE KEY (name,sex,state,country) //This sql statement is intended for MySQL version 5.1 please check your's </code></pre> <p>With this constraint, the only duplicates allowed are people in the same country and state, of the same sex and with the same name.</p> <h2>Adding a Primary key</h2> <p>Now, this will not be a good solution so I propose a better one on the following assumption</p> <ul> <li>employees have a unique ID and that it can be found in the xml</li> </ul> <p>Ok, now it's very simple, but tedious</p> <ul> <li>we need to add a column to the table <code>ALTER TABLE salary ADD COLUMN...</code></li> <li>we need to populate <strong>all</strong> tuples with the unique ID of each employee</li> <li>we finally add the <strong>important</strong> constraint <code>ALTER TABLE salary ADD PRIMARY KEY...</code></li> </ul> <p>The table is now optimized for quick searches</p>

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