Transact SQL Constraints

1. Primary Key


Primary keys are the unique identifiers for each row. They must contain unique values. A primary key column can’t contain NULL values. A table can have a maximum of one primary key.

2. Unique Key


You can only have a single primary key defined on a table. If you wish to enforce uniqueness on other non-primary key columns, you can use a UNIQUE constraint. A unique constraint, by definition, creates an alternate key. Unlike a PRIMARY KEY constraint, you can create multiple UNIQUE constraints for a single table and are also allowed to designate a UNIQUE constraint for columns that allow NULL values.

3. Foreign Key


Foreign key constraints establish and enforce relationships between tables and help maintain referential integrity, which means that every value in the foreign key column must exist in the corresponding column for the referenced table. Each foreign key is defined using the FOREIGN KEY clause combined with the REFERENCES clause.

4. Check Constraints


The CHECK constraint is used to define what format and values are allowed for a column.

5. Default constraints


A default constraint specifies a value to be applied to a column whenever a value is not supplied in an INSERT.

Introduction to SQL Server Schemas

Schemas


A schema is nothing more than a named, logical container in which you can create database objects.

A schema is a collection of database objects that is owned by a single person and forms a single namespace.

As of SQL Server 2005 and 2008, users are separated from direct ownership of a database object (such as tables, views, and stored procedures). This separation is achieved by the use of schemas, which are basically containers for database objects. Instead of having a direct object owner, the object is contained within a schema, and that schema is then owned by a user.

One or more users can own a schema or use it as their default schema for creating objects.

CREATE SCHEMA

CREATE SCHEMA schema_name [AUTHORIZATION owner_name ]

Schema name is the name for schema and owner name is database user name.

Eg:

CREATE SCHEMA Teacher 


AUTHORIZATION teacher



CREATE SCHEMA Student

AUTHORIZATION student