Creating Tables

--creating tables
/*this is a multiple
line */

Create Database TestTables

Use TestTables

Create table Person
(
 PersonKey int identity(1,1) primary key,
 PersonLastName nvarchar(255) not null,
 PersonFirstName nvarchar(255) null,
 PersonEmail nvarchar(255) not null,
 PersonAddedDate Datetime default GetDate()
)

Create Table PersonAddress
(
   PersonAddressKey int identity(1,1),
   PersonAddressStreet nvarchar(255) not null,
   PersonAddressCity nvarchar(255) default 'Seattle',
   PersonKey int, --Foreign Key references Person(personKey)
   constraint PK_PersonAddress primary key(PersonAddressKey),
   constraint FK_PersonAddress Foreign key(personKey)
        references Person(PersonKey)
)

Create Table PersonContact
(
   PersonContactKey int identity(1,1),
   PersonContactHomePhone nchar(14),
   PersonKey int

)

Alter table PersonContact
Add Constraint Pk_PersonContact primary key (PersonContactKey),
Constraint Fk_personContact foreign key (PersonKey)
     references Person(PersonKey)

--dropping a column
Alter table Person
Drop column PersoneEmail

--adding a column
Alter Table Person
Add PersonEmail nvarchar(255)

--add unique constraint
Alter table Person
Add constraint unique_Email Unique(PersonEmail)