Keys and Constraints - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

These lecture slide are very easy to understand and very helpful to built a concept about the foundation of computers and Database Design.The key points in these slide are:Keys and Constraints, Key Constraint, Methods for Key Declaration, Parenthesized List, Enforcing Key Constraints, Referential Integrity, Foreign Keys, Default Policy, Cascade Policy, Set-Null Policy, Values for Attributes

Typology: Slides

2012/2013

Uploaded on 04/27/2013

arunima
arunima 🇮🇳

3

(2)

99 documents

1 / 13

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
SQL
Keys and Constraints
Docsity.com
pf3
pf4
pf5
pf8
pf9
pfa
pfd

Partial preview of the text

Download Keys and Constraints - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

SQL

Keys and Constraints

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

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

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#) );

Constraints On the values of Attributes

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

Not-Null Constraints

  • record# INT REFERENCES Record(record#) NOT NULL
    • Can’t update to null
    • Can’t insert null value
    • Can’t use the set-null policy

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,