How to map specialization with covering constraints in sql server

Question

I have two tables, 'PublishedBook' and 'TextBook', which specialize "Items" entity. 'PublishedBook' and 'TextBook' cover 'items'.No overlapping as well. I think that to illustrate this I need to do something impossible like this;

create table TextBook
(

    itemNo char(5),
    semester varchar(5),
    section varchar(10),
    pYear int,
    constraint TextBookPK primary key(itemNo),
);


create table PublishedBook
(

    itemNo char(5),
    edition varchar(5),
    constraint PublishebookPK primary key(itemNo)
);

create table Item
(

    itemNo char(5) ,
    title  varchar(10),
    description varchar(100),
    constraint itemPK primary key(itemNo),
    constraint itemFK foreign key(itemNo) references PublishedBook(itemNo) 
    constraint itemFK2 foreign key(itemNo) references TextBook(itemNo)
);

Make the 'itemNo' of Item table refer to both primary keys of PublishedBook and TextBook.

Can anyone please explain me how can I go through this?

You are already doing it what is the problem with your approach? — M.Ali, Mar 08 '14 at 17:52
mr.M.Ali, my way is not possible in sql server i illustrated what i think i should do... Im asking how i should do it correctly.. — Nadun Liyanage, Mar 08 '14 at 17:54

score 0 · Answer 1 · answered Mar 08 '14 at 18:37

0

what you are doing is correct, you are just missing one comma after the second constraint on table Item:

create table TextBook
(

itemNo char(5),
semester varchar(5),
section varchar(10),
pYear int,
constraint TextBookPK primary key(itemNo),
);


create table PublishedBook
(

itemNo char(5),
edition varchar(5),
constraint PublishebookPK primary key(itemNo)
);

create table Item
(

itemNo char(5) ,
title  varchar(10),
description varchar(100),
constraint itemPK primary key(itemNo),
constraint itemFK foreign key(itemNo) references PublishedBook(itemNo) ,
constraint itemFK2 foreign key(itemNo) references TextBook(itemNo)
);

answered Mar 08 '14 at 18:37

Jayvee

10,670
3
29
40

but when I insert data into items i got this error after correctly inserting to PublishedBook and TextBook; " The INSERT statement conflicted with the FOREIGN KEY constraint "itemFK2". The conflict occurred in database "master", table "dbo.PublishedBook", column 'itemNo'. The statement has been terminated." – Nadun Liyanage Mar 08 '14 at 18:59
are all three tables in master database? – Jayvee Mar 08 '14 at 20:18

score 0 · Answer 2 · answered Mar 09 '14 at 08:07

what is the result of this query that checks the constraints?:

SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE constraint_name
in(
SELECT UNIQUE_CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE constraint_name in(
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE constraint_type='FOREIGN KEY'
AND TABLE_NAME='Item')
)

How to map specialization with covering constraints in sql server

2 Answers2