CODEWITHSUNDEEP
.netsqlsql-serversql-server-2012.net-4.5
Leo
Leo

Reputation: 2360

Inheritance and association in SQL

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?

Upvotes: 1

Views: 240

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

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)

Upvotes: 1

Tony
Tony

Reputation: 10327

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.

Upvotes: 0

Paul Hadfield
Paul Hadfield

Reputation: 6136

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.

Upvotes: 0

Related Questions