Unique fileds Foreign keys duplicate key and Joins

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.

Grab data from tables

Alter the variable @tableName to the table and also the @tableWhere for the where condition, I found that if you use the standard sql dumps that you was taking allot of other crap with you as well.

The code

DECLARE @colName VARCHAR(100), @colSql VARCHAR(500), @colSQLInsert VARCHAR(500), @TYPE INT, @auto INT, @tableName VARCHAR(50), @tableWhere VARCHAR(500);
SET @tableName = 'tablename';
SET @tableWhere = 'the where condition';
 
-- grab the table column names
DECLARE tablecol cursor FOR
SELECT name, typestat, autoval FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = @tableName);
 
--The @auto is the auto generated fields e.g. primary key. </b>
SET @colSql = '';
SET @colSQLInsert = '';
OPEN tablecol;
fetch tablecol INTO @colName, @TYPE, @auto;
while (@@fetch_status = 0)
BEGIN
       IF (@auto IS NULL)
       BEGIN
              IF (charindex('.',@colName) > 0) SET @colName = '['+@colName+']';
              SET @colSQL = @colName + ',' +@colSQL;
              IF (@TYPE = 2)
                     SET @colSQLInsert = '''''''+isnull(' + @colName + ',0)+'''''',' + @colSQLInsert;
              ELSE
                     SET @colSQLInsert = '''+str(isnull(' + @colName + ',''''))+'',' + @colSQLInsert;
       END
       fetch NEXT FROM tablecol INTO @colName, @TYPE, @auto;
END
close tablecol;
deallocate tablecol;
-- to build the sql statement, since it stops at 255 charactes split the outputs</b>
SELECT 'select (''insert into '+@tableName+' (';
DECLARE @loopingVal INT;
SET @loopingVal =0;
while (len(@colSQL) > @loopingVal)
BEGIN
       SELECT SUBSTRING(@colSQL, @loopingVal, 255);
       SET @loopingVal = @loopingVal + 255;
END
SELECT ') values (';
 
SET @loopingVal =0;
while (len(@colSQLInsert) > @loopingVal)
BEGIN
       SELECT SUBSTRING(@colSQLInsert, @loopingVal, 255);
       SET @loopingVal = @loopingVal + 255;
END
SELECT ')'') from '+@tableName + ' ' + @tableWhere;

The output will be x lines, and if you just copy them into a single line and this will display (once executed) the insert lines.

If anyone has any better methods, please comment 🙂

Hello World!!

Well, might as well do a Hello world in all of the languages. Once you have a database installed, e.g. MySQL, start up the database and goto the query line so that you are able to *talk* to the database. Type in

SELECT 'Hello World';

This should show Hello World in the output of the database.

I shall have to do Hello World in all languages now 🙂