Create and alter Table

--Create tables
use CommunityAssist

--creating a table
-- if you need a schema just add the schema.TableName
--employee.Meeting

Create table Meeting
(
 MeetingID int identity(1,1) primary key,
 MeetingDate Date not null,
 MeetingTopic NVarchar(255) not null,
 MeetingNotes xml
)

--an example of a auto generated key
--PK__Meeting__E9F9E9AC33D4B598

--table with keys as named constraints
Create Table MeetingAttendance
(
 MeetingAttendanceId int identity(1,1),
 MeetingID int not null Foreign key references Meeting(MeetingID),
 EmployeeKey int not null,
 Constraint PK_MeetingAttendance Primary Key(MeetingAttendanceID),
 Constraint FK_EmployeeAttending Foreign Key (EmployeeKey)
  References Employee(EmployeeKey)
)

--this table is made without any constraints
Create table MeetingRating
(
 MeetingRatingID int identity (1,1),
 MeetingId int not null,
 Rating int default 0
)

--the constraints are added in alter table statments

Alter table MeetingRating
Add Constraint PK_MeetingRating 
Primary key(MeetingRatingID)

Alter table MeetingRating
Add Constraint FK_Meeting Foreign Key(MeetingID)
References Meeting (MeetingID)

--this does a check constraint that 
--limits the value to values between 0 and 5
Alter Table MeetingRating
Add Constraint ck_RatingValue Check
(Rating between 0 and 5)--

--this would force the Meeting Rating value
--to be unique
Alter table MeetingRating
Add Constraint uq_Rating unique (Rating)

--this drops the rating constraint
Alter Table MeetingRating
Drop Constraint uq_Rating

--Drop Table MeetingRating

--to alter or even rename a column
--you must drop it an re add it
--you will lose any data in the column
--if you don't copy it somewhere
Alter table Meeting
Drop column MeetingNotes


Alter table Meeting
Add MeetingNotes xml