SQL

1
SQL

• Keys and Constraints
• Justin Maksim

2
Key Declaration

• Key constraint defined within the CREATE TABLE
  command
• Key can be declared using either the PRIMARY KEY
  keyword or the UNIQUE keyword.
• Can have only one PRIMARY KEY can have many
  attributes declared UNIQUE

3
Methods for Key Declaration

• Method 1 append the keywords PRIMARY KEY after
  the attribute
• i.e.
• CREATE TABLE Student (
• studentID VARCHAR(8) PRIMARY KEY,
• name CHAR(30)
• )

4

• Method 2 Add element PRIMARY KEY with a
  parenthesized list of the attribute(s) forming
  the primary key
• i.e.
• CREATE TABLE Student(
• studentID VARCHAR(8),
• name CHAR(30),
• PRIMARY KEY (studentID)
• )
• Can have multiple attributes using this method
• PRIMARY KEY (studentID, name)

5

• Method 3 Use the keyword UNIQUE. Can appear
  exactly where PRIMARY KEY can appear.
• i.e.
• CREATE TABLE Student(
• studentID VARCHAR(8),
• name CHAR(30),
• UNIQUE (studentID)
• )
• OR
• CREATE TABLE Student(
• studentID VARCHAR(8) UNIQUE,
• name CHAR(30)
• )
• OR
• UNIQUE ( studentID, name)

6
Enforcing Key Constraints

• SQL system will check key constraint only when an
  insertion or update to the relation occurs
• An index on the attribute(s) declared to be keys
  is important to enforce key constraint
  efficiently.

7
Referential Integrity and Foreign Keys

• Foreign Key
• Referenced attribute(s) of second relation must
  be declared primary key for their relation
• Any value appearing in an attribute of a foreign
  key must appear in the corresponding attribute of
  the second relation

8
Declaring Foreign Key

• Method 1
• CREATE TABLE Student(
• studentID varchar(8),
• name char(30),
• record INT REFERENCES Record(record)
• )
• Method 2
• CREATE TABLE Student(
• studentID varchar(8),
• name char(30),
• record INT,
• FOREIGN KEY record REFERENCES Record(record)
• )

9
Maintaining Referential Integrity

• Default Policy
• Reject changes
• Cascade Policy
• Update changes
• Set-Null Policy
• CREATE TABLE Student(
• studentID varchar(8),
• name char(30),
• record INT REFERENCES Record(record)
• ON DELETE SET NULL
• ON UPDATE CASCADE
• )

10
Constraints On the values of Attributes

• Limits the values for attributes
• Can be expressed
• A constraint on the attribute
• A constraint on the domain

11
Not-Null Constraints

• record INT REFERENCES Record(record) NOT NULL
• Cant update to null
• Cant insert null value
• Cant use the set-null policy

12
Attribute-Based CHECK Constraints

• After defining attribute, can include the CHECK
  constraint
• i.e.
• gender CHAR(1) CHECK (gender IN (F,M)),

13
Domain Constraints

• Can create a domain and assign a constraint to a
  domain
• i.e.
• CREATE DOMAIN GenderDomain CHAR(1) CHECK (VALUE
  IN (F,M))
• Then
• gender GenderDomain,

14
References

• First Course In Database Systems, Jeffery D.
  Ullman and Jennifer Widom,1997 Prentice Hall,
  Inc.
• http//MySql.org