That’s right, check. We can set limits on things now. Say we want ages bigger than 0 so that they cant fool the system by typing a negative value!
1 2 3 4 5 6 | CREATE TABLE People ( ID INT NOT NULL, FirstName VARCHAR(255), Age INT, CHECK (Age>0) ) |
And as usual you can add or remove as so:
ALTER TABLE People ADD CONSTRAINT checkAGE CHECK (Age>0)
And:
1 | ALTER TABLE People DROP CHECK checkAGE |
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Lets have a look:
People table:
| ID | First Name | Last Name | Age | Country |
|---|---|---|---|---|
| 1 | Simon | Dangers | 23 | England |
| 2 | Simon | Fridge | 25 | England |
| 3 | James | Fridge | 46 | America |
Orders table:
| ID | Number | Person |
|---|---|---|
| 1 | 01456 | 2 |
| 2 | 01457 | 2 |
| 3 | 01458 | 3 |
Note that the “Person” column in the “Orders” table points to the “ID” column in the “People” table.
The “ID” column in the “People” table is the PRIMARY KEY in the “People” table.
The “ID” column in the “Orders” table is a FOREIGN KEY in the “Orders” table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
1 2 3 4 5 6 7 | CREATE TABLE Orders ( ID INT NOT NULL, NUMBER INT NOT NULL, Person INT, PRIMARY KEY (ID), FOREIGN KEY (Person) REFERENCES People(ID) ) |
And the two datbases are linked!
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.
So…
1 2 3 4 5 6 | CREATE TABLE People ( ID INT NOT NULL, FirstName VARCHAR(255) NOT NUL, Country VARCHAR(255), PRIMARY KEY (ID) ) |
And adding and deleting is actually just as easy as before just replace UNIQUE with PRIMARY KEY
The UNIQUE constraint uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
So…
1 2 3 4 5 6 | CREATE TABLE People ( ID INT NOT NULL, FirstName VARCHAR(255) NOT NUL, Country VARCHAR(255), UNIQUE (ID) ) |
Means that ID is unique and NO two values can be the same.
And if we want a combination of two fields:
1 2 3 4 5 6 | CREATE TABLE People ( ID INT NOT NULL, FirstName VARCHAR(255) NOT NULL, Country VARCHAR(255), CONSTRAINT constraintIDNAME UNIQUE (ID,FirstName) ) |
This will make the constraint constraintIDNAME relating to the unique values ini ID and FirstName, here you could have a lot of ID’s all the same and a lot of FirstNames all the same but only 1 combination of an ID and FirstName.
In order to add a constraint after the creation you can use:
1 | ALTER TABLE People ADD CONSTRAINT constraintNAME UNIQUE (ID) |
And the following to get rid of it:
1 | ALTER TABLE People DROP INDEX constraintNAME |
This simply requires a field to have a value:
1 2 3 4 5 | CREATE TABLE People ( ID INT NOT NULL, FirstName VARCHAR(255) NOT NULL, Country VARCHAR(255) ) |
The above code means that the table People cannot have a record inserted or updated if it means ID or FirstName do not have any content. This can be useful for user account information and may act as a failsafe for requiring input.
In SQL, constraints limit what data can go into the field and are usually added when creating the table:
And well be looking at each individually in the upcoming days.
This is a very useful tool, however if you wish to create a database using SQL you can:
1 | CREATE DATABASE name |
Line 1: Where name is the name of the database to be created.
That’s what Union does anyway.
Table1
| ID | Name |
|---|---|
| 1 | PersonA |
| 2 | PersonB |
| 3 | PersonC |
| 4 | PersonD |
Table2
| ID | Name |
|---|---|
| 1 | PersonC |
| 2 | PersonD |
| 3 | PersonE |
| 4 | PersonF |
And we just use:
1 | SELECT Name FROM Table1 UNION ALL SELECT Name FROM Table2 |
And you get:
PersonA
PersonB
PersonC
PersonD
PersonE
PersonF
It only selects Distinct values, use UNION ALL to ignore this and return all the data:
1 | SELECT Name FROM Table1 UNION SELECT Name FROM Table2 |
PersonA
PersonB
PersonC
PersonD
PersonC
PersonD
PersonE
PersonF
Sometimes it’s important to join tables to avoid having duplicate values. Instead we pass unique IDs to a tables row and then reference it again from another table.
We can read the data using joins.
JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables
1 2 | SELECT * FROM People INNER JOIN Countries ON People.Country=Countries.Name |
And that’s the standard format, just use it for the different scenarios 
Well one of my weird friends has decided he needed in AS2:
The following code is the AS2 version, you should be able to work out what does what and if you need anything just comment below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 | //-----Create visual placeholders----- //New MovieClip lineOfSight = _root.createEmptyMovieClip("lineOfSight",1) //-----Define key press automation----- var keys:Array = new Array() var key:Number = 0 var minKeys:Number = 37 var maxKeys:Number = 40 for(key = minKeys; key < maxKeys; key++){ keys[key] = false } //-----Variable definition----- //Movement speed var speed:Number = 4 //Line / sight distance var sightDistance:Number = 100 //Line / sight spread var sightSpread:Number = 60 //Line / sight accuracy (How many lines) var sightAccuracy:Number = 75 //Line / sight hit accuracy (How many 'part' lines) var sightHitAccuracy:Number = 50 _root.onEnterFrame = function(){ move_hero() collision_hero() lineOfSight.clear() create_Sight() } function move_hero(){ if(Key.isDown(Key.LEFT)){ //Left hero._x -= speed } if(Key.isDown(Key.UP)){ //Up hero._y -= speed } if(Key.isDown(Key.RIGHT)){ //Right hero._x += speed } if(Key.isDown(Key.DOWN)){ //Down hero._y += speed } } function collision_hero(){ radiusX = hero._width/2 radiusY = hero._height/2 while(wall.hitTest(hero._x - radiusX, hero._y, true)){ hero._x ++ } while(wall.hitTest(hero._x + radiusX, hero._y, true)){ hero._x -- } while(wall.hitTest(hero._x, hero._y - radiusY, true)){ hero._y ++ } while(wall.hitTest(hero._x, hero._y + radiusY, true)){ hero._y -- } } function create_Sight(){ //Read global variables for mouse position and convert into local variable radiusX = hero._width/2 radiusY = hero._height/2 xMouse = _root._xmouse yMouse = _root._ymouse xDif = xMouse - hero._x yDif = yMouse - hero._y sightAngleBaseRadians = Math.atan2(yDif, xDif) sightAngleBase = 360 * sightAngleBaseRadians / (2 * Math.PI) lineOfSight.lineStyle(3,0xff0000) lineOfSight.beginFill(0xff0000) startX = hero._x + Math.cos(sightAngleBase * Math.PI / 180) * radiusX startY = hero._y - Math.sin(sightAngleBase * Math.PI / 180) * -radiusX //Draw the line lineOfSight.moveTo(startX, startY) for (var sightAngle:Number = 0; sightAngle <= sightSpread; sightAngle += (sightSpread/sightAccuracy)){ //Find individual line angle var sightAngleIndividual = (sightAngleBase-(sightSpread/2) + sightAngle)*(Math.PI/180) for (var sightLength:Number = 0; sightLength < sightDistance; sightLength += (sightDistance/sightHitAccuracy)){ //Find end of line endX = hero._x + Math.cos(sightAngleIndividual) * sightLength endY = hero._y + Math.sin(sightAngleIndividual) * sightLength if(wall.hitTest(endX, endY, true)){ break } } lineOfSight.lineTo(endX, endY) } lineOfSight.lineTo(startX, startY) floor.setMask(lineOfSight) } |
And, thanks to Timothy John McLennan
Written by