SQL Create Statement

Tables are the structure where data is stored in the database. In most cases, there is no way for the database vendor to know ahead of time what your data storage needs are, then tables in the database makes itself. Many database tools allow you to create tables automatically,but always tables are the container of all the data, it is important to include the CREATE TABLE syntax in this tutorial.

Tables are divided into rows and columns. Each row represents one piece of data, and each column represent a component of piece of data.Example, if we have a table for recording customer information, then the columns may include information such as First Name, Last Name, birth Date, Address, City, Country, and so on. As a result, when we specify a table, we include the column headers and the data types for that particular column.

Data comes in a variety of forms. It could be an integer (such as 1), a real number (such as 0.55), a date/time expression (such as '2000-JAN-25 03:22:22'),a string (such as 'sql'), or even in binary format. When we specify a table, we need to specify the data type associated with each column (i.e., we will specify that 'First Name' is of type char(40) - meaning it is a string with 40 characters). One thing to note is that different relational databases allow for different data types.

Syntax

CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )

Example

CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)

Create Index Statement

Index are used to retrieve data from tables by quicker way. Let's use an example it is much quicker for us to go to the index section at the end of the book, locate which pages contain information that we want and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need.

This principle applies for retrieving data from a database table. Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster.

Therefore, it is often desirable to create indexes on tables. An index can cover one or more columns.

Syntax

CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)

Example

CREATE INDEX IDX_CUSTOMER_LAST_NAME on CUSTOMER (Last_Name)

Primary Key

A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself , or it can be an artificial field (one that has nothing to do with the actual record). A primary key consist of one or more fields on a table. When multiple fields are used as a primary key then it is called composite key.

Primary keys can be specified either when the table is created or by changing the existing table structure with alter command.

CREATE TABLE Customer (SID integer, Last_Name varchar(30), First_Name varchar(30), PRIMARY KEY (SID));

ALTER TABLE Customer ADD PRIMARY KEY (SID);

Foreign Key

A foreign key is a field that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity means only values that are supposed to appear in the database are permitted.

For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDERS table that includes all customer orders. The constraint here is that all orders must be associated with a customer that is already in the CUSTOMER table. In this case, we will place a foreign key on the ORDERS table and have it relate to the primary key of the CUSTOMER table. By this way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMER table.

Both Table is given below:-

Table CUSTOMERS

column name	characteristic
SID	Primary Key
Last_Name
First_Name

Table ORDERS

column name	characteristic
Order_ID	Primary Key
Order_Date
Customer_SID	Foreign Key
Amount

In the above example, the Customer_SID column in the ORDERS table is a foreign key pointing to the SID column in the CUSTOMER table.

Syntax

CREATE TABLE ORDERS
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references CUSTOMER(SID));

Share And Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.

Keywords sql create table, oracle create table, mysql create table, oracle create table syntax, create global temporary table, mysql create table syntax, how to create html table links, access create table, composite primary key, primary key access, mysql primary key

HTML Quizes

XML Quizes