继续请教一个面试题目,高手帮忙啊,先谢了!

[zrb007]

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.

[zsl5305256]

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

[zrb007]

csdn果然高手如云啊,拜谢!!

[playwarcraft]

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
)