Using Relational Databases and SQL

1. Steven Emory Department of Computer Science California State University, Los Angeles Using Relational Databases and SQL Lecture 10: Data Definition Language

2. Miscellany • For today’s topic, use the Lyrics database

3. Topics for Today • Data Types (Appendix) • Creating Tables (Pages 188 – 190)‏ • Deleting Tables (Page 190)‏ • Adding Columns (Pages 190 – 191)‏ • Deleting Columns (Page 192)‏ • Editing Columns (Pages 191 – 192)‏ • Identity Columns (Pages 193 – 195)‏ • Constraints (Pages 196 – 207)‏

4. Data Types • TINYINT(width): 1 byte • SMALLINT(width): 2 bytes • MEDIUMINT(width): 3 bytes • INT(width): 4 bytes • BIGINT(width): 8 bytes • FLOAT(n, d): 4 bytes • REAL(n, d): 8 bytes • DATE: 3 bytes • CHAR(n) and VARCHAR(n): Variable bytes

5. Creating Tables • Syntax: • CREATETABLE ( fieldname datatype NULL | NOTNULL, fieldname datatype NULL | NOTNULL, ... fieldname datatype NULL | NOTNULL); • Each row specifies a column definition • Use NULL or NOT NULL to specify whether or not a column can contain NULL values

6. CREATE TABLE Example • Example: • CREATETABLE Musician ( Musician VARCHAR(32) NOT NULL);INSERT INTO Musician VALUES(‘Guitarist’);INSERT INTO Musician VALUES(‘Pianist’);INSERT INTO Musician VALUES(‘Drummer’);INSERT INTO Musician VALUES(‘Vocalist’);INSERT INTO Musician VALUES(‘Bassist’);

7. Dropping Tables • Syntax: • DROPTABLE [IFEXISTS] tablename; • Example: • DROPTABLE Artists; • NOTE: BEGIN and ROLLBACK do not work with DROPTABLE

8. Adding Columns • Syntaxes: • ALTERTABLE tablenameADD COLUMN column_definition; • ALTERTABLE tablenameADDCOLUMN column_definitionFIRST; • ALTERTABLE tablenameADDCOLUMN column_definitionAFTER column_name;

9. Examples • Examples: • ALTERTABLE MusicianADDCOLUMN MusicianID INTNOT NULLFIRST; • ALTER TABLE MusicianADD COLUMN Type VARCHAR(16) NULL;INSERT INTO MusicianVALUES(‘1’, ‘Guitarist’, ‘Electric’);

10. Dropping Columns • Syntax: • ALTERTABLE tablenameDROPCOLUMN column_name; • Example: • ALTER TABLE MusicianDROP COLUMN Type;

11. Editing Columns • MODIFY let’s you change a column’s data definition (without renaming it) • CHANGE does the same thing as MODIFY, but you can also specify a new column name • Syntax: • ALTERTABLE tablenameMODIFY fieldname column_definition; • ALTERTABLE tablenameCHANGE oldname newname column_definition

12. Examples • Examples: • -- Let’s rename the Musician column to Instrument.ALTER TABLE MusicianCHANGE Musician Instrument VARCHAR(32) NOT NULL; • -- Let’s modify the Instrument column string length to make it really short!ALTER TABLE MusicianMODIFY Instrument VARCHAR(4) NOT NULL;

13. Constraints • A constraint is any kind of restriction placed on the data inserted into a table • Primary Key Constraints: Enforces uniqueness of data. • Foreign Key Constraints: A value must refer to an existing piece of data. • Default Constraints: Data not specifically inserted will take on default values. • Unique Constraints: Forces data (other than the primary key) to be unique.

14. Primary Key Constraints • CREATE TABLE Syntax: • Can add the keyword PRIMARY key at end of column definition • For more than one column you can use the CONSTRAINT keyword. • ALTER TABLE Syntax: • ALTERTABLE tablenameADDCONSTRAINT constraint_namePRIMARYKEY (field1, field2, ...);

