根据定义,一个连接表(桥接表/链接表)用于多对多的关系,在如下情况中使用:
CREATE TABLE Users
(
UserLogin varchar(50) PRIMARY KEY,
UserPassword varchar(50) NOT NULL,
UserName varchar(50) NOT NULL
)
CREATE TABLE Permissions
(
PermissionKey varchar(50) PRIMARY KEY,
PermissionDescription varchar(500) NOT NULL
)
--This is the junction table.
CREATE TABLE UserPermissions
(
UserLogin varchar(50) REFERENCES Users (UserLogin),
PermissionKey varchar(50) REFERENCES Permissions (PermissionKey),
PRIMARY KEY (UserLogin, PermissionKey)
)
但是它也可以被用于一对多的关系,例如在这个例子中,一个用户与多个订单相关联:
(我不太懂数据库,如果我有什么误解,请纠正我。)
CREATE TABLE Users
(
UserLogin varchar(50) PRIMARY KEY,
UserPassword varchar(50) NOT NULL,
UserName varchar(50) NOT NULL
)
CREATE TABLE Orders
(
OrderKey varchar(50) PRIMARY KEY,
OrderDescription varchar(500) NOT NULL
)
--This is the junction table.
CREATE TABLE UserOrders
(
UserLogin varchar(50) REFERENCES Users (UserLogin),
OrderKey varchar(50) REFERENCES Orders (OrderKey),
PRIMARY KEY (UserLogin, OrderKey)
)