Constraints

Constraints let you define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity.

1.       NOT NULL specifies that the column does not accept NULL values.

2.       CHECK constraints enforce domain integrity by limiting the values that can be put in a column.

3.       UNIQUE constraints enforce the uniqueness of the values in a set of columns.

4.       PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify         a row in a table.

5.       FOREIGN KEY constraints identify and enforce the relationships between tables.

 The ON DELETE clause controls what actions are taken when you try to delete a row to which existing foreign keys point. The ON DELETE clause has the following options:

·         NO ACTION specifies that the deletion fails with an error.

·         CASCADE specifies that all the rows with foreign keys pointing to the deleted row are also deleted.

·         SET NULL specifies that all rows with foreign keys pointing to the deleted row are set to NULL.

·         SET DEFAULT specifies that all rows with foreign keys pointing to the deleted row are set to their default value.

Schema

A schema is a collection of database objects that are owned by a single principal and form a single namespace. Schemas own objects and principals own schemas. A schema can be owned by either a primary or secondary principal, with the term “principal” meaning any SQL Server entity that can access securable objects.

Users can now have a default schema assigned using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER commands. If no default schema is supplied for a user then DBO will be used as the default schema.

            The primary purpose of SQL schema was -is- to facilitate security management: define who [which principals] can access what [which database objects]. This was made particularly easier starting with SQL 2005 when the schema stopped being directly tied to the owner. 

Another use of schema is to serve as a namespace that is preventing name clashes between objects from different schemas.

       The original use of this was to allow multiple [interactive, i.e. ad-hoc like] users of a given database to create their own tables or stored procedures (or other objects), without having to worry about the existence of similarly named objects possibly introduced by other users. The Namespace-like nature of schema can also be put to use in a planned database setting, i.e. one when a single architect designs the database structure in a way which provides distinct type of access, and indeed different behaviors, for distinct user groups.

Schemas provide the opportunity to simplify administration of security, backup and restore, and database management by allowing database objects, or entities, to be logically grouped together. This is especially advantageous in situations where those objects are often utilized as a unit by applications. For example, a hotel-management system may be broken down into the following logical entities or modules: Rooms, Bar/Restaurant, and Kitchen Supplies. These entities can be stored as three separate physical databases. Using schemas however, they can be combined as three logical entities in one physical database. This reduces the administrative complexity of managing three separate databases.

CREATE LOGIN [User1] WITH PASSWORD=N'User1'

CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[Pract]

CREATE ROLE [Pract_Role] AUTHORIZATION [dbo]

CREATE SCHEMA [Pract_Sch] AUTHORIZATION [User1]

GRANT DELETE,SELECT,INSERT,UPDATE,ALTER ON SCHEMA::[Pract_Sch] TO [Pract_Role]

EXEC sp_addrolemember N'Pract_Role', N'User1'

GO

GRANT CREATE TABLE TO [Pract_Role]

1.       We have created a login called User1

2.       Created a user with same name

3.       Created a role

4.       Created a schema under User1

5.       Granted permissions on schema to the role

6.       Added the role to the user

7.       Granted create table to the role

Here, we can observer that we have to grant permissions on table/schema to the role.