Creating and Altering Tables

use ITC22;

Create table Customer_Steve
(
 CustomerKey int identity(1,1) primary key,
 CustomerLastName nvarchar(255) not null,
 CustomerFirstName nvarchar(255),
 CustomerDateAdded DateTime default GetDate()

)

Create Table CustomerOrder
(
   CustomerOrderKey int identity(1,1),
   CustomerKey int not null,
   CustomerOrderDate Date default getDate(),
   Constraint PK_CustomerOrder 
          Primary Key(CustomerOrderKey),
   Constraint FK_Customer Foreign Key(CustomerKey)
          References Customer_Steve(CustomerKey)
);

Create Table OrderDetail
(
 OrderDetailKey int identity(1,1),
 OrderKey int not null,
 OrderDetailProduct nvarchar(255) not null,
 OrderDetailPrice decimal(10,2)not null
)

Alter table OrderDetail
Add Constraint Pk_OrderDetail 
     primary key (OrderDetailKey);

Alter Table OrderDetail
Add Constraint FK_CustomerOrder Foreign Key(OrderKey)
   References CustomerOrder (CustomerOrderKey)

Alter Table Customer_Steve
Add CustomerEmail nvarchar(255)

Alter Table Customer_Steve
Drop column CustomerEmail

Alter Table Customer_Steve
Add Constraint unique_Email unique (customerEmail)

Alter Table OrderDetail 
Add constraint check_Price 
Check (OrderDetailPrice Between 1 and 100)