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? CS 105 Spring 2010

2. SQL commands below 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') CS 105 Spring 2010

3. What the table looks like Note: the name Kingfishersmith was truncated CS 105 Spring 2010

4. 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 don’t forget the ) CS 105 Spring 2010

5. Creating a table in SQLyog Using a Wizard CS 105 Spring 2010

6. Language layout “field name" "datatype" [other constraints], ssn CHAR(11) NOT NULL, CS 105 Spring 2010

7. The Primary Key specifies the field that uniquely identifies each record in the Table Primary Key as shown in old client CS 105 Spring 2010

8. 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

9. Validation, Constraints 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. • 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

10. 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

11. 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,maximumpossible is 30 characters, can be less • TEXT – a character string that does nothave a fixed length CS 105 Spring 2010

12. More datatypes • number(size) • unsigned(no negative numbers) • date Date value • Timestamp ---YYYY-MM-DD CS 105 Spring 2010

13. 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! CS 105 Spring 2010

14. Default value in SQL CREATE TABLEtablestudents (FirstName VARCHAR (12) DEFAULT‘First Name’ NOT NULL (etc.) CS 105 Spring 2010

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 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

16. Adding Items Can Be Tricky CS 105 Spring 2010

17. Alter Table – Using a Query Adding a field…. CS 105 Spring 2010

18. 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 Customers VALUES ('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com'); CS 105 Spring 2010

19. 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', sales@villagetoys.com'); CS 105 Spring 2010

20. 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

21. Before running Update CS 105 Spring 2010

22. After running the statement CS 105 Spring 2010

23. What did we learn? • What is a constraint? • How do we change information? • Can a Primary Key be Null? CS 105 Spring 2010