CREATE TABLE in SQL


CREATE TABLE


Before you start implementing your tables in the database, you should always spend some time design your tables properly using a design tool like, e.g., ERwin, Toad Data Modeler, PowerDesigner, Visio, etc. This is called Database Modeling.


Database Design




The CREATE TABLE statement is used to create a table in a database. 


Syntax:


CREATE TABLE table_name
column_name1 data_type,
column_name2 data_type,
column_name3 data_type, 
....

The data type specifies what type of data the column can hold.

You have special data types for numbers, text dates, etc.

 Examples:

  • Numbers: int, float 
  • Text/Stings: varchar(X) – where X is the length of the string 
  • Dates: datetime etc.
Example: We want to create a table called “CUSTOMER” which has the following columns and data types:

Create Table

Example Code:


CREATE TABLE CUSTOMER 
CustomerId int IDENTITY(1,1) PRIMARY KEY, 
CustomerNumber int NOT NULL UNIQUE, 
LastName varchar(50) NOT NULL, 
FirstName varchar(50) NOT NULL, 
AreaCode int NULL, 
Address varchar(50) NULL, 
Phone varchar(50) NULL,
)
GO

 

 

Best practice:

  • When creating tables you should consider following these guidelines: 
  • Tables: Use upper case and singular form in table names – not plural, e.g., “STUDENT” (not students) Columns: Use Pascal notation, e.g., “StudentId” 
  • Primary Key: 
    1. If the table name is “COURSE”, name the Primary Key column “CourseId”, etc. 
    2. “Always” use Integer and Identity(1,1) for Primary Keys. Use UNIQUE constraint for other columns that needs to be unique, e.g. RoomNumber 
  • Specify Required Columns (NOT NULL) – i.e., which columns that need to have data or not
  • Standardize on few/these Data Types: int, float, varchar(x), datetime, bit
  • Use English for table and column names
  • Avoid abbreviations! (Use RoomNumber – not RoomNo, RoomNr, ...)

 

No comments:
Write comments

Please do not enter spam links

Meet US

Services

More Services