Working with Operators’ Constraints and Data Types

There is a corresponding data type for every column, local variable, expression, and parameter in SQL Server. A data type is a property that describes the type of data that an object may store, such as integer data, character data, monetary data, date and time data, binary strings, and so on.

SQL Server comes with a collection of system data types that specify all of the data types that may be used with SQL Server. In Transact-SQL or the Microsoft.NET Framework, you may also design your own data types. The alias data types are based on the data types given by the system. See CREATE TYPE for further information about alias data types (Transact-SQL). The methods and operators of a class that you construct using one of the programming languages supported by the.NET Framework give rise to user-defined types.

When an operator combines two expressions with distinct data types, collations, accuracy, scale, or length, the following properties influence the result:

• The result’s data type is decided by applying the data type precedence rules to the data types of the input expressions. See Data Type Precedence for further details (Transact-SQL).
• When the result data type is char, varchar, text, nchar, nvarchar, or ntext, the collation is decided by collation precedence rules. See Collation Precedence for further details (Transact-SQL).
• The result’s precision, scale, and length are determined by the precision, scale, and length of the input expressions. See Precision, Scale, and Length for further details (Transact-SQL).

SQL data types include:

SQL Data Types with Exact Numeric Values:

1. bigint 
2. int 
3. tinyint 
4. bit 
5. decimal 
6. Numeric
7. Money
8. Small money

SQL Programmers:

Operators are classified into three kinds.

1. Operators for Arithmetic
2. Operators of Comparison
3. Operators Logical

SQL Constraints:

1. Constraints are the rules that are imposed on data columns in a table.
2. Tables can be restricted in terms of what type of data may be entered.
3. This guarantees that the data in the database is accurate and reliable.
4. Constraints might be applied at the column or table level.
5. Only one column is subject to column-level limitations.
6. Table level limitations, on the other hand, are applied to the whole table.

PRIMARY KEY vs. UNIQUE KEY

• The UNIQUE constraint assures that each value in a column is unique.
• The UNIQUE and PRIMARY KEY requirements both ensure that a column or collection of columns is unique.
• When a PRIMARY KEY constraint is added, a UNIQUE constraint is added immediately.
• You can have several UNIQUE constraints per table, but only one PRIMARY KEY constraint.
• The main key column cannot contain any null values, however, the Unique Key column can have one.

FOREIGN KEY: 

• A row/record in another database table that is uniquely recognized.
• A FOREIGN KEY is a field (or group of fields) in one table that relates to the PRIMARY KEY in another. The table containing the foreign key is referred to as the child table, whereas the one containing the main key is referred to as the referenced or parent table.
• A foreign key is used to keep a relationship between two tables alive.
• A foreign key in a table does not need to be unique inside that table. However, because it is the Primary Key of another database, it must be unique in this table as well.
• Foreign keys might be either NULL or duplicate.