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)