I have been asked about some SQL questions, so I thought that I would post on here encase it helps others as well. The questions was based around, what is the unique key compared to a primary key, foreign keys (and how to link) and also the joins within a relationship database(outer, inner, left, right).
Unique key
An unique key is kinder similar to a primary key, where the primary key is the main index into the table itself (kinder like a index at the back of the book, where is item 5, arrh there in the book) well the unique key basically means that this column within the table cannot have a similar value within this column compared to any other values stored in other rows in the table. So for example,
Index | UniqueKey |
1 | genux |
2 | bob |
now if I tried to insert a name into the uniquekey field name of “genux” it would complain because there is already a field name of that.
Foreign key
A foreign key is a value that is linked to another table that if that other table ( which normally is the main table of lets say customers) deletes a row from it, then you can setup in the second table (where the foreign key is) to also delete data from its table where they are linked with the foreign key (this can also happen on updates as well instead of deleting data).
So as taken from this page on the mysql website.
CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) ENGINE=INNODB; |
The foreign key is the parent_id field within the child table where it links to the parent tables id field and with the “ON DELETE CASCADE” it will delete any rows within the childs table if the parent table deletes its linking row.
Duplicate keys
Duplicate keys are nice, when you want to insert some data into a table and the index key is already present then you can update the value within the already present row in the table and thus get around the testing for any duplicate keys, you can also use the REPLACE syntax as well (but this will not allow you to increment the present value within the field, but update it to a new value).
Joins, left,right, inner, outer
Joins are basically in mathematical terms linking sets of data together, so a inner join
SELECT * FROM table1 INNER JOIN table2 ON table1.linkId = table2.Id |
This will bring back only the data from both tables that have the linking column values from table1.linkId and table2.Id, in contrast to outer join
SELECT * FROM table1 OUTER JOIN table2 ON table1.linkId = table2.Id |
where all of the values from within the two tables are outputted in the results but values of NULL are inserted into the unmatched result fields.
There is also, left and right join where it is similar to the outer join as in
SELECT * FROM table1 LEFT JOIN table2 ON table1.linkId = table2.Id |
But only all of the values from the left table are still present in the results output and NULLs inserted into the fields where there is no link with the right had side table, of course the values from the right table are inserted where there is a link with the left table, and the right join is similar to the left join apart from all rows are in the results from the right table instead of the left. As a side note, the left table is the table on the left hand side of the join syntax so table1 in this instance.