/* organization */
CREATE TABLE organization (
	org_id                  key_t NOT NULL,
	org_name                varchar(220) NOT NULL,
	org_remark              varchar(255) NULL,
	data_source_id          int NOT NULL,
	dept_unit               varchar(120) NULL,
	entered_staff_id        int NOT NULL,
	date_entered            datetime NOT NULL,
	last_change_staff_id    int NOT NULL,
	last_change_date        datetime NOT NULL
)
go

CREATE UNIQUE CLUSTERED INDEX PKorganization ON organization (org_id, org_name)
go

CREATE UNIQUE INDEX AK1organization ON organization (org_name, org_id)
go

CREATE INDEX F1organization ON organization (entered_staff_id)
go

CREATE INDEX F2organization ON organization (last_change_staff_id)
go

exec sp_primarykey organization, org_id
go

sp_bindefault zero_default, 'organization.data_source_id'
go

sp_bindefault zero_default, 'organization.entered_staff_id'
go

sp_bindefault zero_default, 'organization.last_change_staff_id'
go

sp_bindefault date_default, 'organization.date_entered'
go

sp_bindefault date_default, 'organization.last_change_date'
go

exec sp_foreignkey organization, agent, org_id
go

exec sp_foreignkey organization, staff, entered_staff_id
go

exec sp_foreignkey organization, staff, last_change_staff_id
go

