The organization of a database involves many different objects. All objects of a database are either physical or logical. The physical objects are related to the organization of the data on the physical device (disk). The Database Engine’s physical objects are files and filegroups. Logical objects represent a user’s view of a database. Databases, tables,… [Continue Reading]
Data Definition Language - SQL Server 2012
This chapter describes all the Transact-SQL statements concerning data definition language (DDL). The DDL statements are divided into three groups, which are discussed in turn. The first group includes statements that create objects, the second group includes statements that modify the structure of objects, and the third group includes statements that remove objects.
CREATE TABLE: A Basic Form
The CREATE TABLE statement creates a new base table with all corresponding columns and their data types. The basic form of the CREATE TABLE statement is Note – Besides base tables, there are also some special kinds of tables, such as temporary tables and views. table_name is the name of the created base table. The… [Continue Reading]
CREATE TABLE and Declarative Integrity Constraints
One of the most important features that a DBMS must provide is a way of maintaining the integrity of data. The constraints, which are used to check the modification or insertion of data, are called integrity constraints. The task of maintaining integrity constraints can be handled by the user in application programs or by the… [Continue Reading]
Referential Integrity (ON DELETE, ON UPDATE)
A referential integrity enforces insert and update rules for the tables with the foreign key and the corresponding primary key constraint. Examples 5.7 and 5.10 (see section “CREATE TABLE and Declarative Integrity Constraints“) specify two such constraints: prim_empl and foreign_works. The REFERENCES clause in Example 5.10 determines the employee table as the parent table. If… [Continue Reading]
Creating Other Database Objects (views, index, etc)
A relational database contains not only base tables that exist in their own right but also views, which are virtual tables. The data of a base table exists physically—that is, it is stored on a disk—while a view is derived from one or more base tables. The CREATE VIEW statement creates a new view from… [Continue Reading]
Integrity Constraints and Domains
A domain is the set of all possible legitimate values that columns of a table may contain. Almost all DBMSs use base data types such as INT, CHAR, and DATE to define the set of possible values for a column. This method of enforcing “domain integrity” is incomplete, as can be seen from the following… [Continue Reading]
Altering a Database – ALTER DATABASE statement
The ALTER DATABASE statement changes the physical structure of a database. The Transact-SQL language allows you to change the following properties of a database: Add or remove one or more database files Add or remove one or more log files Add or remove filegroups Modify file or filegroup properties Set database options Change the name… [Continue Reading]
Altering a Table – ALTER TABLE statement
The ALTER TABLE statement modifies the schema of a table. The Transact-SQL language allows the following types of alteration: Add or drop one or more new columns Modify column properties Add or remove integrity constraints Enable or disable constraints Rename tables and other database objects The following sections describe these types of changes. Adding or… [Continue Reading]