15. Examples • Examples: • CREATETABLE tablename( field1 INTNOTNULL, field2 INTNOT NULL, CONSTRAINT constraint_name PRIMARY KEY(field1)); • ALTER TABLE TitlesADDCONSTRAINT fk_titlesPRIMARY KEY(TitleID);

16. Foreign Key Constraints • Sometimes called integrity constraints • CREATE TABLE Syntax: • CONSTRAINT constraint_nameFOREIGNKEY(field1, field2, ...)REFERENCES parent_table(field1, field2, ...)‏ • ALTER TABLE Syntax: • ALTERTABLE tablenameADDCONSTRAINT constraint_nameFOREIGNKEY (field1, field2, ...)REFERENCES parent_table(field1, field2, ...);

17. Examples • Example: • -- To run the following example, make sure to make the Genre attribute the primary key in the Genre table or else this won’t work! A foreign key must reference a primary key!ALTERTABLE TitlesADDCONSTRAINT fk_titles_genreFOREIGNKEY (Genre)REFERENCES Genre (Genre);

18. Unique Constraints • Use to make one or more columns (other than the primary key) contain only unique values • CREATE TABLE Syntax: • Just add the UNIQUE keyword at the end. • ALTER TABLE Syntax: • ALTERTABLE tablenameADDCONSTRAINT constraint_nameUNIQUE (fieldname1, fieldname2, ...);

19. Examples • Examples: • CREATE TABLE login( user_id INT NOT NULL PRIMARY KEY, username VARCHAR(32) NOT NULL UNIQUE, password VARCHAR(32) NOT NULL); • ALTER TABLE loginADD CONSTRAINT uq_usernameUNIQUE(username); • INSERTINTO login(1, ‘semory’, ‘mypassword’); • INSERTINTO login(2, ‘semory’, ‘anotherpass’);

20. Default Constraints • Using CREATE TABLE: • After specifying the datatype in the column definition, use the following:DEFAULT value • Using ALTER TABLE: • ALTERTABLE tablenameALTER fieldnameSETDEFAULT value;

21. Examples • Examples: • DROP TABLE login; • CREATE TABLE login( user_id INT NOT NULL PRIMARY KEY, username VARCHAR(32) NOT NULL UNIQUE, password VARCHAR(32) DEFAULT ‘0xk2739’NOT NULL); • ALTERTABLE SalesPeopleALTER Base SETDEFAULT 100.00;

22. Dropping Constraints • Do not read the book on this! The book’s code will not work! Use the following syntax: • Syntax: • -- To drop a primary key...ALTERTABLE tablenameDROPPRIMARY KEY; • -- To drop a foreign key...ALTER TABLE tablenameDROP FOREIGN KEY keyname; • -- To drop any other key...ALTER TABLE tablename DROP KEY keyname;

23. Examples • Examples: • -- Drop the fk_titles_genre foreign key in the Titles table.ALTER TABLE TitlesDROP FOREIGN KEY fk_titles_genre; • -- Drop the pk_genre primary key in the Genre table.ALTER TABLE GenreDROP PRIMARY KEY pk_genre; • -- Drop the uq_username unique key in the login table.ALTER TABLE login DROP KEY uq_username;

24. Identity Columns • Purpose is to auto-generate primary key values • MySQL uses the non-standard keyword AUTO_INCREMENT and you can only define it on the primary key • SQL standard uses GENERATE keyword • Syntax: • fieldname datatype NOT NULLAUTO_INCREMENT

25. Identity Column Example • Example: • DELETE FROM Musician; • ALTER TABLE MusicianMODIFY InstrumentVARCHAR(32) NOT NULL AUTO_INCREMENT; • INSERTINTO Musician VALUES(0, ‘Guitarist’);INSERTINTO Musician VALUES(0, ‘Pianist’);INSERTINTO Musician VALUES(0, ‘Vocalist’);INSERTINTO Musician VALUES(0, ‘Bassist’);INSERTINTO Musician VALUES(0, ‘Drummer’);