------------------------------------------------------------------
-- Working with CONSTRAINT
------------------------------------------------------------------

-------------------
--- PRIMARY KEY ---
-------------------

--- After Table Creation ---
DROP TABLE emp 

CREATE TABLE emp
(emp_id int, 
emp_name varchar(25), 
emp_hiredate date default getdate())

Insert Into emp values(3, 'John', getdate()-100), 
					  (1, 'Keren', default),
					  (9, 'Nick', getdate()-365)

select * from emp

alter table emp
add constraint PK_EMP primary key clustered (emp_id) 

alter table emp
alter column emp_id int not null

alter table emp
add constraint PK_EMP primary key clustered (emp_id) 

select * from emp

--- With Table Creation ---
Drop Table emp

CREATE TABLE emp
(emp_id int CONSTRAINT emp_id_pk PRIMARY KEY, 
emp_name varchar(25), 
emp_hiredate date default getdate())

Insert Into emp values(3, 'John', getdate()-100)
Insert Into emp values(9, 'Nick', default)
Insert Into emp values(3, 'Keren', default)

-------------------
--- FOREIGN KEY ---
-------------------

--- With Table Creation ---

DROP TABLE dep

CREATE TABLE dep
(
dep_id int CONSTRAINT dep_id_pk PRIMARY KEY, 
dep_name varchar(25)
)

INSERT INTO dep VALUES (50 , 'HR') 

INSERT INTO dep VALUES (999 , 'Sales') 

SELECT * FROM dep
select * from emp

DROP TABLE emp

CREATE TABLE emp
(emp_id int identity (1,1) CONSTRAINT emp_id_pk PRIMARY KEY, 
emp_name varchar(25) CONSTRAINT emp_nam_nn NOT NULL, 
emp_phone varchar(25) CONSTRAINT emp_phn_uq UNIQUE,
emp_mail  varchar(25) CONSTRAINT emp_mail_ck CHECK(emp_mail LIKE '%@gmail.com'),
emp_hiredate date default getdate(), 
emp_salary int CONSTRAINT emp_sal_ck CHECK(emp_salary BETWEEN 0 AND 10000),
dep_id int CONSTRAINT emp_depid_fk REFERENCES dep(dep_id))

INSERT INTO emp
VALUES ('Nick', '054-0000000', 'Nick@gmail.com', getdate(), 9000, 999)

INSERT INTO emp
VALUES ('Nick', '054-0000001', 'Nick@gmal.com', getdate(), 9000, 50)

DELETE FROM dep 
WHERE dep_id = 50 

DELETE FROM dep 
WHERE dep_id = 60 

SELECT * FROM dep

SELECT * FROM emp

----------------------------
--- After Table Creation ---
----------------------------

Drop Table emp 

CREATE TABLE emp
(emp_id int identity (1,1) CONSTRAINT emp_id_pk PRIMARY KEY , 
emp_name varchar(25) CONSTRAINT emp_nam_nn NOT NULL, 
emp_phone varchar(25) CONSTRAINT emp_phn_uq UNIQUE,
emp_mail  varchar(25) CONSTRAINT emp_mail_ck CHECK(emp_mail LIKE '%@gmail.com'),
emp_hiredate date default getdate(), 
emp_salary int CONSTRAINT emp_sal_ck CHECK(emp_salary BETWEEN 0 AND 10000),
dep_id int)

Alter Table emp
Add Constraint FK_DEP_ID FOREIGN KEY (dep_id)
REFERENCES dep(dep_id) ON DELETE CASCADE 

--* Cascading option updates data automatically

INSERT INTO emp
VALUES ('Nick', '054-0000000', 'Nick@gmail.com', getdate(), 9000, 999)

INSERT INTO emp
VALUES ('Nick', '054-0000001', 'Nick@gmail.com', getdate(), 9000, 50)

DELETE FROM dep 
WHERE dep_id = 50 

SELECT * FROM dep
SELECT * FROM emp

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

DROP TABLE emp 

CREATE TABLE emp
(emp_id int identity (1,1) CONSTRAINT emp_id_pk PRIMARY KEY , 
emp_name varchar(25), 
emp_phone varchar(25) CONSTRAINT emp_phn_uq UNIQUE,
emp_hiredate date default getdate())

INSERT INTO emp
VALUES ('Nick' , '050-0000000' ,  getdate())

INSERT INTO emp
VALUES ('John' , '050-0000001' ,  getdate())

SELECT * FROM emp 

----------------
--- NOT NULL ---
----------------

DROP TABLE emp 

CREATE TABLE emp
(emp_id int identity (1,1) CONSTRAINT emp_id_pk PRIMARY KEY , 
emp_name varchar(25) CONSTRAINT emp_nam_nn NOT NULL, 
emp_phone varchar(25) CONSTRAINT emp_phn_uq UNIQUE,
emp_hiredate date default getdate())

INSERT INTO emp
VALUES (NULL , '050-0000000' ,  getdate())

INSERT INTO emp
VALUES ('Nick' , '050-0000001' ,  getdate())

SELECT * 
FROM emp 

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

DROP TABLE emp 

CREATE TABLE emp
(emp_id int identity (1,1) CONSTRAINT emp_id_pk PRIMARY KEY , 
emp_name varchar(25) CONSTRAINT emp_nam_nn NOT NULL, 
emp_phone varchar(25) CONSTRAINT emp_phn_uq UNIQUE,
emp_mail  varchar(25) CONSTRAINT emp_mail_ck CHECK(emp_mail LIKE '%@gmail.com'),
emp_hiredate date default getdate())

--- Error Message
INSERT INTO emp
VALUES ('Nick' , '054-0000000' , 'Nick@walla.co.il' , getdate())
 
INSERT INTO emp
VALUES ('Nick' , '054-0000000' , 'Nick@gmail.com' , getdate())

SELECT * FROM emp 

DROP TABLE emp 

CREATE TABLE emp
(emp_id int identity (1,1) CONSTRAINT emp_id_pk PRIMARY KEY , 
emp_name varchar(25) CONSTRAINT emp_nam_nn NOT NULL, 
emp_phone varchar(25) CONSTRAINT emp_phn_uq UNIQUE,
emp_mail  varchar(25) CONSTRAINT emp_mail_ck CHECK(emp_mail LIKE '%@gmail.com'),
emp_hiredate date default getdate(), 
emp_salary int CONSTRAINT emp_sal_ck CHECK(emp_salary BETWEEN 0 AND 10000))

INSERT INTO emp
VALUES ('Nick' , '054-0000000' , 'Nick@gmail.com' , getdate(), 11000)

INSERT INTO emp
VALUES ('Nick' , '054-0000000' , 'Nick@gmail.com' , getdate(), 9000)