BOOK_INFORMATION(Book_ID,Book-Name), 其中Book_ID为主键
Author(Author, Age)主键Author
Write(Book_ID, Author)主键(Book_ID, Author), 两个都是外键.再写一个触发器, 保证每一个作者编写的图书的名应该是互不相同的。(即将Write表和BOOK_INFORMATION表连接之后按Author, Book_Name分组, 保证每组的元组个数都为1)
Author(Author, Age)主键Author
Write(Book_ID, Author)主键(Book_ID, Author), 两个都是外键.再写一个触发器, 保证每一个作者编写的图书的名应该是互不相同的。(即将Write表和BOOK_INFORMATION表连接之后按Author, Book_Name分组, 保证每组的元组个数都为1)
create table BOOK_INFORMATION(Book_ID varchar(10) primary key, Book_Name varchar(40))
create table Authors(Author varchar(5) primary key, Age int)
create table Write(Book_ID varchar(10) references BOOK_INFORMATION(Book_ID)
, Author varchar(5) references Authors(Author)
primary key(Book_ID, Author))
go
create trigger tr_author_bookname
on write
after update, insert
as
if exists(select 1
from (select count(1) as times
from write join book_information
on write.book_ID=book_information.book_ID
group by author, book_name)t
where times<>1)
begin
rollback tran
raiserror('错误:每个作者参与编写的图书的名应该是互不相同', 1, 16)
end
go
--测试数据
insert BOOK_INFORMATION select '001', '数据库'
union all select '002', '软件工程'
insert authors select '张锦', 40
union all select '李华', 35
insert write select '001', '张锦'
union all select '002', '张锦'
select*
from book_information as t1 join write as t2 on t1.book_id=t2.book_id
join authors as t3 on t2.author=t3.author
--清理
drop trigger tr_author_bookname
drop table Write
drop table BOOK_INFORMATION
drop table Authors