Using Relational Databases and SQL

1
Using Relational Databases and SQL
Lecture 10 Data Definition Language

• Steven Emory
• Department of Computer Science
• California State University, Los Angeles

2
Miscellany

• For todays 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
• CREATE TABLE ( fieldname datatype NULL NOT
  NULL, fieldname datatype NULL NOT NULL,
  ... fieldname datatype NULL NOT NULL)
• 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
• CREATE TABLE 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
• DROP TABLE IF EXISTS tablename
• Example
• DROP TABLE Artists
• NOTE BEGIN and ROLLBACK do not work with DROP
  TABLE

8
Adding Columns

• Syntaxes
• ALTER TABLE tablenameADD COLUMN
  column_definition
• ALTER TABLE tablenameADD COLUMN
  column_definitionFIRST
• ALTER TABLE tablenameADD COLUMN
  column_definitionAFTER column_name

9
Examples

• Examples
• ALTER TABLE MusicianADD COLUMN MusicianID INT
  NOT NULLFIRST
• ALTER TABLE MusicianADD COLUMN Type VARCHAR(16)
  NULLINSERT INTO MusicianVALUES(1,
  Guitarist, Electric)

10
Dropping Columns

• Syntax
• ALTER TABLE tablenameDROP COLUMN column_name
• Example
• ALTER TABLE MusicianDROP COLUMN Type

11
Editing Columns

• MODIFY lets you change a columns data
  definition (without renaming it)
• CHANGE does the same thing as MODIFY, but you can
  also specify a new column name
• Syntax
• ALTER TABLE tablenameMODIFY fieldname
  column_definition
• ALTER TABLE tablenameCHANGE oldname newname
  column_definition

12
Examples

• Examples
• -- Lets rename the Musician column to
  Instrument.ALTER TABLE MusicianCHANGE Musician
  Instrument VARCHAR(32) NOT NULL
• -- Lets 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
• ALTER TABLE tablenameADD CONSTRAINT
  constraint_namePRIMARY KEY (field1, field2, ...)

15
Examples

• Examples
• CREATE TABLE tablename( field1 INT NOT NULL,
  field2 INT NOT NULL, CONSTRAINT
  constraint_name PRIMARY KEY(field1))
• ALTER TABLE TitlesADD CONSTRAINT
  fk_titlesPRIMARY KEY(TitleID)

16
Foreign Key Constraints

• Sometimes called integrity constraints
• CREATE TABLE Syntax
• CONSTRAINT constraint_nameFOREIGN KEY(field1,
  field2, ...)REFERENCES parent_table(field1,
  field2, ...)?
• ALTER TABLE Syntax
• ALTER TABLE tablenameADD CONSTRAINT
  constraint_nameFOREIGN KEY (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 wont work! A foreign
  key must reference a primary key!ALTER TABLE
  TitlesADD CONSTRAINT fk_titles_genreFOREIGN KEY
  (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
• ALTER TABLE tablenameADD CONSTRAINT
  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)
• INSERT INTO login(1, semory, mypassword)
• INSERT INTO login(2, semory, anotherpass)

20
Default Constraints

• Using CREATE TABLE
• After specifying the datatype in the column
  definition, use the followingDEFAULT value
• Using ALTER TABLE
• ALTER TABLE tablenameALTER fieldnameSET DEFAULT
  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)
• ALTER TABLE SalesPeopleALTER Base SET DEFAULT
  100.00

22
Dropping Constraints

• Do not read the book on this! The books code
  will not work! Use the following syntax
• Syntax
• -- To drop a primary key...ALTER TABLE
  tablenameDROP PRIMARY 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 NULL AUTO_INCREMENT

25
Identity Column Example

• Example
• DELETE FROM Musician
• ALTER TABLE MusicianMODIFY InstrumentVARCHAR(32)
  NOT NULL AUTO_INCREMENT
• INSERT INTO Musician VALUES(0, Guitarist)INSER
  T INTO Musician VALUES(0, Pianist)INSERT INTO
  Musician VALUES(0, Vocalist)INSERT INTO
  Musician VALUES(0, Bassist)INSERT INTO
  Musician VALUES(0, Drummer)