Very Computer

However this is what I'm try to do:

If "some_other_field" in the child table = 'X' only
consider child_parent1_fk constraint

if "some_other_field" in the child table = 'Y' only
consider child_parent2_fk constraint

In other word I have a condition that will dictate if the
inserted value need to exist IN parent1 OR the parent2
table.

I hope I didn't make too confusing.

Following are the 2 parents and child table I'm using as
an example. Any help is truly apprecited.

-- The PARENT1 table

CREATE TABLE TABLE_LOOKUP_1 (
lk1col1 int not null,
lk1col2 varchar(25) not null,
primary key (lk1col1))

insert into table_lookup_1
values (1, 'any value')

--The PARENT2 table

CREATE TABLE TABLE_LOOKUP_2 (
lk2col1 int not null,
lk2col2 varchar(25) not null,
primary key (lk2col1))

insert into table_lookup_2
values (2, 'any value')

--The CHILD table

CREATE TABLE T1 (
COL1 int not null,
COL2 int not null,
COL3 varchar(25) not null,
PRIMARY KEY (COL1),
FOREIGN KEY (col2)
        REFERENCES TABLE_LOOKUP_1,
FOREIGN KEY (col2)
        REFERENCES TABLE_LOOKUP_2
)
GO

insert into T1
values (1,1,'any value') -- I get fk violation because 1
only exist in one parent

CREATE TABLE Products (prodcode INTEGER PRIMARY KEY, prodtype CHAR(1) NOT
NULL CHECK (prodtype IN ('A','B')), UNIQUE (prodcode,prodtype))

CREATE TABLE Books (prodcode INTEGER PRIMARY KEY, prodtype CHAR(1) NOT NULL
CHECK (prodtype='B'), FOREIGN KEY (prodcode,prodtype) REFERENCES Products
(prodcode, prodtype))

CREATE TABLE Audio (prodcode INTEGER PRIMARY KEY, prodtype CHAR(1) NOT NULL
CHECK (prodtype='A'), FOREIGN KEY (prodcode,prodtype) REFERENCES Products
(prodcode, prodtype))

The constraint in your child table would reference the Products table on
both type and code:

... FOREIGN KEY (prodtype, prodcode) REFERENCES Products (prodtype,
prodcode)...

--
David Portas
------------
Please reply only to the newsgroup
--

It seems to me that the way you suggested, would work only
if the fk could be validated agains ANY parent.

But my case is that it must validated against a specific
parent -- depending on another field in the child table
(not the fk field). If this field = 'A' validate again
PARENT1 if this field = 'B' validate again PARENT2.

Am I correct?

Thanks again so much.

helena

Quote:>-----Original Message-----
>You can't have an "optional" FK constraint but you can

Quote:>that relates the other two and then reference that
instead. Example:

