Inheritance and association in SQL

Question

I have two tables:

create table [Customer]
(
    [Id] int primary key identity not null,
    [Type] int not null check([Type >= 0 and [Type] <= 2)
    -- other columns
)
create table [Partial Record]
(
    [Id] int primary key identity not null,
    [Student Id] int references [Customer]([Id])
)

I called [Student Id] because the Customer table has a inheritance, here's the problem: I want to add a check [Partial Record] to make sure that the association has a "[Type] = 1" for [Partial Record] belongs only to students.

is it possible?

I've never seen a question here in Portuguese. You might have better luck if you can translate it into English. — Gordon Linoff, Oct 05 '12 at 21:21
[Non-English Question Policy](http://blog.stackoverflow.com/2009/07/non-english-question-policy/) — Tim Schmelter, Oct 05 '12 at 21:23
Have you tried inputting data that breaks the rule you expect to be enforced? — Nadir Sampaoli, Oct 05 '12 at 21:29
Personally, I'd probably prefer to derive the type from the various 'child' table relationships (so, the fact that you've got that `Partial_Record` makes it a student, you don't need `Customer.type`). Although, I don't know the 'standard' for these designs. Oh, and whatever you do, **don't** use spaces in entity names. — Clockwork-Muse, Oct 05 '12 at 21:34
What do the values 0 & 2 represent in the `Type` field? If you are separating Students because they are type 1 then it seems to me you need tables for the other types (which will either include or replace your `Customer` table) — Tony, Oct 05 '12 at 21:39

score 1 · Accepted Answer · answered Oct 09 '12 at 06:38

You can do it by adding a super key to the Customer table, and adding an enforcing foreign key:

create table [Customer]
(
    [Id] int primary key identity not null,
    [Type] int not null check([Type] >= 0 and [Type] <= 2)
    ,constraint UQ_Customer_TypeCheck UNIQUE (ID,Type)
)
create table [Partial Record]
(
    [Id] int primary key identity not null,
    [Student Id] int references [Customer]([Id]),
    Type as 1 persisted,
    constraint FK_Partial_TypeCheck FOREIGN KEY ([Student Id],Type) references Customer (ID,Type)
)

(I would probably remove Id from [Partial Record] if each Student should only have one row - just make [Student Id] the primary key)

score 0 · Answer 2 · edited May 23 '17 at 12:34

0

I can only think of doing it with triggers using something like: SQL Server: halt an INSERT in a trigger. Probably won't be very efficient or performant, but should do what you want.

edited May 23 '17 at 12:34

Community

1
1

answered Oct 05 '12 at 21:40

Paul Hadfield

6,088
2
35
56

score 0 · Answer 3 · edited May 23 '17 at 10:24

0

As described in this answer (and this one) to similar questions, you can create a User Defined Function to check the values in the other table and then write a contraint to call the function.

But as others have commented I would consider changing your tables, if you are able to. A different design would remove the need for the type field.

edited May 23 '17 at 10:24

Community

1
1

answered Oct 09 '12 at 06:24

Tony

9,672
3
47
75

Inheritance and association in SQL

3 Answers3