Chapter -5 Managing Tables

Leave a comment (2) Go to comments

Previous Post

Managing Tables

Now we need to create tables to store data, while creating tables in a database you need to specify some rules & constraints for columns to accept the specified kind of data. You are responsible for managing tables. Management of tables includes, deleting tables when not required, modifying tables to add columns or to change the rules imposed on table.

Rule to follow while creating a table

  • The table name could be of maximum 128 characters.
  • The column name in a table must be unique for that particular table only. This means two or more tables may have same name columns.

CREATING A TABLE IN A USER DEFINED DATABASE

Before creating a table you must enter the database to create a table or to make any changes in it. To enter the database there is a command & you have to execute this command as done before while creating a database (By selecting the command and pressing the execute button or by selecting the command and by pressing CTRL + E).

SYNTAX: “USE DATABASE_NAME”

We will use HECTOR because our database name is HECTOR.

COMMAND: -USE HECTOR”

Select the command & press CTRL + E. And now we will use the command to create a table.

SYNTAX: – “CREATE TABLE TABLE_NAME (COLUMN NAME_DATATYPE, COLUMN NAME_DATATYPE)”


Suppose I want to create a table named as Employee & Columns in table as EMPID for employee ID, EMPNAME for employee name, EMPSAL for employee salary. Datatype stands for the type of data I want to enter in the columns, for example I want the INTEGER datatype for EMPID, CHAR (Character) datatype for EMPNAME & again INTEGER datatype for EMPSAL. I will type the following command in next line & execute it.

COMMAND: -“CREATE TABLE EMPLOYEE (EMPID INT, EMPNAME VARCHAR (20), EMPSAL INT)”

NOTE: - I wrote EMPNAME VARCHAR (20), this is because I want that characters in EMPNAME column should not exceed 20 words & I used VARCHAR because if I use CHAR instead of VARCHAR, it will allocate a memory of 20 words in system which means if I add a name HECTOR who has only 6 characters, then also it will takes the memory for 20 words only. In VARCHAR the memory varies as per the words entered by the user, it means it will take only 6 memory blocks for HECTOR.

The above command will create a table EMPLOYEE with columns name as EMPID, EMPNAME, EMPSAL as shown in the following Picture.

Chapter  5 Managing Tables sql server Managing Tables sql server Data Integrity Rule to follow while creating a table CREATING TABLE IN A USER DEFINED DATABASE CREATE TABLE TABLE NAME (COLUMN NAME DATATYPE COLUMN NAME DATATYPE)

Implementing Data Integrity

If rules or constraints are not applied while defining or creating a table, the data stored in tables can become redundant. For example: if you do not store the data of students with complete course details & contact details, then the data would not be useful. Similarly if the student contact details are stored in two different tables, the details of student might not match which results in confusion & inconsistency.

· Domain Integrity: – This ensures that only valid values should be filled in the columns. It can be done by restricting the type of data, the range of values & the format of the data. For example: you have a table named as STUDENT with a column name CITY that store the name of the cities from which a student can take admission for a particular course. The cities that can be entered are ‘BOSTON’, ’CHICAGO’, ’NEW YORK’, ’DALLAS’, ’WASHINGTON’, ’NEW JERSEY’. By enforcing domain integrity you can ensure that only the valid values are entered in the column name CITY.

· Referential Integrity: – This ensures that the values of primary key match the values of foreign key in another table. For example: when a student register himself for a course in an institute, his registration ID in student registration table must match to the registration ID of the particular student in the table of student details.

· Entity Integrity: -This ensures that each row can be uniquely identified by primary key (which contains only unique values for a column). The column cannot contain any NULL values. For example: There are two students with same name ‘PETER’, by enforcing entity integrity they can be easily identified, because they are depending on the primary key called SID which containing 001 & 002 id’s for both student.

· User Defined Integrity: -This refers to a set of rules applied by a user, and they do not belong to any above category (entity, domain & referential integrity).

Applying Constraints

To remove the repetition of Student ID in SID column you can prevent this by enforcing data integrity by using constraints, these are the set of rules that must be followed to maintain the correctness & consistency of data in a table. Constraints are defined on columns at the time of creating a table.

Types of Constraints

