SQL Create table with primary key :

In my previous articles, I have explained about the different SQL statements, Interview questions for different MNCs. In this article, I will explain the multiple ways to create table. Lot of people wants to know about sql create table with primary key with syntax and example. In this article I explain the sql create table with primary key with real world examples. I will explain multiple ways to create table in SQL also. Table is nothing but the collection of records. User needs to follow specific syntax for creating table in sql.

Primary key is column, which will identify the unique records in the table.

How to create table using multiple ways?

To create table in oracle the user should have the create table privileges. There are so many ways of creating table. User can create table using primary key or unique key also. CREATE TABLE statement is used to create the table in oracle.

sql create table without primary key :

User can create table without using primary key and with using primary key. The simple CREATE TABLE statement is used to create a table.

Create table syntax:

CREATE TABEL TABLE_NAME

(Column_name1 Datatype (Size),

Column_name2 Datatype (Size),

.

.

.

Column_name3 Datatype (size));

 

Here while creating table column name is any name and datatype is SQL supported datatype. User needs to give the size of the datatype according to datatype.

Example:

Roll_No Number(3,0);

Name varchar2(10);

Create table Example :

CREATE TABLE STUDENT

(Rollno Number(3,0),

Name  Varchar2(30),

Standard Varchar2(10));

This is simple example of creating table without any constraints. Constraints are very important for every table to identify the uniqueness of the records as well as identifying the relationship between different tables.

sql create table with primary key :

There is second way to create the table with primary key.User directly needs to add the constraint while creating table with primary key.The syntax is bit different for creating table with primary key.

sql create table with primary key syntax in Oracle :

CREATE TABEL TABLE_NAME

(Column_name1 Datatype (Size),

Column_name2 Datatype (Size),

.

.

.

Column_name3 Datatype (size);

Constraint Constraint_name Primary Key(Column_name));

Example of create table with primary key (Oracle / SQL Server)  :

CREATE TABLE STUDENT_Primary

(Rollno Number(3,0),

NAME  VARCHAR2(30),

Standard VARCHAR2(10),

Constraint pk_roll Primary key(Rollno));

The table named STUDENT_primary is created with Rollno as primary key constraint. If user want to check the constraint use the table named all_constraints and put the filter of the table.There are following type constraints available in all_constraints table.

List Of Constraints identification from All_Constraints table :

C – Check constraint on a table

P – Primary key

U – Unique key

R – Referential integrity

V – With check option, on a view

O – With read only, on a view

H – Hash expression

F – Constraint that involves a REF column

S – Supplemental logging

Checking the Constraint from the table.

Query :

select Constraint_name,Constraint_type from all_constraints where table_name=upper(‘STUDENT_Primary’);

Output :

PK_ROLL  P

Where P Stands fot Constraint type as Primary key and PK_ROLL is the name given to primary key by the user.

 sql create table with primary key syntax in Mysql :

CREATE TABEL TABLE_NAME

(Column_name1 Datatype (Size),

Column_name2 Datatype (Size),

.

.

.

Column_name3 Datatype (size)

Primary Key(Column_name));

In Mysql the Constraint keyword is not used.Instead of that user needs to add primary key keyword directly and in bracket give the column name.

Example of create table with primary key (Mysql)  :

CREATE TABLE STUDENT_Primary

(Rollno Number(3,0),

NAME  VARCHAR2(30),

Standard VARCHAR2(10),

Primary key(Rollno));

So using two different syntax user can create the table in different enviroments and databases.Sometimes user needs to add the constraint after creating the table.

Create table using other table :

User can create the replica of same table using another table in Oracle,Mysql,SQLSERVER as well.

Syntax :

Create table Table_name as Select * from Table_name_old;

Example :

Create table Employee1 as Select * from Employee;

How to add constraint after creating table?

Sometimes user forget to add the constraints to the table.There is some specific way to add the constraint to the table.User needs to use ALTER TABLE statement and add the constraint to specific column.

Add Constraint using mysql :

ALTER TABLE TABLENAME

ADD PRIMARY KEY(Column_name);

 

Example :

ALTER TABLE STUDENT

ADD PRIMARY KEY(Rollno);

Add Constraint using Oracle and SQLSERVER :

ALTER TABLE TABLENAME

ADD Constraint Constraint_name  PRIMARY KEY(Column_name);

Hope this article is helpful to users for create table using primary key. This is the most common question for all the programmer and database developers.