Wednesday, June 24, 2015

SQL - Keywords, Identifiers, and Constants

A database is the computerized logical representation of a conceptual (or business) model, consisting of a set of informal business rules. These rules are the user-understood meaning of the data. Because computers comprehend only formal representations, business rules cannot be represented directly in a database. They must be mapped to a formal representation, a logical model, which consists of a set of integrity constraints. The same thing is applied for an SQL statements which are made up of words that are keywords, identifiers, and constants. Avoid using ISO reserved keywords for object names and identifiers. Every word in an SQL statement will always one of these Keywords, Identifiers and Constants. We are defining all these words here -

The ISO standard SQL language has many keywords. Some are designated as reserved words and others as non-reserved words. In ISO SQL, reserved words cannot be used as identifiers for database objects, such as tables, columns, and so on. These are words defined in the SQL standard that we use to construct an SQL statement. Many keywords are mandatory, but most of them are optional.

These are names that we give to database objects such as tables and columns. Everything in Microsoft SQL Server can have an identifier. Servers, databases, and database objects, such as tables, views, columns, indexes, triggers, procedures, constraints, and rules, can have identifiers. Identifiers are required for most objects, but are optional for some objects such as constraints.

The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words. When identifiers are used in Transact-SQL statements, the identifiers that do not comply with these rules must be delimited by double quotation marks or brackets. 

These are literals that represent fixed values and dictate what values are valid for data in the database. 
A constraint is usually associated with a table and is created with a CREATE CONSTRAINT or CREATE ASSERTION SQL statement.
They are the best things in the database because they define certain properties that data in a database must comply with. 
They can apply to a column, a whole table, more than one table or an entire schema. A reliable database system ensures that constraints hold at all times (except possibly inside a transaction, for so called deferred constraints).
Constraints are the rules and restrictions to apply on the type of data in a table. They specify the limit on the type of data that can be stored in a particular column in a table using constraints and also maintain the data integrity and accuracy in the table. They also ensure the unwanted data can't be inserted into tables. The most common and known constraints are:
Not Null - each value in a column must not be NULL. click here to know How does Not Null constraint work?
Unique - value(s) in specified column(s) must be unique for each row in a table
Primary Key - value(s) in specified column(s) must be unique for each row in a table and not be NULL; normally each table in a database should have a primary key - it is used to identify individual records
Foreign Key - value(s) in specified column(s) must reference an existing record in another table (via it's primary key or some other unique constraint)
Check - an expression is specified, which must evaluate to true for constraint to be satisfied 

Example - a perfectly respectable SQL statement


Microsoft SQL Server uses reserved keywords for defining, manipulating, and accessing databases. Reserved keywords are part of the grammar of the Transact-SQL language that is used by SQL Server to parse and understand Transact-SQL statements and batches.

Let’s examine its keywords, identifiers and constants:

Keywords - SELECT, FROM, WHERE and The equals sign (=) .

Note: SELECT and FROM are mandatory, but WHERE is optional. The equals sign (=) is an operator, a special type of keyword.

Identifiers - EmployeeName and AGE
They refer to objects in the database. EmployeeName and Age are column names, while EmployeeMaster is a table name

Constant = 2098 is a numeric constant. The constraints are conditions to validate some specific condition. Constraints related with database are Domain integrity, Entity integrity, Referential Integrity, User Defined Integrity constraints etc.

Constraints are nothing but the rules on the data. What data is valid and what is invalid can be defined using constraints. So, that integrity of data can be maintained.Now we are able to explain the SQL statements as a SQL statement is nothing but the combination of Keywords, Identifiers and Constants.

No comments:

Post a Comment