· Primary Key Constraints:-You can define a primary key constraint while creating a table or add it later by altering the table. This constraint will not accept NULL values & repeated values, it also ensures the entity integrity.

SYNTAX: -“CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPE CONSTRAINT CONSTRAINT_NAME PRIMARY KEY)”

For example: – I want to create a primary key column named as SID in table STUDENT2. The following is the command of creating this column.

COMMAND: - CREATE TABLE STUDENT2 (SID INT CONSTRAINT CK1 PRIMARY KEY)

· Foreign Key Constraints:-When the data of one table depends upon the data in another table, we can use foreign key constraint to remove the inconsistency between these tables. A foreign key constraint associate one or more column (foreign key) with other columns (primary key) in another table.

SYNTAX: -“CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPE CONSTRAINT CONSTRAINT_NAME FOREIGN KEY

REFERENCES TABLE_NAME (COLUMN_NAME) )”

For example: – I want to create a foreign key column SID in table STUDENT3, which depends on the primary key column SID in table STUDENT2.

COMMAND: -CREATE TABLE STUDENT3 (SID INT CONSTRAINT CK6 FOREIGN KEY REFERENCES STUDENT2 (SID))

· Unique Constraint:-The unique constraint is similar to the primary key constraint except that it allows NULL values for a single row. This is used to enforce uniqueness on non-primary key columns. Multiple unique constraints can be applied on multiple columns in a table.

SYNTAX: -“CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPE CONSTRAINT CONSTRAINT_NAME UNIQUE)”

For example: -I want to create a unique column SID in table STUDENT4, this will accept only one null values & rest it will work as a primary key.

COMMAND: - CREATE TABLE STUDENT4 (SID INT CONSTRAINT CK3 UNIQUE)

· Check Constraints: – A check constraints enforce domain integrity by restricting the values to be inserted in a column. These are evaluated in the order in which they are defined; there may multiple check constraints on a single column. You can use IN, LIKE & BETWEEN to store values in a column.

IN: -This ensures that values entered are from a list of constraint expression only.

For example: – I want that in a column name CITY only ‘WASHINGTON’, ‘NEW JERSEY’ or ‘BOSTON’ should be entered. Any other city entered gets refused automatically.

SYNTAX for IN: -“CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPE CONSTRAINT CONSTRAINT_NAME CHECK (COLUMN_NAME IN (‘CHOICE ONE OF THESE’, ‘CHOICE ONE OF THESE’, ‘CHOICE ONE OF THESE’) ) )”

COMMAND: -CREATE TABLE STUDENT6(CITY VARCHAR(20)CONSTRAINT CK7 CHECK(CITY IN(‘WASHIGNTON’,’NEW JERSEY’,’BOSTON’)))

LIKE: -This ensures that the values entered are of certain pattern. This can be achieved by using wildcards (%, _, []).

For example: – I want that a name entered in column SNAME ends with character ‘a’, I will use like check constraint for that. This will only accept a name which ends with ‘a’.

SYNTAX for LIKE: -“CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPE CONSTRAINT CONSTRAINT_NAME CHECK (COLUMN_NAME LIKE % a ) )”

COMMAND: -CREATE TABLE STUDENT7 (SNAME VARCHAR(20) CONSTRAINT CK8 CHECK(SNAME LIKE ‘%a’))

This column will not accept any name except those who end’s with ‘a’ only like ‘Sandra’.

BETWEEN: -This ensures that the values entered are from a predefined range only.

For example: – I want that a column name LEAVES in table STUDENT8 will accept only values from 10 to 25 only, for this I will use BETWEEN check constraint because I am giving a range to the user.

SYNTAX for BETWEEN: -“CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPE CONSTRAINT CONSTRAINT_NAME CHECK (COLUMN_NAME BETWEEN FROM_RANGE AND TO_RANGE ) )”

COMMAND: -CREATE TABLE STUDENT8 (LEAVES INT CONSTRAINT CK9 CHECK(LEAVES BETWEEN 10 AND 25))

This will accept values from 10 to 25 only in column LEAVES.

RULES

EOF - Chapter -5 Managing Tables, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

2 Comments.

  1. very usefull for points of bignner

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.

Trackbacks and Pingbacks: