Creating and altering Tables

Create DataBase PetPals

Use PetPals

Create table Pet
(
   PetKey int identity(1,1) primary key,
   PetName nvarchar(255) not null,
   PetSpecies nvarchar(255) not null,
   PetSex nchar(1),
   PetCondition nvarchar(255),
   PetAge int,
   PetDateAdded Date default GetDate()
)

alter table Pet
Add Constraint chk_Sex Check (PetSex in ('F', 'M'))




Insert into Pet(PetName, PetSpecies, PetSex, PetCondition, PetAge)
Values('Skittles','Cat','F', 'Good', 2)

Select * from Pet


Create Table CareTakers
(
 CareTakerKey int identity(1,1),
 CaretakerName nvarchar(255) not null,
 CareTakerStreet nvarchar(255) not null,
 CareTakerCity nvarchar(255) default 'Seattle',
 careTakerState nchar(2) default 'WA',
 CareTakerZip nchar(10) not null,
 CareTakerPhone nchar(13),
 Constraint PK_CareTakers primary key(CareTakerKey)
)

Create table Services
(
 ServiceKey int identity(1,1),
 ServiceName Nvarchar(255) not null,
 ServicePrice Decimal(10,2) not null
)
 Alter table Services
 add Constraint PK_Service primary Key (ServiceKey)

 Alter table Services
 Add Constraint unique_name Unique(ServiceName)

Create Table CareTakerPet
(
 CareTakerPetKey int identity(1,1),
 PetKey int Foreign key references Pet(PetKey),
 CareTakerKey int,
 CareTakerPetDate Date not null,
 Constraint FK_CareTaker Foreign Key (CareTakerKey)
  references CareTakers(CareTakerKey),
 Constraint PK_CareTakerPet Primary Key (CareTakerPetKey)
 

)

Create Table CareTakerPetDetail
(
 CareTakerPetKey int,
 ServiceKey int,
 Constraint PK_CareTakerPetDetail Primary Key (CareTakerPetKey, ServiceKey),
 Constraint FK_CareTakerPetKey Foreign Key (CareTakerPetKey)
  References CaretakerPet(CareTakerPetKey),
 Constraint FK_Service foreign key (ServiceKey) 
  references Services(ServiceKey)
)