Oracle SQL Query to Set Default Values While Creating Tables

Oracle SQL Query to Set Default Values While Creating Tables

To learn the Oracle SQL query for setting default values in the columns like the primary key at the time of the creation of tables we need to see the table structure first. 

The table structure is as follows:

   Table Name: Client_Master, Description:  Used to store client information.

Column Name	Data Type	Size	Attributes
clientno	varchar2	6	Primary key, the first letter must start with ‘C’
name	varchar2	20	The name should be in upper case
address1	varchar2	30	Not Null
address2	varchar2	30
city	varchar2	15
pincode	number	8	Not Null
state	varchar2	15	State in West Bengal, Bihar, Odissa, Gujrat
baldue	number	10,2	value is greater or equal to 0

Here, in the above table if we see the Attributes column then we can find the required constraints or default values associated with the column names. Like primary key clientno must have to start with the letter C, for the client names all characters should be in upper case and also like in the due balance column the value should be greater or equal to 0. So these are the default criteria for creating the table.

The SQL query for the above table will be:-

Create table Client_Master(  

  clientno Varchar2(6) Check (clientno LIKE 'C%') Primary Key,

  name Varchar2(20) Check (name=UPPER(name)),

  address1 Varchar2(30) Not Null,

  address2 Varchar2(30),

  city Varchar2(15),

  pincode Number(8) Not Null,

  state Varchar2(15) Check (state In ('West Bengal','Bihar','Tamil Nadu','Gujrat')),

  baldue Number(8,2) Default 0 Check (baldue>=0)

);

Output:-

clientno	name	address1	address2	city	pincode	state	baldue
empty

The use of CHECK:

In the above table, referred to in the question, the primary key has to start with the letter 'C'. So we need to check at the time of table creation, that the primary key column clientno starts with C% which means the first letter always has to be C, and then the rest of the letters or numbers will not be checked. 

Then the next column name has to be in uppercase letters, so again we have to check whether the name is equal to the uppercase form of the inputted value name or not. So, we have to use the UPPER() function where the column name is the argument. If the inputted name is in upper case letters then the constraint will be matched.

Likewise, for the state column, state names have to be in 'West Bengal', 'Bihar', 'Odissa', 'Gujrat', so we have to check the states in those names.

Then the last column baldue has a constraint that the default value will be 0 and any inputted value must have to be positive. So, we again have to check the condition as baldue is greater or equal to 0 or not. If it satisfies all the conditions then the table will be created successfully as it is showing in the output. 

I hope you'll get the desired result after running the SQL command on your end. For any kind of query don't forget to mention them in the comments section.

Inserting Values in the Table

insert into client_master values('C2301','SUNIL','Chennai','Chennai','Chennai',703410,'Tamil Nadu',789.45);

insert into client_master values('C1340','VIVEK','Kolkata','Kolkata','Kolkata',700010,'West Bengal',1002.48);

insert into client_master values('C7020','PRIYA','PATNA','','Patna',720440,'Bihar',882.59);

select * from Client_Master;

clientno	name	address1	address2	city	pincode	state	baldue
C2301	SUNIL	Chennai	Chennai	Chennai	703410	Tamil Nadu	789.45
C1340	VIVEK	Kolkata	Kolkata	Kolkata	700010	West Bengal	1002.48
C7020	PRIYA	PATNA		Patna	720440	Bihar	882.59

Delete Values from Table

delete from client_master where state ='Tamil Nadu';

Here, as you can see the row associated with the Tamil Nadu state is deleted.

clientno	name	address1	address2	city	pincode	state	baldue
C1340	VIVEK	Kolkata	Kolkata	Kolkata	700010	West Bengal	1002.48
C7020	PRIYA	PATNA		Patna	720440	Bihar	882.59