Understanding Table Constraints in SQL: Primary Keys, Null Constraints, UNIQUE, and CHECK, Summaries of Database Management Systems (DBMS)

Download Understanding Table Constraints in SQL: Primary Keys, Null Constraints, UNIQUE, and CHECK and more Summaries Database Management Systems (DBMS) in PDF only on Docsity!

SQL DATA DEFINITION:

KEY CONSTRAINTS

CS121: Relational Databases Fall 2018 – Lecture 7

Data Definition

¨ Covered most of SQL data manipulation operations

¨ Continue exploration of SQL data definition features

¤ Specifying tables and their columns (lecture 4)

¤ Declaring views of the logical-level schema (lecture 6)

¤ Specifying constraints on individual columns, or entire tables

¤ Providing stored procedures to manipulate data

¤ Specifying security access constraints

¤ …and more!

2

Catalogs and Schemas

¨ SQL provides hierarchical grouping capabilities for

managing collections of tables

¤ Also separate namespaces for different collections of tables

¨ Standard mechanism has three levels:

¤ Catalogs

¤ Schemas

¤ Tables

¤ Each level is assigned a name

¤ Within each container, names must be unique

¨ Allows multiple applications to use the same server

¤ Even multiple instances of a particular application

4

Catalogs and Schemas (2)

¨ Every table has a full name:

¤ catalog.schema.table

¨ Database systems vary widely on implementation of

these features!

¤ Catalog functionality not covered by SQL specification

¤ Schema and table levels are specified

¤ Most DBMSes offer some kind of grouping

¨ Common behaviors:

¤ “Databases” generally correspond to catalogs

n CREATE DATABASE web_db;

¤ Schema-level grouping is usually provided

n CREATE SCHEMA blog_schema; 5

Creating Tables

¨ General form:

CREATE TABLE name ( attr1 type1 , attr2 type2 , ... );

¨ SQL provides a variety of standard column types

¤ INT , CHAR(N) , VARCHAR(N) , DATE , etc.

¤ (see Lecture 4 for more details about basic column types)

¨ Table and column names must follow specific rules

¨ Table must have a unique name within schema

¨ All columns must have unique names within the table

7

Table Constraints

¨ By default, SQL tables have no constraints

¤ Can insert multiple copies of a given row

¤ Can insert rows with NULL values in any column

¨ Can specify columns that comprise primary key

CREATE TABLE account ( account_number CHAR(10), branch_name VARCHAR(20), balance NUMERIC(12, 2), PRIMARY KEY (account_number) );

¤ No two rows can have same values for primary key

¤ A table can have only one primary key

8

Null-Value Constraints

¨ Every attribute domain contains null by default

¤ Same with SQL: every column can be set to NULL , if it isn’t

part of a primary key

¨ Often, NULL is not an acceptable value!

¤ e.g. bank accounts must always have a balance

¨ Can specify NOT NULL to exclude NULL values for

particular columns

¤ NOT NULL constraint specified in column declaration itself

¨ Stating NOT NULL for primary key columns is

unnecessary and redundant

10

Account Relation

¨ Account number is a primary key

¤ Already cannot be NULL

¨ Branch name and balance also should always be

specified

¤ Add NOT NULL constraints to those columns

¨ SQL:

CREATE TABLE account ( account_number CHAR(10) PRIMARY KEY, branch_name VARCHAR(20) NOT NULL, balance NUMERIC(12, 2) NOT NULL ); 11

UNIQUE Constraints

¨ Example: An employee relation

CREATE TABLE employee ( emp_id INT PRIMARY KEY, emp_ssn CHAR(9) NOT NULL UNIQUE, emp_name VARCHAR(40) NOT NULL, ... );

¤ Employee’s ID is the primary key

¤ All employees need a SSN, but no two employees should

have the same SSN

n Don’t forget NOT NULL constraint too!

¤ All employees should have a name, but multiple employees

might have same name

13

UNIQUE and NULL

¨ Example:

CREATE TABLE customer ( cust_name VARCHAR(30) NOT NULL, address VARCHAR(60), UNIQUE (cust_name, address) );

¨ Try inserting values:

INSERT INTO customer VALUES ('John Doe', '123 Spring Lane'); INSERT INTO customer VALUES ('John Doe', '123 Spring Lane');

¤ Second insert fails, as expected:

Duplicate entry 'John Doe- 123 Spring Lane' for key 'cust_name' 14

CHECK Constraints

¨ Often want to specify other constraints on values

¨ Can require values in a table to satisfy some

predicate, using a CHECK constraint

¤ Very effective for constraining columns’ domains, and

eliminating obviously bad inputs

¨ CHECK constraints must appear after the column

specifications

¨ In theory, can specify any expression that generates a

Boolean result

¤ This includes nested subqueries!

¤ In practice, DBMS support for CHECK constraints varies

widely, and is often quite limited

16

CHECK Constraint Examples

¨ Can constrain values in a particular column:

CREATE TABLE employee ( emp_id INT PRIMARY KEY, emp_ssn CHAR(9) NOT NULL UNIQUE, emp_name VARCHAR(40) NOT NULL, pay_rate NUMERIC(5,2) NOT NULL, CHECK (pay_rate > 5.25) );

¨ Ensures that all employees have a minimum wage

17

Another CHECK Constraint

¨ Depositor relation:

CREATE TABLE depositor ( customer_name VARCHAR(30), account_number CHAR(10), PRIMARY KEY (customer_name, account_number), CHECK (account_number IN (SELECT account_number FROM account)) );

¨ Rows in depositor table should only contain valid

account numbers!

¤ The valid account numbers appear in account table

¤ This is a referential integrity constraint

19

Another CHECK Constraint (2)

¨ Depositor relation:

CREATE TABLE depositor ( customer_name VARCHAR(30), account_number CHAR(10), PRIMARY KEY (customer_name, account_number), CHECK (account_number IN (SELECT account_number FROM account)) );

¨ When does this constraint need to be checked?

¤ When changes are made to depositor table

¤ Also when changes are made to account table!

20