[Oracle-Sql] C.5 Constraints

📋 This is my note-taking from what I learned in the class “Introduction To Database Concept”

Constraints

• Rules used to enforce business rules, practices, and policies
• Rules used to ensure accuracy and integrity of data

Constraint Types

Constraints	Description
Primary Key	Determines which column(s) uniquely identifies each record. The primary key can’t be NULL, and the data values must be unique.
Foreign Key	In a one-to-many or parent-child relationship, the constraint is added to the “many” table. The constraint ensures that if a value is entered in a specific column, it must already exist in the “one” table, or the record isn’t added.
Unique	Ensures that all data values stored in a specified column are unique. The UNIQUE constraint differs from the PRIMARY KEY constraint in that it allows NULL values.
Check	Ensures that a specified condition is true before the data value is added to a table. For example, an order’s ship data can’t be earlier than its order date.
Not Null	Ensures that a specified column can’t contain a NULL value. The NOT NULL constraint can be created only with the column-level approach to table creation.

PK(Primary Key) - Characteristics

• Unique
• Not null
• Only one primary key in a table
• Value never change(Because it is unique)
• A primary key cannot exceed 16 columns and a total key length of 900 bytes
• API key is primary key
• Consistent

FK(Foreign Key)

• Can have null values
• Can have duplicated values
• Create relationships

Unique

• Can be null, but Cannot be duplicated

Creating Constraints

• Use the optional CONSTRAINT keyword during creation to assign a name
• Let the server name the constraint using the default format SYS_Cn
• Informative names can assist in debugging

When:

• During table creation
• After table creation, by modifying the existing table

How:

• Column level approach
• Table level approach

Creating Constraints at the Column Level

• If a constraint is being created at the column level, the constraint applies to the column specified
• Column constraints are constraints attached to a single column
• They are used to determine whether a proposed value for a column is valid or not
• Column constraints are great for expressing requirements that are limited to a single field

For instance, we could model the age restriction in a person table by adding a constraint after the column name and data type:

CREATE TABLE PERSON
(
  AGE INT CHECK (AGE >= 0)
);

Creating Constraints at the Table Level

• Approach can be used to create any constraint type except NOT NULL
• Required if constraint is based on multiple columns
• Table constraints can express almost any restrictions that a column constraint can, but can additionally express restrictions that involve more than one column
• Instead of being attached to a specific column, table constraints are defined as a separate component of the table and can reference any of the table’s columns

The column constraint we saw earlier could be expressed as a table constraint like this:

CREATE TABLE PERSON
(
  AGE INT,
  CHECK (AGE >= 0)
);

CREATE TABLE BORROW
(
  ACCOUNT_ID INT,
  ACCEPT BOOLEAN,
  CHECK (ACCOUNT_ID IS NOT NULL AND ACCEPT = TRUE)
);

📋 Column and Table Constraints

https://www.prisma.io/dataguide/mysql/column-and-table-constraints

☞ Table Level

Parent		Child
Customer		Orders
PK Customer#	Referencing Customer#	PK Order id
		FK Customer#

First create parent table and then create child table → If you try to create child table first, you will get errors!

EX) In student table, Student#, Address, Email is primary key

Example of Constraints

SELECT * FROM orderitems;
SELECT * FROM user_constraints WHERE table_name='orderitems';

-- How many constraints are in orderitems table
SELECT COUNT(*) FROM user_constraints WHERE table_name='orderitems';

CREATE TABLE cars
  (
    VIN# NUMBER(10) PRIMARY KEY,
    MODEL VARCHAR2(10),
    PRICE NUMBER(10),
  );

SELECT * FROM user_constraints WHERE table_name='cars';

ALTER TABLE cars ADD CONSTRAINT cars_model_uk UNIQUE(MODEL); --> cars_model_uk is user-defined constraint!!

-- Add a color column and add a unique constraint
ALTER TABLE cars ADD (COLOR VARCHAR2(10));
ALTER TABLE cars ADD CONSTRAINT cars_color_uk UNIQUE(COLOR);

INSERT INTO cars VALUES(11111, 'BMW', 9000, 'WHITE');
INSERT INTO cars VALUES(22222, 'LEXUS', 9000, 'PEARL_WHITE');

SELECT * FROM cars;

-- Add a constraint for price column -> Price > 20000 is the condition
-- ALTER TABLE table_name CONSTRAINT constraint_name CHECK (check_condition);
ALTER TABLE cars ADD CONSTRAINT cars_price_uk CHECK (price > 20000);

