Scenario:
A company has several small tables in their database. These tables consisted of two columns and less than one thousand rows.
Below an example of table schema,
create table hairColor
(
hairColorID int identity(1,1),
description varchar(16) not null
)
Go
insert into hairColor values(‘Brown’)
insert into hairColor values(‘Blonde’)
create table eyeColor
(
eyeColorID int identity(1,1),
name varchar(10) not null
)
Go
insert into hairColor values(‘Black’)
insert into hairColor values(‘Grey’)
create table submitStatus
(
statusID int identity(1,1),
description varchar(30) not null
)
Go
Question:
How would you combine these tables into one table and enforce the data integrity? Please provide the table schema and few insert statement to demonstration how to fill the table.
------解决方案--------------------
create table T
(
id int identity(1,1),
description varchar(30) not null,
flag varchar(02) not null
)
insert into T(description,flag)
select 'Brown ', 'H ' union all
select 'Blonde ', 'H ' union all
select 'Black ', 'E ' union all
select 'Gray ', 'E ' union all
select 'xxx ', 'S '
.....
------解决方案--------------------
create table T
(
id int identity(1,1),
description varchar(30) not null,
eyeColorID int not null,
hairColorID int not null,
statusID int not null
)
------解决方案--------------------
create table hairColor
(
hairColorID int identity(1,1),
description varchar(16) not null
)
Go
insert into hairColor values(‘Brown’)
insert into hairColor values(‘Blonde’)
create table eyeColor
(
eyeColorID int identity(1,1),
name varchar(10) not null
)
Go
insert into hairColor values(‘Black’)
insert into hairColor values(‘Grey’)
create table submitStatus
(
statusID int identity(1,1),
description varchar(30) not null
)
Go
select id=identity(int,1,1),* into #t from
(
select description,1 as flag from hairColor
union all
select description,2 from eyeColor
union all
select description,3 from submitStatus
)a