DDL

use ReportBuilderTraining
GO

/*
drop table evc.Student
drop table evc.Ethnicity
*/

CREATE TABLE [evc].[Student](
	[StudentId] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](25) NULL,
	[LastName] [varchar](50) NULL,
	[BirthDate] [datetime] NULL,
	[Gender] [varchar](50) NULL,
	[Ethnicity] [varchar](50) NULL,
	CONSTRAINT [Person_PK] PRIMARY KEY CLUSTERED 
	(
		[StudentId] ASC
	)
)

go

alter table evc.Student add EthnicityID int null
go

create table evc.Ethnicity (ID int not null identity(1,1))
go

-- add a column
alter table evc.Ethnicity add Description varchar(255) not null
alter table evc.Ethnicity add SortOrder int null
go

-- change column data type
alter table evc.Ethnicity alter column Description nvarchar(255) not null
go

-- create a primary key
alter table evc.Ethnicity add constraint PK_evc_Ethnicity primary key (ID)
go

-- create a foriegn key
alter table evc.Student add foreign key (EthnicityID) references evc.Ethnicity(ID)
go

-- defaults
alter table evc.Ethnicity add constraint SortOrder_def default 0 for SortOrder
alter table evc.Ethnicity alter column SortOrder int not null

-- check contraints
alter table evc.Student add constraint chk_Gender check (Gender in ('Male', 'Female'))
alter table evc.Student drop constraint chk_Gender