-- Insert values to check the check constraints
INSERT INTO cars VALUES(44444, 'BENZ', 21000, 'BLUE'); --> 20000 for price is not allowed
SELECT * FROM cars;

Enforcement of Constraints

• All constraints are enforced at the table level
• If a data value violates a constraint, the entire row is rejected

Adding Constraints to Existing Tables

• Constraints are added to an existing table with the ALTER TABLE command
• Add a NOT NULL constraint using MODIFY clause
• All other constraints are added using ADD clause

Using the PRIMARY KEY Constraint

• Ensures that columns do not contain duplicate or NULL values
• Only one per table is allowed

ALTER TABLE STUDENT ADD [CONSTRAINT STUDENT_PK] PRIMARY KEY (SID);

Constraint Checked with Data Input

INSERT INTO CUSTOMERS (CUSTOMER#, LNAME, FNAME, REGION) VALUES (1020, 'PADDY', 'JACK', 'NE');

PRIMARY KEY Constraint for Composite Key

ALTER TABLE ORDERITEMS ADD CONSTRAINT ORDERITEMS_ORDER#ITEM#_PK PRIMARY KEY (ORDER#, ITEM#);

Using the FOREIGN KEY Constraint

• Requires a value to exist in the referenced column of another table
• NULL values are allowed
• Enforces referential integrity
• Maps to the PRIMARY KEY in parent table

ALTER TABLE ORDERS ADD CONSTRAINT ORDERS_CUSTOMER#_FK FOREIGN KEY (CUSTOMER#) REFERENCES CUSTOMERS (CUSTOMER#);

Deletion of FOREIGN KEY Values

• You cannot delete a value in a parent table referenced by a row in a child table
• Use ON DELETE CASCADE keywords when creating FOREIGN KEY constraint – it automatically deletes a parent row when the row in a child table is deleted

Using the UNIQUE Constraint

• No duplicates are allowed in the referenced column
• NULL values are permitted

ALTER TABLE BOOKS ADD CONSTRAINT BOOKS_TITLE_UK UNIQUE (TITLE);

Using the CHECK Constraint

• Updates and additions must meet specified condition

ALTER TABLE ORDERS ADD CONSTRAINT ORDERS_SHIPDATE_CK CHECK (ORDERDATE <= SHIPDATE);

Using the NOT NULL Constraint

• The NOT NULL constraint is a special CHECK constraint with IS NOT NULL condition
• Can only be created at column level
• Included in output of DESCRIBE command
• Can only be added to an existing table using ALTER TABLE… MODIFY command

ALTER TABLE ORDERS MODIFY (CUSTOMER# CONSTRAINT ORDERS_CUSTOMER#_NN NOT NULL);

Including Constraints during Table Creation

Including Constraints during Table Creation

Column Level

Include in column definition:

CREATE TABLE DEPT
(
  DEPTID NUMBER(2) CONSTRAINT DEPT_DEPTID_PK PRIMARY KEY,
  DNAME VARCHAR2(20) NOT NULL CONSTRAINT DEPT_DNAME_UK UNIQUE,
  FAX VARCHAR2(12)
);

Table Level

Include at end of column list:

CREATE TABLE EQUIP
(
  EQUIPID NUMBER(3),
  EDESC VARCHAR2(30),
  PURCHDATE DATE,
  RATING CHAR(1),
  DEPTID NUMBER(2) NOT NULL,
  ETYPEID NUMBER(2),
    CONSTRAINT EQUIP_EQUIPID_PK PRIMARY KEY (EQUIPID),
    CONSTRAINT EQUIP_DEPTID_FK FOREIGN KEY (DEPTID) REFERENCES DEPT (DEPTID),
    CONSTRAINT EQUIP_ETYPEID_FK FOREIGN KEY (ETYPEID) REFERENCES ETYPES (ETYPEID),
    CONSTRAINT EQUIP_RATING_CK CHECK (RATING IN ('A', 'B', 'C'))
);

Multiple Constraints on a Single Column

• A column may be included in multiple constraints
• The order# column is included in a primary key and a foreign key constraint

CREATE TABLE ORDERITEMS
(
  ORDER# NUMBER(4),
  ITEM# NUMBER(2),
  ISBN VARCHAR2(10),
  QUANTITY NUMBER(3) NOT NULL,
  PAIDEACH NUMBER(5,2) NOT NULL,
    CONSTRAINT ORDERITEMS_ORDER#ITEM#_PK PRIMARY KEY (ORDER#, ITEM#),
    CONSTRAINT ORDERITEMS_ORDER#_FK FOREIGN KEY (ORDER#) REFERENCES ORDERS (ORDER#),
    CONSTRAINT ORDERITEMS_ISBN_FK FOREIGN KEY (ISBN) REFERENCES BOOKS (ISBN),
    CONSTRAINT ORDERITEMS_QUANTITY_CK CHECK (QUANTITY > 0)
);

Viewing Constraints

Viewing Constraints

USER_CONSTRAINTS

Display constraint listing for a specific table

SELECT constraint_name, constraint_type, search_condition, r_constraint_name FROM user_constraints WHERE table_name = 'EQUIP';

USER_CONS_COLUMNS

Display constraint listing by column

SELECT constraint_name, column_name FROM user_cons_columns WHERE table_name = 'EQUIP';

Using DISABLE/ENABLE

Use DISABLE or ENABLE clause of ALTER TABLE command

ALTER TABLE tablename DISABLE CONSTRAINT constraintname;

ALTER TABLE tablename ENABLE CONSTRAINT constraintname;

Dropping Constraints

• Constraints cannot be modified; they must be dropped and recreated

• Actual syntax depends on type of constraint

  • PRIMARY KEY – just list type of constraint

  • UNIQUE – include column name

  • All others – reference constraint name

Drop Constraint

ALTER TABLE EQUIP DROP CONSTRAINT EQUIP_RATING_CK;

Drop Constraint Error

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

-- Error: this unique/primary key is referenced by some foreign keys

Dropping a PRIMARY KEY referenced by a FOREIGN KEY using CASCADE option

ALTER TABLE CUSTOMERS DROP PRIMARY KEY CASCADE;

Summary

• A constraint is a rule that is applied to data being added to a table

  • The constraint represents business rules, policies, and/or procedures

  • Data violating the constraint is not added to the table

• A constraint can be included during table creation as part of the CREATE TABLE command or added to an existing table using the
• ALTER TABLE command
• A PRIMARY KEY constraint does not allow duplicate or NULL values in the designated column
• Only one PRIMARY KEY constraint is allowed in a table
• A FOREIGN KEY constraint requires that the column entry match a referenced column entry in the referenced table or be NULL
• A UNIQUE constraint is similar to a PRIMARY KEY constraint except it allows NULL values to be stored in the specified column
• A CHECK constraint ensures a value meets a specified condition
• A NOT NULL constraint ensures a value is provided for a column
• A constraint can be disabled or enabled using the ALTER TABLE command and the DISABLE and ENABLE keywords
• A constraint cannot be modified
• To change a constraint, the constraint must first be dropped with the DROP command and then re-created
• USER_CONSTRAINTS and USER_CONS_COLUMNS data dictionary views provide information regarding constraints

C.5 Demo

-- Week 05 --

-- DISPLAY LIST OF USER_TABLES
SELECT * FROM USER_TABLES;
-- DISPLAY LIST OF USER_CONSTRAINTS
SELECT * FROM USER_CONSTRAINTS;

-- CREATE CONSTRAINT DURING TABLE CREATION
CREATE TABLE SUMMERCUSTOMER
(
CID NUMBER(5) PRIMARY KEY,
CNAME VARCHAR2(10) UNIQUE NOT NULL
);

-- DISPLAY CONSTRAINT IN TABLE 'SUMMERCUSTOMER'
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='SUMMERCUSTOMER';
--> SYS_C001873386: SYSTEM DEFINED CONSTRAINT

-- CREATE USER DEFINED CONSTRAINT NAMED 'SUMMERCUSTOMER_CID_PK'
CREATE TABLE SUMMERCUSTOMER1
(
CID NUMBER(5) CONSTRAINT SUMMERCUSTOMER_CID_PK PRIMARY KEY,
CNAME VARCHAR2(10)
);
-- DISPLAY USER DEFINED CONSTRAINT NAMED 'SUMMERCUSTOMER_CID_PK' IN TABLE 'SUMMERCUSTOMER1'
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='SUMMERCUSTOMER1';

-- DROP TABLE SUMMERCUSTOMER1 WITH ENSURING THAT ALL CONSTRAINTS ASSOCIATED WITH TABLE ARE ALSO DROPPED
DROP TABLE SUMMERCUSTOMER1 CASCADE CONSTRAINTS;

-- LIST THE CONSTRAINTS IN SUMMERCUSTOMER AND SUMMERCUSTOMER1 TABLES
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='SUMMERCUSTOMER' OR TABLE_NAME='SUMMERCUSTOMER1';

-- LIST THE CONSTRAINTS IN SUMMERCUSTOMER, SUMMERCUSTOMER1 AND CUSTOMERS TABLES
--> 'IN' OPERATOR = SHORTHAND FOR MULTIPLE 'OR' CONDITIONS
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME IN ('SUMMERCUSTOMER','SUMMERCUSTOMER1','CUSTOMERS');
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='SUMMERCUSTOMER' OR TABLE_NAME='SUMMERCUSTOMER1' OR TABLE_NAME='CUSTOMERS';
--> THE ABOVE 2 CODES WORK SAME!!

-- PARENT TABLE & CHILD TABLE => PRIMARY & FOREIGN KEY RELATIONSHIP
SELECT * FROM BOOKS;
-- PARENT TABLE (AUTHOR)
CREATE TABLE CAUTHOR
(
AID NUMBER(5) CONSTRAINT CAUTHOR_AID_PK PRIMARY KEY,
ANAME VARCHAR2(10)
);
-- CHILD TABLE (BOOK) => CREATE FOREIGN KEY RELATIONSHIP "AID FROM CAUTHOR"
CREATE TABLE CBOOK
(
BID NUMBER(4) CONSTRAINT CBOOK_BID_PK PRIMARY KEY,
TITLE VARCHAR2(20),
CBOOK_AID NUMBER(3) CONSTRAINT CBOOK_AID_FK REFERENCES CAUTHOR(AID)
);
--> PK: 'AID' FROM 'CAUTHOR' & FK: 'CBOOK_AID' FROM 'CBOOK'

-- ⇩ COLUMN LEVEL
CREATE TABLE DEPT
(
DEPTID NUMBER(2) CONSTRAINT DEPT_DEPTID_PK PRIMARY KEY,
DNAME VARCHAR2(20) NOT NULL CONSTRAINT DEPT_DNAME_UK UNIQUE,
FAX VARCHAR2(12)
);
-- ⇩ TABLE LEVEL
CREATE TABLE EQUIP
(
EQUIPID NUMBER(3),
EDESC VARCHAR2(30),
PURCHDATE DATE,
RATING CHAR(1),
DEPTID NUMBER(2) NOT NULL,
ETYPEID NUMBER(2),
CONSTRAINT EQUIP_EQUIPID_PK PRIMARY KEY (EQUIPID),
CONSTRAINT EQUIP_DEPTID_FK FOREIGN KEY (DEPTID) REFERENCES DEPT (DEPTID),
CONSTRAINT EQUIP_ETYPEID_FK FOREIGN KEY (ETYPEID) REFERENCES ETYPES (ETYPEID),
CONSTRAINT EQUIP_RATING_CK CHECK (RATING IN ('A', 'B', 'C'))
);

-- MULTIPLE CONSTRAINTS ON SINGLE COLUMN
CREATE TABLE ORDERITEMS
(
ORDER# NUMBER(4),
ITEM# NUMBER(2),
ISBN VARCHAR2(10)
CONSTRAINT ORDERITEMS_ORDER#ITEM#_PK PRIMARY KEY (ORDER#, ITEM#),
CONSTRAINT ORDERITEMS_ORDER#_FK FOREIGN KEY (ORDER#) REFERENCES ORDERS (ORDER#)
);

-- 'ON DELETE CASCADE' WHEN CREATING FOREIGN KEY
--> AUTOMATICALLY DELETE PARENT ROW WHEN THE ROW IN A CHILD TABLE IS DELETED
CREATE TABLE CBOOK
(BID NUMBER(4) CONSTRAINT CBOOK_BID_PK PRIMARY KEY,
TITLE VARCHAR2(20),
CBOOK_AID NUMBER(3) CONSTRAINT CBOOK_AID_FK REFERENCES CAUTHOR(AID) ON DELETE CASCADE);

-- LIST THE CONSTRAINTS IN CAUTHOR AND CBOOK TABLES
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='CAUTHOR' OR TABLE_NAME='CBOOK';
-- LIST OF SPECIFIC COLUMNS FROM USER_CONSTRAINTS
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE TABLE_NAME='CAUTHOR';
-- DISPLAY CONSTRAINT LISTING BY COLUMN
SELECT CONSTRAINT_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME='CAUTHOR';
-- LIST CONSTRAINTS OF THE CONSTRAINTS_NAME='SYS_C001857793' IN STORE_REPS TABLE
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'STORE_REPS' AND CONSTRAINT_NAME='SYS_C001857793';

-- 'DISABLE' OR 'ENABLE' CONSTRAINTS
ALTER TABLE STORE_REPS DISABLE CONSTRAINT SYS_C001857795;
ALTER TABLE STORE_REPS ENABLE CONSTRAINT SYS_C001857795;

-- DROP CONSTRAINTS & PRIMARY KEY CONSTRAINT
ALTER TABLE EQUIP DROP PRIMARY KEY;
-- DROP PRIMARY KEY REFERENCED BY FOREIGN KEY USING CASCADE OPTION
ALTER TABLE EQUIP DROP PRIMARY KEY CASCADE;
-- DROP CONSTRAINTS & UNIQUE CONSTRAINT
ALTER TABLE EQUIP DROP CONSTRAINT EQUIP_RATING_UK;
-- DROP CONSTRAINTS & FOREIGN KEY CONSTRAINT
ALTER TABLE EQUIP DROP CONSTRAINT EQUIP_RATING_FK;
-- DROP CONSTRAINTS & OTHER TYPES OF CONSTRAINT(EX. CHECK, NOT NULL, ETC)
ALTER TABLE EQUIP DROP CONSTRAINT EQUIP_RATING_CK;

-- ALTER TABLE & ADD CHECK CONSTRAINT
ALTER TABLE CBOOK ADD CONSTRAINT CBOOK_EDITION_CK CHECK (EDITION IN ('A','B','C'));
--> ENSURE THAT THE VALUES ENTERED INTO THE 'EDITION' COLUMN OF THE 'CBOOK' TABLE
--> ARE LIMITED TO ONLY THE VALUES 'A', 'B', OR 'C'
--> ANY OTHER VALUES, VIOLATE THE CONSTRAINT AND RESULT IN AN ERROR!!
ALTER TABLE ORDERS ADD CONSTRAINT ORDERS_SHIPDATE_CK CHECK (ORDERDATE <= SHIPDATE);

-- ALTER TABLE & ADD PRIMARY KEY CONSTRAINT
ALTER TABLE ORDERITEMS
ADD CONSTRAINT ORDERITEMS_ORDER#ITEM#_PK PRIMARY KEY (ORDER#, ITEM#);
-- ALTER TABLE & ADD FOREIGN KEY CONSTRAINT
ALTER TABLE ORDERS
ADD CONSTRAINT ORDERS_CUSTOMER#_FK FOREIGN KEY (CUSTOMER#) REFERENCES CUSTOMERS (CUSTOMER#);
-- ALTER TABLE & ADD FOREIGN KEY CONSTRAINT WITH 'ON DELETE CASCADE'
ALTER TABLE BOOK_STORES
ADD CONSTRAINT FK_REP_ID FOREIGN KEY (REP_ID) REFERENCES STORE_REPS (REP_ID) ON DELETE CASCADE;

-- ALTER TABLE & ADD UNIQUE CONSTRAINT
ALTER TABLE BOOKS ADD CONSTRAINT BOOKS_TITLE_UK UNIQUE (TITLE);
--> NO DUPLICATED VALUE & NULL VALUES ARE PERMITTED

-- ALTER TABLE & ADD NOT NULL CONSTRAINT
ALTER TABLE ORDERS MODIFY (CUSTOMER# CONSTRAINT ORDERS_CUSTOMER#_NN NOT NULL);

-- CAUTHOR IS THE PARENT TABLE,SO U CANNOT DELETE PARENT TABLE FIRST BEFORE CHILD TABLE
-- CBOOK IS THE CHILD TABLE, SO DELETE CBOOK TABLE AND THEN DELETE CAUTHOR TABLE
DROP TABLE CBOOK CASCADE CONSTRAINTS;
DROP TABLE CAUTHOR;

-- PK = PRIMARY KEY CONSTRAINT -> UNIQUENESS & NON-NULL VALUES
-- FK = FOREIGN KEY CONSTRAINT -> ESTABLISH RELATIONSHIP BETWEEN TWO TABLES BASED ON COMMON COLUMN
-- CK = CHECK CONSTRAINT -> SPECIFIC CONDITIONS ON THE VALUES
-- PARENT-CHILD TABLE -> RELATIONSHIP BETWEEN TWO TABLES (PRIMARY KEY OF ONE TABLE & REFERENCED AS FOREIGN KEY IN ANOTHER TABLE)
-- DDL = DATA DEFINITION LANGUAGE -> DEFINING AND MANAGING STRUCTURE OF DB
--> CREATE ALTER DROP TRUNCATE COMMENT RENAME GRANT REVOKE

---

Back to Top

See other articles in Category Oracle-Sql