Thursday, January 7, 2016

Creating and altering tables

Create database Assignment1Example;

use Assignment1Example;
/*
This is a multi line
comment
*/

Create Table Person
(
  -- identity autoincrements the integer
      PersonKey int identity(1,1) primary key,
   PersonLastName nvarchar(255) not null,
   PersonFirstname nvarchar(255) null,
   PersonAddress nvarchar(255) not null,
   PersonCity nvarchar(255) default 'Seattle',
   PersonState nchar(2),
   PersonZip nchar(10) not null

);

Create table Donation
(
 DonationKey int identity(1,1),
 PersonKey int not null,
 DonationDonationDate date default GetDate(),
 DonationAmount decimal(10,2),
 constraint PK_Donation  Primary key (DonationKey),
 constraint Fk_Person Foreign Key (PersonKey)
             references Person(PersonKey)

);

Create table Volunteer
(
 VolunteerKey int identity(1,1),
 PersonKey int not null,
 VolunteerStartDate Date not null,
 VolunteerEndDate Date not null
)

Alter table Volunteer 
Add Constraint PK_Volunteer Primary Key (VolunteerKey)

Alter Table Volunteer
Add Constraint FK_VolunteerPerson Foreign Key (PersonKey)
   references Person(PersonKey)

   Create table LinkToSomething
   (
      PersonKey int,
   DonationKey int,
   Constraint pK_Link primary key(PersonKey, DonationKey),
   constraint FK_PersonLink foreign key(PersonKey)
       references Person(PersonKey),
   Constraint FK_Donor Foreign key (DonationKey)
        references Donation(donationKey)
   )


   Alter table Person
   Drop column PersonState

   Alter Table Volunteer
   add VolunteerLocation Nvarchar(255)

   Begin tran

   Rollback tran
   Commit tran

No comments:

Post a Comment