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

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,