Creating Tables, Setting Constraints, and Datatypes

1
Creating Tables, Setting Constraints, and
Datatypes

• What is a constraint and why do we use it?
• What is a datatype?
• What does CHAR mean?

p. 91 Course Guide
2
What can this mean?
3
Language explained

• CREATE TABLE tablename
• (column1 datatype,
• column2 datatype,
• column3 datatype )
• create table means create a table
• ( begin defining fields with a (
• ) end of fields is signaled by )
• end of statement

Add the ) mark!
4
Validation, Constraints

• The database automatically checks that entered
  data is appropriate to the field type
• If the field is a phone number, you can create a
  constraint that input is to be numbers only and
  no letters are allowed.
• We will not cover "Input Masks" this semester

NOT NULL means that the column must have a value
in each row. If NULL is used, that column may
be left empty in a given row.
5
What is a constraint?

• A constraint is basically a rule associated with
  a column that the data entered into that column
  must follow.
• "Not Null" -- a column can't be left blank
• VARCHAR(30) -- entry of varying length, maximum
  possible is 30 characters, can be less
• See SAMS book, lessons 17 and 22

6
Design View in SQLyog
Unsigned means than no sign is accepted in front
of a number. That means it wont accept 2, for
example.

• How do we determine what to accept in each
  field?
• These are the table properties

SAMS Lesson 17
7
Doing nearly the same thing in SQLyog
DemoForClass CREATE TABLE 'DemoForClass' (
FirstName'
varchar(12) NOT NULL default 'First Name',
'LastName' varchar(12) NOT NULL default 'Last
Name', 'Year' year(4) NOT NULL default
'2006', 'SSN' varchar(11)
NOT NULL default '000-00-0000')
8
What the table looks like
Note the name Kingfishersmith was truncated
9
Creating a table in SQLyog
10
Number value in a field
PK is for Primary Key
Binary is for pictures, etc.
No negative numbers allowed, and field will not
take letters!
11
Starting to make sense?
Table name
CREATE TABLE employee (ssn
CHAR(11) NOT NULL, first VARCHAR(15),
last VARCHAR(20) NOT
NULL, age INT(3),
address VARCHAR(30),
city VARCHAR(20),
state CHAR(2))
Field names
Datatype
Note MySQL may change CHAR to VARCHAR in this
situationsee the MySQL Manual.
12
Language layout
"column1" "datatype" constraint,
ssn CHAR(11) NOT NULL,
13
Default value in SQL

• CREATE TABLE tablestudents
• (FirstName VARCHAR (12)
• DEFAULT First Name NOT NULL (etc.)

14
Adding Items Can Be Tricky
15
NULL Values and calculations

• If you add a value to another value that is NULL,
  the answer that MySQL gives you is NULL! The same
  thing sometimes happens in our Compass
  GradeBookif a score is missing, sometimes the
  total score is not computed. It all depends on
  what software you use.

16
Alter Table Using a Query
17
Variation 1 of the Insert Statement

• If you know the order of the fields, you dont
  have to specify the field names
• INSERT INTO Customers
• VALUES
• ('1000000001', 'Village Toys', '200 Maple Lane',
  'Detroit', 'MI', '44444', 'USA', 'John Smith',
  'sales_at_villagetoys.com')

18
Variation 2 of Insert Statement

• You dont have to insert a value for every field,
  unless it is a required (NOT NULL, PRIMARY KEY)
  field
• INSERT INTO Customers
• (cust_id, cust_name, cust_email)
• VALUES
• ('1000000001', 'Village Toys', 'sales_at_villagetoys.
  com')

19
Update Statement

• See page 131 and 132 of SAMs book
• Modifies data in a table
• UPDATE players
• SET firstname 'fred'
• WHERE ssn'899-98-9989'

20
Before running Update
21
After running the statement
22
Primary Key as shown in old client

• The Primary Key specifies the field that uniquely
  identifies each record in the Table

23
Setting a Primary Key

• A primary key is a column (field) that uniquely
  identifies the rest of the data in any given row.
  
• More than one field can be combined to establish
  a primary key (e.g., Deanne Smith rather than
  Smith)
• (MySQL may give you trouble, but trust us)

24
Datatypes

• Datatypes specify what the type of data can be
  for that particular field.
• A field called "Last_Name should have a
  "VARCHAR" (variable-length character) datatype.
• A field called SSN should have a char
  datatype--that would constrain the size to
  exactly 9 or 11 characters.

25
Popular datatypes see Appendix D

• char(size) Fixed-length character string. Size is
  specified in parenthesis. Max 255 bytes.
• varchar(size) Variable-length character string.
  Max size is specified in parenthesis.
• TEXT a character string that does not have a
  fixed length

26
More datatypes

• number(size)
• unsigned (no negative numbers)
• Tinyint (integer)
• This is very complicateddo not worry about it
  for our course. For details see
  http//www.mysql.com/doc/
• date Date value
• Timestamp ---YYYY-MM-DD