Page 81 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes Primary Key Concepts

A primary key is one or more columns in a table used to uniquely identify each row in the table.
Primary key values must not be null and must be unique across the column.
A multicolumn primary key is called a composite primary key. The only function that a primary
key performs is to uniquely identify a row and thus if one column is used it is just as good as if
multiple columns are used. Multiple columns i.e. (composite keys) are used only when the
system designed requires a primary key that cannot be contained in a single column.

Example: Primary Key as a Column Constraint:
Create client_master where client_no is the primary key.
CREATE TABLE client master
(client_no varchar2(6) PRIMARY KEY,
name varchar2(20), add}-essl varchar2(30), address2 varchar2(30),
city varcbar2(15), state varchar2(15), pincode number(6),
remarks varchar2(60), bal_due number (10,2));
Primary Key as a Table Constraint:
Create a sales order details table where

Column Name Data Type Size Attributes
S_order_no varchar2 6 Primary Key
product_no varchar2 6 Primary Key
qty_ordered Number 8
qty-disp Number 8
product_rate Number 8,2
CREATE TABLE sales order details
(s_order_no varchar2(6), product_no varchar2(6),
qty _ordered number(8), qty - disp number(8),
product_rate number(8,2),
PRIMARY KEY (s_order_no, product_no));

Unique Key Concepts

A unique key is similar to a primary key, except that the purpose of a unique key is to ensure that
information in the column for each record is unique, as with telephone or driver’s license
numbers. A table may have many unique keys.

Example: Create Table client_master with unique constraint on column client_no
UNIQUE as a Column Constraint:
CREATE TABLE client master
(client_no varchar2( 6) CONSTRAINT cnmn - ukey UNIQUE,
name varchar2(20), address 1 varchar2(30), address2 varchar2(30),
city varchar2(15), state varchar2(l5), pincode number(6),
remarks varch_2(60), bal_due number(lO,2), partpaY311 char(l));

74 LOVELY PROFESSIONAL UNIVERSITY