creating tables, setting constraints, and datatypes
DESCRIPTION
Creating Tables, Setting Constraints, and Datatypes. What is a constraint and why do we use it? What is a datatype? What does CHAR mean?. SQL commands below. CREATE TABLE DemoForClass (FirstName varchar (12) NOT NULL default 'First Name', - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/1.jpg)
# 1
Creating Tables, Setting Constraints, and Datatypes
Creating Tables, Setting Constraints, and Datatypes
What is a constraint and why do we use it?
What is a datatype?
What does CHAR mean?
CS 105 Spring 2010
![Page 2: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/2.jpg)
SQL commands below
CS 105 Spring 2010
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')
![Page 3: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/3.jpg)
What the table looks like
CS 105 Spring 2010
Note: the name Kingfishersmith was truncated
![Page 4: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/4.jpg)
Language explained Language explained
CS 105 Spring 2010
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
don’t forget the )
![Page 5: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/5.jpg)
Creating a table in SQLyog Using a Wizard
CS 105 Spring 2010
![Page 6: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/6.jpg)
CS 105 Spring 2010
Language layout
“field name" "datatype" [other constraints],
ssn CHAR(11) NOT NULL,
![Page 7: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/7.jpg)
Primary Key as shown in old clientPrimary Key as shown in old client
CS 105 Spring 2010
The Primary Key specifies the field that uniquely identifies each record in the Table
![Page 8: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/8.jpg)
Setting a Primary Key Setting a Primary Key
• A primary key is a column (field) that uniquely identifies the rest of the data in any given record.
• 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)CS 105 Spring 2010
![Page 9: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/9.jpg)
Validation, Constraints
• The database will automatically check that entered data is appropriate to the field type
• If the field is an item price, 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
CS 105 Spring 2010
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.
![Page 10: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/10.jpg)
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• It is required
• See SAMS book, lessons 17 and 22
CS 105 Spring 2010
![Page 11: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/11.jpg)
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.
VARCHAR(30) -- entry of varying length, maximum possible is 30 characters, can be less
• TEXT – a character string that does not have a fixed length
CS 105 Spring 2010
![Page 12: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/12.jpg)
More datatypes
• number(size) • unsigned (no negative numbers)
• date Date value– Timestamp ---YYYY-MM-DD
CS 105 Spring 2010
![Page 13: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/13.jpg)
Number value in a field
CS 105 Spring 2010
No negative numbers allowed, and field will not take letters!
PK is for Primary Key
Binary is for pictures, etc.
![Page 14: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/14.jpg)
Default value in SQL
CREATE TABLE tablestudents (FirstName VARCHAR (12)DEFAULT ‘First Name’ NOT NULL (etc.)
CS 105 Spring 2010
![Page 15: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/15.jpg)
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 GradeBook—if a score is missing, sometimes the total score is not computed. It all depends on what software you use.
CS 105 Spring 2010
![Page 16: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/16.jpg)
Adding Items Can Be Tricky
CS 105 Spring 2010
![Page 17: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/17.jpg)
Alter Table – Using a Query
CS 105 Spring 2010
Adding a field….
![Page 18: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/18.jpg)
Variation 1 of the Insert Statement
• If you know the order of the fields, you don’t have to specify the field names
INSERT INTO CustomersVALUES('1000000001', 'Village Toys', '200 Maple Lane',
'Detroit', 'MI', '44444', 'USA', 'John Smith', '[email protected]');
CS 105 Spring 2010
![Page 19: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/19.jpg)
Variation 2 of Insert Statement
• You don’t 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', [email protected]');
CS 105 Spring 2010
![Page 20: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/20.jpg)
Update Statement
• See page 131 and 132 of SAM’s book
• Modifies data in a table
UPDATE players SET firstname= 'fred'WHERE ssn='899-98-9989'
CS 105 Spring 2010
![Page 21: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/21.jpg)
Before running Update
CS 105 Spring 2010
![Page 22: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/22.jpg)
After running the statement
CS 105 Spring 2010
![Page 23: Creating Tables, Setting Constraints, and Datatypes](https://reader031.vdocuments.us/reader031/viewer/2022032205/56812b29550346895d8f350b/html5/thumbnails/23.jpg)
What did we learn?• What is a constraint?
• How do we change information?
• Can a Primary Key be Null?
CS 105 Spring 2010