Experiment On Primary Key

Today, We will do the experiment on Primary Key.

What is Primary Key?
The primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique or it can be generated by the DBMS. Primary keys may consist of a single attribute or multiple attributes in combination.

Assuming we have a CUSTOMER Table with CUST_ID (PK), CUST_NAME columns. Now by any chance this CUST_ID will repeat in the CUSTOMER Table.

Consider the following example,

SQL> -- Experiment On Primary Key…

SQL> -- Creating Customer Table


SQL>create table customer(
2   cust_id number,
3   cust_name varchar2(15));


Table created.


SQL> -- Note: While creating the table PK was not defined.
SQL> -- Now populate the records for customer table


SQL> insert into customer values(101,’John Peter’);
1 row created.


SQL> insert into customer values(102,’Tom Kyte’);
1 row created.


SQL> insert into customer values(102,’Tom Kyte’);
1 row created.


SQL> commit;
Commit complete.


SQL> select * from customer;


CUST_ID CUST_NAME
------- ---------
101     John Peter
102     Tom Kyte
102     Tom Kyte




SQL>/* Note: In the CUSTOMER Table, CUST_ID - 102 was repeated, Okay becuase PK is not yet defined. */



SQL> -- Now, We are going to define the PK Constraint for CUSTOMER Table


SQL> alter table customer add constraint customer_pk
2 primary key(cust_id) disable;


Table altered.


SQL> create index cust_id_idx on customer(cust_id);


Index created.


SQL> alter table customer enable novalidate primary key;


Table altered.


SQL> -- Now PK Constraint has been defined and enabled for CUSTOMER
SQL>
SQL> -- Verifying the Data Dictionary…
SQL>


SQL>select table_name,constraint_name,constraint_type,status
2   from user_constraints
3   where table_name=’CUSTOMER’;



TABLE_NAME   CONSTRAINT_NAME  C  STATUS
----------   ---------------  -  -------
CUSTOMER     CUSTOMER_PK      P  ENABLED


SQL> /* All right now we have a table called CUSTOMER with primary key defined for column CUST_ID and the CUSTOMER_PK constraint status is ENABLED now.*/


SQL> select * from customer;


CUST_ID  CUST_NAME
-------  ----------
101      John Peter
102      Tom Kyte
102      Tom Kyte

Oops!, What happened?, for CUSTOMER table table we have defined the PRIMARY KEY and its STATUS is enabled but stil, it has duplicate records, CUST_ID - 102 was repeated and while enabling the Primary Key constraint also Oracle did not show any error, stating that your Existing records are duplicated.

SQL> — After enabling the constraint, We will try to insert the duplicate record
SQL> — Lets see what it is telling…


SQL> insert into customer values(101,’John Peter’);


insert into customer values(101,’John Peter’)
*
ERROR at line 1:ORA-00001: unique constraint (DEMO.CUSTOMER_PK) violated

Now if i am trying to insert a duplicate record, it is throwing an error, so constraint is validating the data while inserting new record.

What about the existing duplicate records?
Hmm, The key here is NOVALIDATE clause in the ALTER TABLE CUSTOMER ENABLE… command, the function of this clause is it will skip the validation for existing records while enabling the constraint.

Where it will be useful?, NOVALIDATE clause it will be frequently used in DWH environment by which the user will be TRUSTING the data (that is no validation is required for this data) and they will be loading into their tables.

Any constraint (PRIMARY KEY/FOREIGN KEY/UNIQUE/CHECK) which is defined in the table, will take extra effort to do the validation while you insert a record into table. But it is always Best Practice to define your constraints in the database level.

Hope you have enjoyed the experiment :-)

Validating your Data Entry for Trailing Blanks

Today, I would like to experiment the use of CHECK constraint to validate the trailing blanks in the data.
Example,

demo$Vino%orcl> create table t(name varchar2(20)
2 check (length(name)-length(trim(name))=0));


Table created.


demo$Vino%orcl> insert into t values('Tom');
1 row created.


demo$Vino%orcl> insert into t values('Tom Peter');
1 row created.


demo$Vino%orcl> -- Name with trailing blanks


demo$Vino%orcl> insert into t values('Tom ');
insert into t values('Tom ')
*
ERROR at line 1:
ORA-02290: check constraint (DEMO.SYS_C005409) violated


demo$Vino%orcl> select * from t;


NAME
----------
Tom
Tom Peter

Hope this will be useful for you :-)