CREATE TABLE Room ( RoomID nvarchar(8) PRIMARY KEY, Capacity numeric(3)
CREATE TABLE Room(RoomID nvarchar(8)PRIMARY KEY,容量数字(3)
)
CREATE TABLE RoomType ( RoomType nvarchar(2) primary key, Description nvarchar(20), Responsiblity nvarchar(20) )
CREATE TABLE RoomType(RoomType nvarchar(2)主键,描述nvarchar(20),Responsiblity nvarchar(20))
alter table Room
改变房间
add constraint fk_type foreign key (Type) references RoomType(Type)
添加约束fk_type外键(Type)引用RoomType(Type)
Major Error 0x80040E11, Minor Error 0
重大错误0x80040E11,轻微错误0
alter table Room add constraint fk_type foreign key (Type) references RoomType(Type) Invalid column ID. [ Type ]
alter table房间添加约束fk_type外键(类型)引用RoomType(Type)无效的列ID。 [类型]
1 个解决方案
#1
0
You can create a table RoomEquipment that combines RoomID and EquipmentType like this:
您可以创建一个RoomEquipment表,它将RoomID和EquipmentType组合在一起,如下所示:
create table RoomEquipment (
RoomID int,
EquipmentType nvarchar(1),
primary key (roomid, equipmenttype),
constraint fk_roomequipment_equipment_type
foreign key (equipmenttype)
references equipment(equipmenttype),
constraint fk_roomequipment_equipment_roomid
foreign key (roomid)
references room(roomid)
);
Other tables may look like this:
其他表可能如下所示:
-- I prefer using char datatype for predictable number of characters in a field
CREATE TABLE RoomType (
Roomtype nvarchar(2) NOT NULL,
Description nvarchar(20),
Responsibility nvarchar(20),
primary key (Roomtype)
);
-- You could use char(1) instead of nvarchar(1) for type; I'd prefer using int instead
create table Equipment (
Equipmenttype nvarchar(1) NOT NULL,
Description nvarchar(10)
);
-- Added a unique key to assist with good foreign key relationship
alter table Equipment add constraint uk_equipment_equipmenttype unique (equipmenttype);
-- Just use an int or char if roomID length is predictable
Create table Room (
RoomID nvarchar(8),
Capacity numeric(3),
Roomtype(fk,nvarchar(2)
);
alter table room add constraint uk_room_id unique (roomid);
Example is here: http://sqlfiddle.com/#!3/f510b
示例如下:http://sqlfiddle.com/#!3 / f510b
#1
0
You can create a table RoomEquipment that combines RoomID and EquipmentType like this:
您可以创建一个RoomEquipment表,它将RoomID和EquipmentType组合在一起,如下所示:
create table RoomEquipment (
RoomID int,
EquipmentType nvarchar(1),
primary key (roomid, equipmenttype),
constraint fk_roomequipment_equipment_type
foreign key (equipmenttype)
references equipment(equipmenttype),
constraint fk_roomequipment_equipment_roomid
foreign key (roomid)
references room(roomid)
);
Other tables may look like this:
其他表可能如下所示:
-- I prefer using char datatype for predictable number of characters in a field
CREATE TABLE RoomType (
Roomtype nvarchar(2) NOT NULL,
Description nvarchar(20),
Responsibility nvarchar(20),
primary key (Roomtype)
);
-- You could use char(1) instead of nvarchar(1) for type; I'd prefer using int instead
create table Equipment (
Equipmenttype nvarchar(1) NOT NULL,
Description nvarchar(10)
);
-- Added a unique key to assist with good foreign key relationship
alter table Equipment add constraint uk_equipment_equipmenttype unique (equipmenttype);
-- Just use an int or char if roomID length is predictable
Create table Room (
RoomID nvarchar(8),
Capacity numeric(3),
Roomtype(fk,nvarchar(2)
);
alter table room add constraint uk_room_id unique (roomid);
Example is here: http://sqlfiddle.com/#!3/f510b
示例如下:http://sqlfiddle.com/#!3 / f510b