Entry Check 2

[M2E]

Hi there,

I'm quite new to SQL .. I think therefore what I'm asking is pretty basic...I just can't get the correct syntax.

Basically I want to insert information into a datbase, but before that information goes in, I want to check that it does not already exsist? How do I add this with an insert command?

Thanking you in advance...

 

[sleipnir214]

You have a couple of options.

One is to make the columns on which you will make the "already exists" decision part of a unique key. If you attempt to insert a record with the same values as in the key, the insert will fail.

Another is to perform a SELECT query to check for "already exist" records, then performing the insert if necessary.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[M2E]

Thanks for that.

Would the second option have less of a strain on the database?

Would it be possible to provide an example of code?

Lastly do you know a good SQL reference where I could researh keywords etc?

Thanks again

 

[sleipnir214]

Strain on the database"? I don't know.

A good general reference on SQL? Sorry, don't know of a general one. MySQL has its own SQL reference in chapter 13 of its online manual:

http://dev.mysql.com/doc/mysql/en/index.html

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[TonyGroves]

When you say "strain on the database", I suppose you mean speed.

If your only requirement is to prevent the modification of records which already exist, then both of Sleipnir214's options will do that; the first involves less processing, both at the client and server ends, and is therefore faster. You don't need any special syntax to achieve this; just a plain INSERT statement. Your progam can then check the result of the insert and take whatever action it wants; the syntax here obviously depends on the programming language and database interface used.

Regarding MySQL reference material, a generic SQL reference book would be of limited use as each database system has its own way of doing things; you really need one specific to MySQL. A favourite one is "MySQL" (3rd edition!) by P. DuBois, published by Developers' Library. There's also the MySQL reference manual, which is available in PDF and HTML forms, but it is quite technical for a beginner.

 

[M2E]

Hi there,

Thanks again for the information. In MySQL I am now trying to make a field a unique field. However I keep getting the following error...

#1170 - BLOB column 'email' used in key specification without a key length

What does this mean, and how can I specify the key length?

Thanks

 

[TonyGroves]

If you're indexing a BLOB field, you need to specify a key length:[tt]
ALTER TABLE t ADD UNIQUE (email(50))[tt]
This means that records will be considered duplicate if the first 50 bytes of the field "email" are identical.

However, are you sure you need a Blob field? If it's an e-mail address you're storing, a CHAR or VARCHAR field would be perfect.

 

[TonyGroves]

Sorry, you can ignore those [tt] bits in the last post. They're the result of a failed attempt at formatting.