>CREATE TABLE Products (prodcode INTEGER PRIMARY KEY,

Quote:>NULL CHECK (prodtype IN ('A','B')), UNIQUE

Quote:

>CREATE TABLE Books (prodcode INTEGER PRIMARY KEY,

Quote:>CHECK (prodtype='B'), FOREIGN KEY (prodcode,prodtype)
REFERENCES Products
>(prodcode, prodtype))

>CREATE TABLE Audio (prodcode INTEGER PRIMARY KEY,

Quote:>CHECK (prodtype='A'), FOREIGN KEY (prodcode,prodtype)
REFERENCES Products
>(prodcode, prodtype))

>The constraint in your child table would reference the
Products table on
>both type and code:

>.... FOREIGN KEY (prodtype, prodcode) REFERENCES Products
(prodtype,
>prodcode)...

>--
>David Portas
>------------
>Please reply only to the newsgroup
>--

>.

In general this is not a valid normalised design.

But you can try:

CREATE TABLE [IdParents1] (
 [Id] [int] IDENTITY (1, 2) NOT NULL ,
 [Val] [char] (10) NOT NULL ,
 CONSTRAINT [PK_IdParents1] PRIMARY KEY  CLUSTERED
 (
  [Id]
 )
)

GO
INSERT INTO IdParents1 ( Val ) values ( 'First' )
INSERT INTO IdParents1 ( Val ) values ( 'Third' )
GO

CREATE TABLE [IdParents2] (
 [Id] [int] IDENTITY (2, 2) NOT NULL ,
 [Val] [char] (10) NOT NULL ,
 CONSTRAINT [PK_IdParents2] PRIMARY KEY  CLUSTERED
 (
  [Id]
 )
)

GO

INSERT INTO IdParents2 ( Val ) values ( 'Second' )
INSERT INTO IdParents2 ( Val ) values ( 'Fourth' )
GO

SELECT * FROM  [IdParents1]
UNION ALL
SELECT * FROM  [IdParents2]
GO

BEGIN


 UNION ALL


 ELSE


END
GO

CREATE TABLE Mytable ( Id INT NOT NULL CHECK ( dbo.fn_ParentExists (Id) =
1 ), Val CHAR(10) )
GO

--Test Data
-- Valid Values
INSERT INTO Mytable  ( Id, Val ) values ( 1, 'One' )
INSERT INTO Mytable  ( Id, Val ) values ( 2, 'Two' )

-- Invalid Values
INSERT INTO Mytable  ( Id, Val ) values ( 5, 'Five' )

--Server: Msg 547, Level 16, State 1, Line 1
--INSERT statement conflicted with COLUMN CHECK constraint
'CK__Mytable__Id__3587F3E0'. The conflict occurred in database 'MyDb', table
'Mytable', column 'Id'.
--The statement has been terminated.

John

Quote:> I have ONE FIELD in the child refering to 2 parents. So,
> for me to enter a value in this child fk field, the value
> must exist in BOTH parent. Otherwise I get a fk violation
> error message. OK.

> However this is what I'm try to do:

> If "some_other_field" in the child table = 'X' only
> consider child_parent1_fk constraint

> if "some_other_field" in the child table = 'Y' only
> consider child_parent2_fk constraint

> In other word I have a condition that will dictate if the
> inserted value need to exist IN parent1 OR the parent2
> table.

> I hope I didn't make too confusing.

> Following are the 2 parents and child table I'm using as
> an example. Any help is truly apprecited.

> -- The PARENT1 table

> CREATE TABLE TABLE_LOOKUP_1 (
> lk1col1 int not null,
> lk1col2 varchar(25) not null,
> primary key (lk1col1))

> insert into table_lookup_1
> values (1, 'any value')

> --The PARENT2 table

> CREATE TABLE TABLE_LOOKUP_2 (
> lk2col1 int not null,
> lk2col2 varchar(25) not null,
> primary key (lk2col1))

> insert into table_lookup_2
> values (2, 'any value')

> --The CHILD table

> CREATE TABLE T1 (
> COL1 int not null,
> COL2 int not null,
> COL3 varchar(25) not null,
> PRIMARY KEY (COL1),
> FOREIGN KEY (col2)
> REFERENCES TABLE_LOOKUP_1,
> FOREIGN KEY (col2)
> REFERENCES TABLE_LOOKUP_2
> )
> GO

> insert into T1
> values (1,1,'any value') -- I get fk violation because 1
> only exist in one parent

Quote:> It seems to me that the way you suggested, would work only
> if the fk could be validated agains ANY parent.

CREATE TABLE LOOKUP /*** A new table ***/ (lkcol1 INTEGER, some_other
CHAR(1) CHECK (some_other IN ('X','Y')), PRIMARY KEY (lkcol1,some_other))

CREATE TABLE TABLE_LOOKUP_1 (lk1col1 INTEGER NOT NULL, some_other CHAR(1)
NOT NULL CHECK (some_other='X'), PRIMARY KEY (lk1col1), FOREIGN KEY
(lk1col1,some_other) REFERENCES LOOKUP (lkcol1,some_other))

CREATE TABLE TABLE_LOOKUP_2 (lk2col1 INTEGER NOT NULL, some_other CHAR(1)
NOT NULL CHECK (some_other='Y'), PRIMARY KEY (lk2col1), FOREIGN KEY
(lk2col1,some_other) REFERENCES LOOKUP (lkcol1,some_other))

CREATE TABLE T1 (
 col1 INTEGER PRIMARY KEY,
 col2 INTEGER NOT NULL,
 col3 VARCHAR(25) NOT NULL,
 some_other CHAR(1) NOT NULL,
 FOREIGN KEY (col2,some_other) REFERENCES LOOKUP (lkcol1,some_other),
 FOREIGN KEY (col2,some_other) REFERENCES LOOKUP (lkcol1,some_other))

Some_other is the column which determines which LOOKUP table applies. LOOKUP
is a new table which exists just to pull the FK constraints together.

--
David Portas
------------
Please reply only to the newsgroup
--

Quote:> In general this is not a valid normalised design.

--
David Portas
------------
Please reply only to the newsgroup
--

When DRI (declaritive referential integrity) gets beyond SQL's abilities,
you can drop back to the old method and just use a trigger.  In your
case an AFTER INSERT. In the trigger if you see anything that violates
integrity you issue a ROLLBACK.

Regards
 AJ

--This function is good but I'm trying to split in 2 so I -
--can enforce this business logic:
--if value=x use CHECK CONSTRAINT1
--if value=y use CHECK CONSTRAINT2
--I don't know how to make this work. Can you please help -
--me?

BEGIN



 ELSE


END
GO

BEGIN



 ELSE


END
GO

CREATE TABLE Mytable (
Id INT NOT NULL CHECK ( dbo.fn_ParentExists1 (Id)=1) if
(val2)='x',
CHECK ( dbo.fn_ParentExists2 (Id)=1) if (val2)='y',
Val CHAR(10),
Val2 char(2) )
GO

Quote:>1 ), Val CHAR(10) )
>GO

>--Test Data
>-- Valid Values
>INSERT INTO Mytable  ( Id, Val ) values ( 1, 'One' )
>INSERT INTO Mytable  ( Id, Val ) values ( 2, 'Two' )

