SQL Constraints

CONSTRAINTS

--=========================================[SQL CONSTRAINTS]===========================================================

--Constraints are used to limit the type of data that can go into a table.

--We will focus on NOT NULL, UNIQUE,PRIMARY KEY ,FOREIGN KEY,CHECK ,DEFAULT constraints-----

--NOT NULL-------

CREATE TABLE employee(

     ID          int NOT NULL,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

--UNIQUE---------

CREATE TABLE employee(

     ID          int NOT NULL UNIQUE,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

--PRIMARY KEY-----

CREATE TABLE employee(

     ID          int NOT NULL PRIMARY KEY,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

--FOREIGN KEY-----

--here we have two tables employee and department

CREATE TABLE employee(

     ID          int NOT NULL PRIMARY KEY,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

CREATE TABLE department(

     DepartmentID int NOT NULL PRIMARY KEY,

     name        nvarchar (10),

     designation nvarchar (10),

     empID          int references employee(ID))

--CHECK-----------

CREATE TABLE employee(

     ID          int NOT NULL CHECK (ID>0),

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

--DEFAULT (see below example)--

--=========================================[DEFAULT]====================================================================

--To specify a default value, add "Default [value]" after the data type declaration

--Default City Unknown

CREATE TABLE employee(

     ID          int,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10)default 'Unknown',

     region      char (1))

 INSERT INTO employee (ID, name,    salary, start_date, city,       region)

               values (1,  'Jason', 40420,  '02/01/94', 'New York', 'W')

 INSERT INTO employee (ID, name,    salary, start_date, city,       region)

               values (2,  'Robert',14420,  '01/02/95', 'Vancouver','N')

 INSERT INTO employee (ID, name,    salary, start_date, city,       region)

               values (3,  'Habib', 90020,  '12/03/99',DEFAULT,  'W')

 INSERT INTO employee (ID, name,    salary, start_date, city,       region)

               values (4,  'Linda', 40620,  '11/04/97', 'New York', 'N')

 INSERT INTO employee (ID, name,    salary, start_date, city,       region)

               values (5,  'David', 80026,  '10/05/98', 'Vancouver','W')

SELECT * FROM employee