Creating and altering tables

--tables
Use Community_Assist

Create table EventType
(
   EventTypeKey int identity(1,1) primary key,
   EventTypeName nvarchar(255) not null,
   EventTypeDescription nvarchar(255) null
)

--how to drop key constraint
Alter table Eventype
Drop Constraint [PK__EventTyp__F376BCE4BD778CDF]

Create table EventLocation
(
    EventLocationkey int identity(1,1) not null,
 EventLocationName nvarchar(255) not null,
 EventLocationAddress nvarchar(255) not null,
 EventLocationCity nvarchar(255) default 'Seattle',
 EventLocationState nchar(2) default 'WA',
 EventLocationZip nchar(12) not null,
 EventLocationEmail nvarchar(255) null,
 EventLocationUrl nvarchar(255),
 Constraint PK_EventLocation primary key(EventLocationkey)
)

Create table CAEvent
(
    EventKey int identity(1,1) not null,
 EventDate Date not null,
 EventStartTime Time not null,
 EventTypeKey int references EventType(EventTypeKey),
 EventLocationkey int,
 EmployeeKey int,
 constraint PK_CAEvent primary key (EventKey),
 constraint FK_Location Foreign Key(EventLocationKey)
       references EventLocation(EventLocationKey),
 constraint FK_EmployeeEvent Foreign Key(EmployeeKey)
       references Employee(EmployeeKey)

)

Create table Attendence
(
    EventKey int not null,
 PersonKey int not null
)

Alter table Attendence
Add Constraint PK_Attendence primary key(EventKey, PersonKey)

Alter table Attendence
add Constraint FK_EventAttendence Foreign Key(EventKey)
     References CAEvent(EventKey),
constraint FK_PersonAttendence Foreign Key (PersonKey)
     References Person(PersonKey)

Create table EventComment
( 
   CommentKey int identity(1,1) not null primary key,
   EventKey int references CaEvent(EventKey) ,
   PersonKey int references Person(PersonKey),
   CommentText nvarchar(max),
   CommentDate Datetime default GetDate()
)

Alter table EventLocation
Add constraint unq_name unique(EventLocationName)

--bit is used for booleans 1 true 0 false
Alter table CAEvent
Add Catered bit

Alter table EventType
Drop column EventTypeDescription

Alter Table EventComment
Add EventRating int

Alter table EventComment
Add Constraint ck_rating Check(Eventrating between 1 and 5)