>-- Invalid Values
>INSERT INTO Mytable  ( Id, Val ) values ( 5, 'Five' )

>--Server: Msg 547, Level 16, State 1, Line 1
>--INSERT statement conflicted with COLUMN CHECK constraint
>'CK__Mytable__Id__3587F3E0'. The conflict occurred in

--I was able to add these 2 constraint to the table. But --
--now it only accept the value if match the logic of both -
--constraint.

ALTER TABLE mytable
ADD CONSTRAINT constX
CHECK ((dbo.fn_ParentExists1 (Id)=1) and (val2 ='x'))

ALTER TABLE mytable
ADD CONSTRAINT constY
CHECK ((dbo.fn_ParentExists2 (Id)=1) and (val2 ='y'))

Quote:>1 ), Val CHAR(10) )
>GO

>--Test Data
>-- Valid Values
>INSERT INTO Mytable  ( Id, Val ) values ( 1, 'One' )
>INSERT INTO Mytable  ( Id, Val ) values ( 2, 'Two' )

>-- Invalid Values
>INSERT INTO Mytable  ( Id, Val ) values ( 5, 'Five' )

>--Server: Msg 547, Level 16, State 1, Line 1
>--INSERT statement conflicted with COLUMN CHECK constraint
>'CK__Mytable__Id__3587F3E0'. The conflict occurred in

GO
INSERT INTO IdParents1 ( Val ) values ( 'First' )
INSERT INTO IdParents1 ( Val ) values ( 'Third' )
GO

CREATE TABLE [IdParents2] (
 [Id] [int] IDENTITY (2, 2) NOT NULL ,
 [Val] [char] (10) NOT NULL ,
 CONSTRAINT [PK_IdParents2] PRIMARY KEY  CLUSTERED
 (
  [Id]
 )
)

GO

INSERT INTO IdParents2 ( Val ) values ( 'Second' )
INSERT INTO IdParents2 ( Val ) values ( 'Fourth' )
GO

SELECT * FROM  [IdParents1]
UNION ALL
SELECT * FROM  [IdParents2]
GO

BEGIN


 UNION ALL


 ELSE


END
GO

CREATE TABLE Mytable ( Id INT NOT NULL, Val CHAR(10) ,
CONSTRAINT FK_ParentCheck CHECK ( dbo.fn_ParentExists (Id, val) = 1) )
GO

--Test Data
-- Valid Values
INSERT INTO Mytable  ( Id, Val ) values ( 1, 'First' )
INSERT INTO Mytable  ( Id, Val ) values ( 2, 'Second' )

-- Invalid Values
INSERT INTO Mytable  ( Id, Val ) values ( 5, 'Fifth' )
INSERT INTO Mytable  ( Id, Val ) values ( 1, 'One' )

--Server: Msg 547, Level 16, State 1, Line 1
--INSERT statement conflicted with TABLE CHECK constraint 'FK_ParentCheck'.
The conflict occurred in database 'Test', table 'Mytable'.
--The statement has been terminated.
--Server: Msg 547, Level 16, State 1, Line 1
--INSERT statement conflicted with TABLE CHECK constraint 'FK_ParentCheck'.
The conflict occurred in database 'Test', table 'Mytable'.
--The statement has been terminated.

John

Quote:> > In general this is not a valid normalised design.

> Helena's spec is rather obscure so it *could* be non-normalised but it
> needn't necessarily be so. It's just that developers often forget to
create
> the table for the "super-entity" that makes sense of this (not uncommon)
> requirement. Creating the table and the extra constraints means you don't
> need to resort to UDFs.

But that may not be a possible, so failing that I can't see a way other than
using a UDF,

John

Quote:> --
> David Portas
> ------------
> Please reply only to the newsgroup
> --

As John pointed out, you can also use a trigger, and this is the
way to go:

Here is a outline of a trigger

   CREATE TRIGGER tbl_ins_upd_tri FOR INSERT, UPDATE AS

      IF EXISTS (SELECT *
                 FROM   inserted i
                 WHERE  i.refcol IS NOT NULL
                 AND    NOT EXISTS (SELECT *
                                    FROM   (SELECT keycol
                                            FROM   reftbl1
                                            UNION
                                            SELECT keycol
                                            FROM   reftbl2) AS ref
                                    WHERE   ref.keycol = i.refcol)
      BEGIN
         ROLLBACK TRANSACTION
         RAISERROR('Attempt to refer violate FK relation', 16, 1)
         RETURN
      END

--

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Few simple questions:

1-Create a super-entity is to combine parent1 and parent2
table?

2-Create a super-entity, it's the same relationship called
supertype and subtype?

Helena

Quote:>> need to resort to UDFs.

>I thought that was the point I was making. If the

Quote:>you can get a neater and more eloquent solution.

>But that may not be a possible, so failing that I can't

Quote:>using a UDF,

>John

>> --
>> David Portas
>> ------------
>> Please reply only to the newsgroup
>> --

>.

--
David Portas
------------
Please reply only to the newsgroup
--