Skip to main content

A little bit about Integrity Constraints (Advance SQL)

I've been asked today by one of my younger brother that "How can we create a table using SQL command in Oracle 10g  such that a column must accept those values that start with a specific character e.g. A."
Then I replied him to use Integrity Constraints. Since he was not familiar about  Integrity Constraints  hence he wanted to get the complete solution of his real problem.
His Problem statement was:

"Create cust table which contains cno having pk(primary key),cname and occupation where data values inserted for cno must start with the capital letter C and cname should be in uppercase."

Integrity Constraints are those which ensure that all the changes made to the Database by authorized users don't result in a loss of data consistency.
A list of Integrity Constraints are given below
  • Primary Key
  • Foreign Key
  • Unique 
  • Check
  • Not Null
We will use Check  Integrity Constraint to solve the problem.
Syntax:
column_name data_type(size) CHECK(logical expression)



However the entire solution of the problem is given below:
In Oracle DBMS's SQL command shell you have to write:
 SQL>
CREATE TABLE cust(cno varchar2(10), cname varchar2(25),occupation varchar2(30),
CHECK(cno like 'C%'),
CHECK(cname=upper(cname)),
CONSTRAINT pk PRIMARY KEY(cno));
And in MySQL DBMS's SQL command shell you have to write: 
  SQL>
 CREATE TABLE cust(
cno nvarchar( 10 ) ,
cname nvarchar( 25 ) ,
occupation nvarchar( 30 ) ,
CHECK (
cno LIKE 'C%'
),
CHECK (
cname = upper( cname )
),
CONSTRAINT pk PRIMARY KEY ( cno )
);
Hope it will help others( SQL beginners) as well.This is my first article about SQL.    
 

Comments

Popular posts from this blog

Cannot access a disposed object. A common cause of this error is disposing a context that was

  Cannot access a disposed object. A common cause of this error is disposing a context that was resolved from dependency injection and then later trying to use the same context instance elsewhere in your application. This may occur if you are calling Dispose() on the context, or wrapping the context in a using statement. If you are using dependency injection, you should let the dependency injection container take care of disposing context instances. Object name: 'PropertyContext'.  public class CustomerHub : Hub     {         public readonly ICustomerService _customerService;         private readonly IServiceScopeFactory _serviceScopeFactory;         public CustomerHub(ICustomerService service, IServiceScopeFactory serviceScopeFactory)         {             _customerService = service;             _serviceScopeFactory = serviceSco...

First J2ME Application & Deployment(.jar,.jad) for your Supported Mobile Phones

Most probably 2 years ago I had decided to swap from C#.NET to Java.But at the time of its beginning I found it so messy than that of C#.As I desired to develop applications for my famous China Phone(Network down most of the time but supports java) at last I did it at the end of 2010.Now I'll discuss how to prepare your desktop to develop your first  j2me applications .   What is actually J2ME? J2ME stands for Java 2 Micro Edition, also known as Java ME. J2ME is a stripped down version of Java targeting devices with lower processing capabilities, like mobile phones, pagers .J2ME runs on a highly optimized version of Java Run-time Environment called KVM which comes pre-installed on devices(mobile phone,hand-held devices) supporting J2ME apps. Basic Concepts on J2ME: We can divide J2ME framework into three main parts namely Configurations, profiles and optional packages Configurations : A configuration contains the KVM and important class libraries, It defin...