/
Creating Tables
48 lines (39 loc) · 1.14 KB
/
Creating Tables
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
Use MetroAlt
Create Table BusService
(
BusServiceKey int identity(1,1) primary key,
BusServiceName nvarchar(255) not null,
BusServiceDescription nvarchar(255)
)
Create Table Maintenance
(
Maintenance Key int identity(1,1) primary key,
MaintenanceDate Date not null,
BusKey int not null
Constraint FK_Bus Foreign Key(BusKey)
References Bus(BusKey)
)
Create Table MaintenanceDetail
(
MaintenanceDetailKey int identity(1,1),
MaintenanceKey int not null,
EmployeeKey int not null,
BusServiceKey int not null,
MaintenanceNotes nvarchar(255)
)
Alter table MaintenanceDetail
Add Constraint PK_MaintenanceDetail
primary key (MaintenanceDetailKey);
Alter Table MaintenanceKey
Add Constraint Fk_MaintenanceKey
References MaintenanceDetail(MaintenanceKey);
Alter Table EmployeeKey
Add Constraint Fk_EmployeeKey
References MaintenanceDetail(EmployeeKey);
Alter Table BusServiceKey
Add Constraint Fk_BusServiceKey
References MaintenanceDetail(BusServiceKey);
Alter Table BusType
Add BusTypeAccessible bit default 0; --defaults to 'no', when SELECT is performed, CASE WHEN is used to convert to YES and NO
Alter Table Employee
Add Unique (EmployeeEmail)