Understanding SQL

Lethal` · 2 Jul 2009 at 16:42

I'm in the process of trying to get to grips with MySQL and actually use it.

I have been supplied some code to create some tables in a database. I understand most of it, but not all...

First table:

Code:

CREATE TABLE IF NOT EXISTS `players` ( 
		  `pid` varchar(32) NOT NULL, 
		  `firstname` varchar(32) NOT NULL, 
		  `lastname` varchar(32) NOT NULL, 
		  `lastip` varchar(32) NOT NULL, 
		  `connections` int(11) NOT NULL default 1, 
		  `lastconnect` timestamp NOT NULL default CURRENT_TIMESTAMP, 
		  PRIMARY KEY (`pid`) 
		) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

Second table:

Code:

CREATE TABLE IF NOT EXISTS `playernames` ( 
		  `pid` varchar(32) NOT NULL, 
		  `name` varchar(32) NOT NULL, 
		  `last` timestamp NOT NULL default CURRENT_TIMESTAMP, 
		  CONSTRAINT PRIMARY KEY (`pid`, `name`) 
		) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Third table:

Code:

CREATE TABLE IF NOT EXISTS `playerips` ( 
		  `pid` varchar(32) NOT NULL, 
		  `ip` varchar(32) NOT NULL, 
		  `last` timestamp NOT NULL default CURRENT_TIMESTAMP, 
		  CONSTRAINT PRIMARY KEY (`pid`, `ip`) 
		) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

In the second and third table it has CONSTRAINT before PRIMARY KEY, what is the purpose of this? What does it do?

Thanks for any help

Kriist · 2 Jul 2009 at 16:46

ignore, talking rubbish
edit: wasn't talking rubbish, just didn't word it correctly. Spunkey is correct!

GravyMonster · 2 Jul 2009 at 16:47

It's there because in the second and thrid tables, there is more than one primary key so the syntax differs slightly.

Lethal` · 2 Jul 2009 at 17:01

Hmm, in a book on PHP/MySQL that I have it has some SQL to create a table with more than one primary key, but it only uses:

Code:

primary key (orderid, isbn)

Haircut · 2 Jul 2009 at 17:04

Spunkey said:
It's there because in the second and thrid tables, there is more than one primary key so the syntax differs slightly.

Just because it's a composite key doesn't mean you need to specify the CONSTRAINT keyword, at least not in any of the SQL variants I've ever used.
Not used MySQL though, so of course I could be wrong!
It would work perfectly well without it in Oracle/SQL Server/Sybase anyway.

The CONSTRAINT keywork is generally used to give the constraint a user specified name rather than a system generated one.
So

Code:

CONSTRAINT playerips_pk PRIMARY KEY (`pid`, `ip`)

would name the primary key playerips_pk

Dj_Jestar · 2 Jul 2009 at 18:07

It's an optional keyword, and they opted to not use it on the first table.

Lethal` · 3 Jul 2009 at 12:48

Okay, thanks. It also has this SQL to insert entries into the database:

Code:

"INSERT INTO players (pid, firstname, lastname, lastip) VALUES ('%s', '%s', '%s', '%s') ON DUPLICATE KEY UPDATE lastname = VALUES(lastname), lastip = VALUES(lastip), connections = connections + 1, lastconnect = NOW()", auth, clientNameBuffer, clientNameBuffer, ipaddr

(The formatting values are there, as it is part of a formatting function.)

I'm having trouble understanding the "ON DUPLICATE KEY" command, I realise what it is supposed to do but don't quite understand how it is working. Could someone explain this for me?

Also, is there a good tutorial about DUPLICATE KEY that someone has a link to?

Dj_Jestar · 3 Jul 2009 at 13:35

ON DUPLICATE KEY when translated to English means "When a record with existing key is found, do this instead"

In technical terms it is actually saying "If Duplicate Key Error is raised, do this"

Lethal` · 3 Jul 2009 at 15:30

Thanks Jestar, another question...

Here are the SQL queries to insert data into the other two tables:

playernames:

Code:

INSERT INTO playernames (pid, name, last) VALUES ('%s', '%s', NOW()) ON DUPLICATE KEY UPDATE last = NOW()", auth, clientNameBuffer

playerips:

Code:

INSERT INTO playerips (pid, ip, last) VALUES ('%s', '%s', NOW()) ON DUPLICATE KEY UPDATE last = NOW()", auth, ipaddr

To make it easier to understand, these three queries are ran when a player joins a game server.

When the first query (in the post above) is ran it doesn't add a new row if it finds the player's ID already there, it just updates there lastname, lastip, connections and lastconnect. When the second query runs it adds a new row even if their name is different, but if the name is the same it will just update the last time value. Similar concept for the third query.

What makes the first query behave in a different way to the second and third?

Dj_Jestar · 3 Jul 2009 at 16:04

see what the differences are between the respective ON DUPLICATE KEYs.

Lethal` · 3 Jul 2009 at 17:54

I'm confused, I don't understand...

Dj_Jestar · 3 Jul 2009 at 17:57

Code:

UPDATE last = NOW()", auth, clientNameBuffer

Code:

UPDATE last = NOW()", auth, ipaddr

Lethal` · 3 Jul 2009 at 18:30

I'm sorry that doesn't really explain it, I can see the difference but I don't understand it...

Dj_Jestar · 3 Jul 2009 at 18:43

it's updating those columns when a duplicate key is found, instead of inserting a new record.