/*If you run these alters you will need to drop the
Magazine subsription database and 
rebuild it from the original script*/

--alter table to drop a column
Alter Table MagazineDetail
Drop column SubscriptionPrice

--alter table to re add the columns with 
--a new datatype
Alter Table MagazineDetail
Add SubscriptionPrice Decimal(5,2)

--the stored procedure sphelp returns all the meta
--information about the current database

--alter table to drop a Foreign key constraint
Alter Table MagazineDetail
Drop Constraint FK_MagazineDetails

--alter table add constraint back in
Alter Table MagazineDetail
Add Constraint FK_MagazineDetails Foreign Key(MagID)
references Magazine(magID)

--add a check constraint that sets a limit
--on the range of prices
Alter table MagazineDetail
Add Constraint ck_Price check
	(SubscriptionPrice between 10.00 and 150.00)

--Add a check constraint that limits
--magazineType to a list
Alter table Magazine
Add Constraint ck_Type check
(Magtype in ('Monthly','Annual','Weekly','biannual'))

--create an index on magazine name
create index ix_magname on Magazine(magName)


--see if the index exists
Select * from sys.indexes