Creating Table' Additional Parameters

1
Creating Table. Additional Parameters

• NULL values
• CREATE TABLE students (ssn CHAR(9) NOT NULL,
  fname VARCHAR(20), lname VARCHAR(20), major
  CHAR(3) NOT NULL)
• Effect - System will not allow NULL values in the
  specified fields.
• CREATE TABLE students (ssn CHAR(9) NOT NULL,
  fname VARCHAR(20) NULL, lname VARCHAR(20) NULL,
  major CHAR(3) NOT NULL)
• Effect System does not complain if the
  specified fields are NULL.

2
Creating Table. Integrity Constraints

• Default values
• the values which system will assign to a field if
  not specified by user.
• CREATE TABLE students (ssn CHAR(9) NOT NULL,
  fname VARCHAR(20), lname VARCHAR(20), major
  CHAR(3) NOT NULL DEFAULT IST)
• CREATE TABLE students (ssn CHAR(9) NOT NULL
  DEFAULT 000000000, fname VARCHAR(20), lname
  VARCHAR(20), major CHAR(3) NOT NULL DEFAULT
  IST)
• Type DESCRIBE STUDENTS to see the changed
  parameters.

3
Creating Table. Integrity Constraints

• Primary Key
• Its value should be different in each row.
• CREATE TABLE students (ssn CHAR(9) NOT NULL,
  fname VARCHAR(20), lname VARCHAR(20), major
  CHAR(3) NOT NULL DEFAULT IST, PRIMARY KEY
  (ssn))
• CREATE TABLE students (ssn CHAR(9) NOT NULL,
  fname VARCHAR(20), lname VARCHAR(20), major
  CHAR(3) NOT NULL DEFAULT IST, PRIMARY KEY
  (ssn, lname))
• CREATE TABLE students (ssn CHAR(9) NOT NULL,
  fname VARCHAR(20), lname VARCHAR(20), major
  CHAR(3) NOT NULL DEFAULT IST, CONSTRAINT
  prim_key PRIMARY KEY (ssn))

4
Creating Table. Integrity Constraints

• Uniqueness Candidate key.
• sometimes some fields should be unique to records
  even if they dont form part of the primary key.
• Consider the following
• CREATE TABLE students
• (ssn CHAR(9) NOT NULL,
• fname VARCHAR(20), lname VARCHAR(20),
• credit_card_num VARCHAR(20), major CHAR(3)
  NOT NULL DEFAULT IST, CONSTRAINT prim_key
  PRIMARY KEY (ssn),
• CONSTRAINT cc_uni UNIQUE(credit_card_num))
• Schema-
• students(ssn, fname, lname, credit_card_num,
  major)

5
Creating Table. Integrity Constraints

• Foreign Key and Referential Constraints
• Relations
• students(ssn, fname, lname, credit_card_num,
  major)
• musicians(ssn, band, instrument) ssn is foreign
  key.
• CREATE TABLE musicians
• (ssn CHAR(9) NOT NULL,
• band VARCHAR(20) NOT NULL, instrument
  VARCHAR(20),
• CONSTRAINT prim_key PRIMARY KEY(band),
• CONSTRAINT foreign_k FOERIGN KEY(ssn)
• REFERENCES students(ssn))

6
Creating Table. Integrity Constraints

• Check Clause
• To specify conditions for data to be inserted
  into a column.
• CREATE TABLE students
• (ssn CHAR(9) NOT NULL,
• fname VARCHAR(20), lname VARCHAR(20),
• credit_card_num VARCHAR(20), major CHAR(3) NOT
  NULL DEFAULT IST, CONSTRAINT prim_key PRIMARY
  KEY (ssn),
• CONSTRAINT cc_uni UNIQUE(credit_card_num)
• CONSTRAINT check_major CHECK(major ltgt CSE))

7
Altering Table Schema

• Altering schema is different from changing a
  value.
• Adding a column
• ALTER TABLE students
• ADD COLUMN telephone_num CHAR(10) NOT NULL
• Deleting a column
• ALTER TABLE students
• DROP COLUMN telephone_num
• Modifying a column
• ALTER TABLE students
• MODIFY COLUMN credit_card_num VARCHAR(10) NOT
  NULL

8
Altering Table Schema

• Changing Integrity Constraints
• ALTER TABLE students
• ADD CONSTRAINT ltgt
• ALTER TABLE students
• DROP CONSTRAINT ltgt

9
Deleting and Updating

• Deleting rows
• DELETE FROM students WHERE lname like B
• Updating
• UPDATE musicians
• SET band Music Mania
• WHERE ssn LIKE 6333
• Make sure to preserve integrity when updating
  otherwise there will be errors.

10
Exercise

• Create tables according to the following schemas