Help me with - Enforce relationship for INSERTs ..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help me with - Enforce relationship for INSERTs ..

Author

Topic

argon
Starting Member

3 Posts

Posted - 2005-09-15 : 13:23:01

Please help me to find a way ..
I have created a small example database with 4 tables.
You can download it's backup from here:
[url]http://www.anuntu.ro/anuntu.bkp[/url]

I know that you know how to restore-it.. however, in order to install it on your MS SQL 2000 server you need to chose from the "restore database menu" , the "From Device" and then select this anuntu.bkp file..

Now about the problem :

As U can see, there are 2 tables named combo1 and combo2 (because their values will feed some drop-down-boxes into a web page..)..
The PrimaryKeys of those tables are spread over two columns in order to be unique.. (I've inserted some data , and there are duplicate values.. so this "PK over 2" is the only way )

What I want is that "Enforce relationship for INSERTs and UPDATEs" TO WORK when:

1. when I modify the name of the domain from the table domainn (the PK has a useful-web-inteligible name ..) , I want to see it propagated into all the affected records from the tblmain , tblcombo1 and tblcombo2 .

and

2. when I modify any of the tblcombo1.combo1 or tblcombo2.combo2 values , the update-wind to propagate into all the affected records of the tblmain

I can make it to work in order to respect the condition 1. OR 2. but not BOTH TOGEDER !!! :)

Can you, imagine another way in order to Enforce relationship for INSERTs & UPDATEs ?

Once again, that db is here: http://www.anuntu.ro/anuntu.bkp

THANK you !

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-15 : 13:29:31

If you really want us to help you, please provide the DDL for your database schema rather than the database backup as I doubt anyone will be restoring your database into their environments. You can easily generate the DDL via Enterprise Manager's Generate SQL script wizard.

Tara

argon
Starting Member

3 Posts

Posted - 2005-09-15 : 18:37:15

You are right! I'm sorry , here is the sql code:



CREATE TABLE [dbo].[tbldomainn] (
	[domainn] [nvarchar] (25) COLLATE Romanian_CI_AS NOT NULL ,
	[combo1info] [nvarchar] (255) COLLATE Romanian_CI_AS NULL ,
	[combo2info] [nvarchar] (255) COLLATE Romanian_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblcombo1] (
	[combo1] [nvarchar] (25) COLLATE Romanian_CI_AS NOT NULL ,
	[domainn] [nvarchar] (25) COLLATE Romanian_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblcombo2] (
	[combo2] [nvarchar] (25) COLLATE Romanian_CI_AS NOT NULL ,
	[domainn] [nvarchar] (25) COLLATE Romanian_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblmain] (
	[IDpost] [int] IDENTITY (1, 1) NOT NULL ,
	[domainn] [nvarchar] (25) COLLATE Romanian_CI_AS NOT NULL ,
	[combo1] [nvarchar] (25) COLLATE Romanian_CI_AS NOT NULL ,
	[combo2] [nvarchar] (25) COLLATE Romanian_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbldomainn] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbldomainn] PRIMARY KEY  CLUSTERED 
	(
		[domainn]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblcombo1] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblcombo1] PRIMARY KEY  CLUSTERED 
	(
		[combo1],
		[domainn]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblcombo2] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblcombo2] PRIMARY KEY  CLUSTERED 
	(
		[combo2],
		[domainn]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblmain] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblmain] PRIMARY KEY  CLUSTERED 
	(
		[IDpost]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblcombo1] ADD 
	CONSTRAINT [FK_tblcombo1_tbldomainn] FOREIGN KEY 
	(
		[domainn]
	) REFERENCES [dbo].[tbldomainn] (
		[domainn]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[tblcombo2] ADD 
	CONSTRAINT [FK_tblcombo2_tbldomainn] FOREIGN KEY 
	(
		[domainn]
	) REFERENCES [dbo].[tbldomainn] (
		[domainn]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[tblmain] ADD 
	CONSTRAINT [FK_tblmain_tblcombo1] FOREIGN KEY 
	(
		[combo1],
		[domainn]
	) REFERENCES [dbo].[tblcombo1] (
		[combo1],
		[domainn]
	),
	CONSTRAINT [FK_tblmain_tblcombo2] FOREIGN KEY 
	(
		[combo2],
		[domainn]
	) REFERENCES [dbo].[tblcombo2] (
		[combo2],
		[domainn]
	),
	CONSTRAINT [FK_tblmain_tbldomainn] FOREIGN KEY 
	(
		[domainn]
	) REFERENCES [dbo].[tbldomainn] (
		[domainn]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

The same code you can download it from here:
http://www.anuntu.ro/anuntu.sql

and here is the DB's diagram gif:

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-15 : 18:44:06

I don't understand why your database design is this way. Could you explain the purpose of these tables and why the design is the way it is?

Cascading updates will allow you to update the child data when you only update the parent data via UPDATE statement. You can't update child data and have it cascade it to the parent. If you want this to happen, you'll have to handle this via triggers or handle it yourself in the app or via a stored procedure.

Tara

argon
Starting Member

3 Posts

Posted - 2005-09-17 : 15:19:23

It is for a posting (announces) web site.
There can be many posting domains.
Each one has it's specialities ; for example :

Doain = auto
you can sell or buy (combo1)
and you can choose among a dozen cars (Ford Opel Mazda etc ; this will be combo2)

for the

Domain = realestates
you can sell buy for-renting looking-a-rent (combo1)
and for combo2 there can be house appartment-1room apprtment-2-rooms etc

Now you gaot the picture..

Why I need those constraints ?
Well let's supose I misspeled the name of the domain "realeskakes" and I want to correct it to "realestates".. Or a car "Mazga" insted of "Mazda" ; It will be much easier for me if the "Enforce relationship for INSERTs and UPDATEs" will do the job.

And by the way , the relationship from that picture are not working in the way I've wanted.. I know..

That's why I've posted my question HERE !

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-18 : 22:02:19

hi argon,

I'm seeing redundancy, can you not include tbldomainn since combining tblcombo1 and tblcombo2 will give you the same table not to mention tblmain?

your naming convention is confusing, see if this works for you...

Domain={DomainID,DomainName}
Combo =(ComboID,ComboName}
DomainCombo=(PostID,DomainID,ComboID}

Your PK is the underlined field and you'll get foreign key on DomainID and ComboID for their respective table. Domain and Combo tables give you control over the domains and Combos that can exist and be picked up by DomainCombo

HTH

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources