Create and alter tables

use CommunityAssist

Select * from PersonAddress

Begin Tran

Update PersonAddress
Set street ='1000 South Main',
apartment='201',
city='Kent'
where PersonAddressKey=1

Commit tran

--create and alter tables
Create database music
use Music

Create table Employee.Album
(
 AlbumKey int identity(1,1) primary key,
 AlbumTitle Nvarchar(255) not null,
 AlbumReleaseDate Date,
 AlbumRecordlabel nvarchar(255)
)

Create Table Artist
(
 ArtistKey int identity(1,1),
 ArtistName nvarchar(255),
 constraint pk_Artist primary key(ArtistKey)
)

Create Table ArtistAlbum
(
 AlbumKey int,
 ArtistKey int,
 Constraint PK_ArtistAlbum Primary Key(Albumkey, ArtistKey),
 Constraint FK_Album Foreign Key(AlbumKey)
  References Album(AlbumKey),
 Constraint FK_Artist Foreign Key (ArtistKey) 
  References Artist(ArtistKey)
  
)

Create table Sale
(
 SaleKey int identity(1000,1) primary key,
 SaleDatetime DateTime default GetDate(),
 EmployeeKey int not null
)

Create Table SaleDetail
(
 SaleDetailKey int identity(1,1) primary Key,
 SaleKey int foreign Key references Sale(SaleKey),
 SalePrice Decimal(6,2)
 Constraint ck_price check (SalePrice between 5 and 5000)
)

Alter table SaleDetail
Add AlbumKey int

Alter table SaleDetail
Add constraint FK_Album2 Foreign key(AlbumKey) references Album(AlbumKey)

Alter table Album
Drop column AlbumRecordTable


Alter table Album
add AlbumNotes xml

Select * from SaleDetail

/* kinds of constraints
primary key
foreign key
unique
check
default
*/

use CommunityAssist
Select * from sysConstraints
sp_Help 'Donation'
select * from sys.key_constraints
Select * from sys.foreign_keys