Tiny Deathstars of Foulness

SQL constraints the data that can be in a table. A violation of a constraint causes an action to be aborted. Constraints can be defined upon creation or using the ALTER TABLE statement once created. The general syntax of a CREATE (or use ALTER instead of CREATE) when defining constraints is as follows:

CREATE TABLE tablename
columnname datatype(size) constraintname,
columnname datatype(size) constraintname,
columnname datatype(size) constraintname,
columnname datatype(size) constraint name,
columnname datatype(size) constraint name,

Obviously, replace columnname with the name of each of your column, datatype with the types of data your column contains and constraint name with the constraint you wish to use. You have the following constraints available:

  • CHECK: Verify that values meet the defined condition
  • DEFAULT: Sets a default value for new rows in a column
  • FOREIGN KEY: Verify referential integrity of data in a table to match values in another
  • NOT NULL – Columns cannot store a NULL value (be empty)
  • PRIMARY KEY – Columns cannot store a NULL value AND values in rows must be unique
  • UNIQUE – Each row in a column must be unique

For example, the NOT NULL constraint would be defined as follows:

CREATE TABLE testingnotnull
telephonenumber int NOT NULL,

If you have an app sitting in front of a database, then use these with caution, as if SQL just terminates an operation your app might have unexpected integrity issues.

February 21st, 2016

Posted In: SQL

Tags: , , , , , , , , ,

In SQL, the DROP Statement is used to remove databases, tables, and indexes. The syntax to remove a table is:

DROP TABLE nameoftable

Wow. That’s really, really easy. I mean, you can delete craploads of data that way! It can’t be! You can also delete a database. To do so, use the DROP statement again, but this time, instead of dropping a table, let’s remove the database:

DROP DATABASE nameofdatabase

Aaaaaand, you can drop an index, which on MySQL is done using an ALTER statement, followed by TABLE, then the table name that has an index needing to drop:

ALTER TABLE nameoftable DROP INDEX nameofindex

You can also delete the data inside the table, rather than the table. This leaves the schema in tact so that you can start writing data to the database again:

TRUNCATE TABLE nameoftable

And viola, you’re deleting data like a fool!


February 11th, 2016

Posted In: SQL

Tags: , , , , , , , , ,

One of the most important aspects of searching for objects is to be able to define multiple values in a search. We looked at searching using text globbing. But the IN operator goes a step and allows you to search
The IN operator allows you to specify multiple values in a WHERE clause.

SELECT column
FROM table
WHERE column IN (value,value,...);

In this article, we’ll use the same “Customers” table from our first articles:

ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US

The following outputs all customers with a Site of “Krypted” or “JAMF”:

SELECT * FROM Customers
WHERE Site IN ('Krypted','JAMF');

When looking to use the IN operator, note that strings are quoted and separated by commas (,) inside parenthesis.

February 9th, 2016

Posted In: SQL

Tags: , , , , ,