Help with table constraints

Question

Help with table constraints

webbies

SSC Enthusiast

Points: 101
More actions
May 29, 2012 at 7:26 am

#392369

I'm looking for some help with the following.
I have a table called Deals (PK DealId)
I have a number of tables subordinate to Deals, they are DealEvents, DealOffers and DealProducts. These contain specific data relating to the type of deal, so core info is held in Deals and event data is held in DealEvents etc.
A deal can only have one subordinate row so I introduced a DealFamily type. The type in Deals is selected from a lookup table but the type in each of the subordinate tables is fixed to ensure it is constrained using a FK.
I.e. Deals (DealId, DealFamily) <== DealEvents (DealId, 'Event')
A DealFamily is the parent of DealType, so DealFamily can contain one or more DealTypes. Ideally a Deal should be associated with a DealType rather than a DealFamily, however I'm struggling to determine how I can replace DealFamily with DealType in the Deals table and retain the constraints on the subordinate tables.
I.e. Deals (DealId, DealType) <== ???
Can anyone help?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

There are lots of people around that can and will help. However what you posted is not enough information to even fully understand your question let alone be able to help with a solution. At the very least you should post ddl (create table statements). Then explain what problems you are facing.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

webbies SSC Enthusiast Points: 101 More actions · Answer 2

Sean Lange (5/29/2012)
There are lots of people around that can and will help. However what you posted is not enough information to even fully understand your question let alone be able to help with a solution. At the very least you should post ddl (create table statements). Then explain what problems you are facing.

Thanks for the advice Sean

webbies SSC Enthusiast Points: 101 More actions · Answer 3

I guess, the initial posting does look rather complicated.

Here's a simpler explanation of what I'm trying to achieve - hopfully.

Table 1: Deals (PK DealId)

DealId int

DealTypeId int

Table 2: DealEvents (PK DealId)

DealId int

DealFamilyId (computed persisted '1')

Table 3: DealTypes (PK DealTypeId)

DealTypeId int

DealFamilyId int

I want to create a relationship between Table1 and Table2 where Table2 is constrained using a FK on DealId and Table2:DealFamilyId must = DealFamilyId belonging to the Table1:DealTypeId derived from Table3

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 4

can't see a reason for the calculated persistent column;

here's my prelim version of your schema, based on what you posted:

by making the column not null, with a foeign key, i think that does what you were after when you said

I want to create a relationship between Table1 and Table2 where Table2 is constrained using a FK on DealId and Table2:DealFamilyId must = DealFamilyId belonging to the Table1:DealTypeId derived from Table3

CREATE TABLE DealFamily(

DealFamilyID int primary key,

DealFamilyName varchar(100))

CREATE TABLE DealEvents (

DealId int primary key,

DealFamilyId int unique references DealFamily(DealFamilyID))

CREATE TABLE DealTypes (

DealTypeId int not null primary key,

DealFamilyId int not null references DealFamily(DealFamilyId))

CREATE TABLE Deals (

DealId int not null primary key,

DealTypeId int not null references DealTypes(DealTypeId))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

webbies SSC Enthusiast Points: 101 More actions · Answer 5

Lowell (5/29/2012)
can't see a reason for the calculated persistent column;

Hi, thanks for the reply, I'm in the process of checking it out.

The reason for the persisted column is this;

Table1: Deals - DealId, DealFamilyId (this can be any value from DealFamily table)

Table2: DealEvents - DealId, 1 - when a key is placed beween the 2 tables this ensures that data can only be inserted into table 2 that has a matching DealId and DealFamilyId.

Therefore if I insert a deal into Table1 with a DealFamilyId = 2, I'm constrained from adding data into Table2.

webbies SSC Enthusiast Points: 101 More actions · Answer 6

Checked it out, not quite there.

I'll try to explain a little more.

A deal always has one dealtypeId.

A deal can be one of 3 variants - an Offer, a Product or an Event.

The deal table itself contains all core deal fields. For each of the variants there is also an extension table containing fields that are unique to that variant.

The computed column in each of the variant tables is simply a fixed DealFamilyId value of 1,2 or 3 respectively. When I create a deal in the deals table, if I set the DealFamily to 1 then it ensures I can only insert extra data into extension table 1 because there is a key constraint between the 2 tables on DealId and DealFamilyId.

I.e. if I have a DealId = 123 and a DealFamilyId of 1, that can only match table 1 because table 2 has a fixed DealFamily = 2 and table 3 = 3.

A DealType is a child of DealFamilyId, so what I'm trying to do is only use DealTypeId in Deals, use DealFamilyId in each extension table and ensure that they are correctly contrained.

I.e. I can only insert into an extension table if the DealFamilyId is valid for the DealTypeId in Deals

I'm no sql expert so if thisis complete b**ll**ks please say so because I've very good at overkill 🙂

ScottPletcher SSC Guru Points: 101091 More actions · Answer 7

Table 1: Deals (PK DealId)
DealId int
DealTypeId int
Table 2: DealEvents (PK DealId)
DealId int
DealFamilyId (computed persisted '1')
Table 3: DealTypes (PK DealTypeId)
DealTypeId int
DealFamilyId int
I want to create a relationship between Table1 and Table2 where Table2 is constrained using a FK on DealId and Table2:DealFamilyId must = DealFamilyId belonging to the Table1:DealTypeId derived from Table3

You can't do that using standard FK constraints.

But I don't see the need for a "DealTypeId" -- can't you just use the DealFamilyId directly in the Deals table?

Then you can use FK constraints from the secondary/customized data tables using (DealID, DealFamilyID) as the reference key.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

webbies SSC Enthusiast Points: 101 More actions · Answer 8

Thanks for the reply Scott.

The 2 fields are reside in the Deals table for 2 different reasons. Please excuse the terminology, Im no expert but the DealFamily allows a "is-a" relationship with one of 3 extension tables using DealTypeId, DealFamiltyId. The DealTypeId is the detailed type for the deal - DealTypes are children of DealFamily. Because both are in the Deal table I felt there might be a need to ensure that they were both correctly related given their relationship.

ScottPletcher SSC Guru Points: 101091 More actions · Answer 9

Sorry, I mis-worded my response.

I didn't see the need for the DealType in the Deals table, since it had its own table. Indeed, since its many-to-one, I don't how it could be in the Deals table.

The DealTypeId is the detailed type for the deal - DealTypes are children of DealFamily. Because both are in the Deal table I felt there might be a need to ensure that they were both correctly related given their relationship.

I didn't see DealFamily in the Deals table -- that's why I suggested it be added.

I don't think the DealType should be in the Deals table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".