How to create table in SQL


SQL stands for Structured Query Language. It is an ANSI standard language to communicate for relational database management system. SQL is used to store, manipulate and maintain the data in SQL server database.

What is SQL?


SQL stands for Structured Query Language.
It is an ANSI standard language to communicate for relational database management system.
SQL is used to store, manipulate and maintain the data in SQL server database.

Tables in SQL


Data will be stored in database object called tables which is uniquely identified by the name. Table comprises of columns and rows in which data is being stored.

Syntax to create table in SQL:

CREATE TABLE "tablename"
("column1" "data type",
"column2" "data type",
"column3" "data type");

Example:


CREATE TABLE employee
(first_name VARCHAR(15),
last_name VARCHAR(20),
age INT,
address VARCHAR(30),
city VARCHAR(20),
state VARCHAR(20)
);


The table and column names must start with a letter and can be followed by letters, numbers, or underscores - not to exceed a total of 30 characters in length
The most commonly used data types:
CHAR (size) : Fixed-length character string. Size is specified in parenthesis. Max 255 bytes
VARCHAR (size) : Variable-length character string. Max size is specified in parenthesis.
INT : Use to store integer value in the column
DATETIME : Use to store data and time values in column

What are constraints?
A constraint is basically a rule/condition associated with a column that the data entered into that column must follow. Constraints define rules for the values allowed in columns and are the standard mechanism for enforcing integrity.
The purpose of giving constraints to table columns is to restrict unwanted data to be inserted in the table which helps to maintain the accuracy and integrity of the data in database. Multiple columns in a table can have constraints separately.
SQL Server has following types of constraints
Not Null Constraint
A Not null constraint restricts the insertion of null values into a column. If user is using a Not Null Constraint for a column then user cannot have null value for this column during an insert of data into the table.

Syntax

Column Level Constraint

CREATE TABLE Table_Name
(Column_Name Datatype CONSTRAINT Constraint_Name NOT NULL);

Table Level Constraint

ALTER TABLE Table_Name
ALTER COLUMN Column_Name Datatype NOT NULL
Check Constraint

A Check constraint checks for a specific condition before inserting data into a table. If the data passes all the Check constraints then the data will be inserted into the table otherwise the data for insertion will be discarded. The CHECK constraint ensures that all values in a column satisfies the conditions.

Syntax

Column Level Constraint

Create Table Table_Name (
Column_Name Datatype Constraint Constraint_Name Check(Condition) )

Table Level Constraint

Alter Table Table_Name
Add Constraint Constraint_Name Check(Condition)

Example:

Alter table Constraint_Check
Add constraint Cons_ID Check(Id>150)


Default Constraint

Specifies a default value for column when a value is not specified for the column. In case of an insertion of any value is not specified for the column then the default value will be inserted into the column.

Syntax

Column Level Constraint

Create Table Table_Name (
Column_Name DataType Constraint Constraint_Name Default(Value) )

Table Level Constraint

Alter Table Table_Name
Add Constraint Constraint_Name Default(Value) for[Column_Name]

Example

Alter Table employee  
Add Constraint cons_Default Default(40) for[Age]


Unique Constraint

It ensures that each row for a column must have a unique value. It is like a Primary key but it can accept only one null value. In a table one or more column can contain a Unique Constraint.

Syntax
Column Level Constraint

Create Table Table_Name (
Column_Name Datatype Constraint Constraint_Name Unique )

Table Level Constraint

Alter Table_Name
Add Constraint Constraint_Name Unique(Column_Name)

Example

Alter Table constraint
Add Constraint Unique_Cons Unique(Name)


Primary Constraint

A Primary key uniquly identifies each row in a table. It cannot accept null and duplicate data. One or more of the columns of a table can contain a Primary key.

Syntax
Column Level Constraint

Create Table Table_Name (
Column_Name Datatype Constraint Constraint_Name Primary Key)

Table Level Constraint

Alter Table Table_Name
Add constraint Constraint_Name Primary Key(Column_Name)

Example

Alter Table Employee
Add constraint Constraint_Name Primary Key(Id)


Foreign Constraint
A Foreign Key is a field in a database table that is a Primary key in another table. A Foreign key creates a relation between two tables. The first table contains a primary key and the second table contains a foreign key
Syntax
Column Level Constraint

Create Table Table_Name (
Column_Name Datatype Constraint Constraint_Name References Reference_Table_Name(Reference_Column_Name) )

Table Level Constraint

ALTER TABLE Table_Name
ADD CONSTRAINT Constraint_Name FOREIGN KEY(Column_Name)
REFERENCES Reference_Table (Column_Name)

Example

ALTER TABLE Employee
ADD CONSTRAINT Cons_Emp_Foreign FOREIGN KEY(Id)
REFERENCES My_Constraint(Id)


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: