时间戳

首页 > 解决方案 > 用外键引用主键的问题

sql - 用外键引用主键的问题

问题描述

这是使用 Microsoft SQL Server Management Studio 创建图书库数据库的开始的代码。

CREATE DATABASE BOOK_LIBRARY

CREATE TABLE LIBRARY_USER
(
    usr_id int not null primary key,
    f_name varchar(30) not null,
    m_init char(1),
    l_name varchar(30) not null,
    balance decimal(6,2),
    join_date date,
    addrss_1 varchar(30) not null,
    addrss_2 varchar(30),
    city varchar(30) not null,
    addrss_state char(2) not null,
    zip_code varchar(10) not null,
    email varchar(30)
);

CREATE TABLE LIBRARY_TRANSACTIONS
(
    transaction_id int not null primary key,
    maximum_borrow_duration int not null,
    strt_date date not null,
    actual_return_date date not null,
    borrow_usr_id int not null,
    foreign key (borrow_usr_id) references LIBRARY_USER(usr_id)
);

CREATE TABLE BOOKS
(
    isbn varchar(17) not null primary key,
    title varchar(30) not null,
    number_of_copies int not null,
    Author varchar(30) not null,
    Number_of_pages int not null,
    publish_year int not null,
    book_type varchar(20)
);

CREATE TABLE DIGITAL_BOOKS
(
    digital_id int not null primary key,
    format varchar(30) not null,
    size_mb int not null,
    digital_isbn varchar(17) not null,
    foreign key(digital_isbn) references BOOKS(isbn)
);

CREATE TABLE PHYSICAL_BOOKS
(
    physical_id int not null primary key,
    condition varchar(20) not null,
    physical_isbn varchar(17) not null,
    foreign key(physical_isbn) references BOOKS(isbn)
);

CREATE TABLE BOOK_COPY
(
    digi_id int not null,
    phys_id int not null,
    primary key(digi_id, phys_id),
    foreign key(digi_id) references DIGITAL_BOOKS(digital_id),
    foreign key(phys_id) references PHYSICAL_BOOKS(physical_id)
);

CREATE TABLE CONTNS
(
    trans_id int not null primary key,
    digi_id int not null,
    phys_id int not null,
    foreign key(digi_id) references BOOK_COPY(digi_id),
    foreign key(phys_id) references BOOK_COPY(phys_id)
);

尽管我能够查看并看到它digi_id实际上phys_id是book_copy表的主键，但我一直收到此错误：

消息 1776，级别 16，状态 0，第 66
行在引用表 'BOOK_COPY' 中没有与外键 'FK__CONTNS__digi_id__37A5467C' 中的引用列列表匹配的主键或候选键。

消息 1750，级别 16，状态 1，第 66 行
无法创建约束或索引。请参阅以前的错误。

我错过了一些明显的东西吗？我刚开始使用这个程序，所以非常感谢任何帮助。

标签： sqlsql-serverdatabase-designforeign-keysssms

解决方案

这里：

CREATE TABLE CONTNS
(
    trans_id int not null primary key,
    digi_id int not null,
    phys_id int not null,
    foreign key(digi_id) references BOOK_COPY(digi_id),
    foreign key(phys_id) references BOOK_COPY(phys_id)
);

您正在为父表创建两个外键BOOK_COPY。但该表有一个复合主键（即多列主键）

CREATE TABLE BOOK_COPY
(
    digi_id int not null,
    phys_id int not null,
    primary key(digi_id, phys_id),  --> here
    ...
)

因此，您需要一个复合外键：

CREATE TABLE CONTNS
(
    trans_id int not null primary key,
    digi_id int not null,
    phys_id int not null,
    foreign key(digi_id, phys_id) references BOOK_COPY(digi_id, phys_id)
);

推荐阅读