writing a constraint - cant figure out the query

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

Transact-SQL (2000)

writing a constraint - cant figure out the query

Author

Topic

numlocked
Starting Member

2 Posts

Posted - 2006-04-11 : 22:11:57

Hey everyone, I'm pretty new to sql and I'm having some trouble coding the query to enforce a constraint. Here's the deal:

I have two tables, one called 'subjects' with fields 'path' and 'displayname', the other table is 'subjectrelations' with fields 'parent' and 'child'. parent and child fields are foreign keys to 'path'

I need to create a query that returns a result only when two children of the same parent have the same displayname.

I cannot for the life of me figure out how to do this.

I've tried a myriad of solutions, but have been unable to figure this out. I would really, really appreciate any help. Please also let me know if you need any more information (though I'm pretty sure this is enough).

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-11 : 23:18:18

"I need to create a query that returns a result only when two children of the same parent have the same displayname."
Can you elaborate more on this ? Preferably with some sample data.

Also try this

select *
from   subjectrelations c1 inner join subjectrelations c2
       on    c1.parent = c2.parent
       inner join path p
       on    c1.parent = p.path

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-04-12 : 06:39:01

Are you trying to run a single query that checks all existing data to see if the constraint is violated? Or are you looking for a query you can use in a stored procedure to perform a check before trying insert/update?

I threw a couple of things togethee. Hopefully, they help.

Here is the set up:

CREATE TABLE dbo.Subjects (
	Path int,
	DisplayName varchar (50)
)

CREATE TABLE SubjectRelations (
	Parent int,
	Child int
)

INSERT Subjects
SELECT 1, 'Name1'
UNION ALL SELECT 2, 'Name2'
UNION ALL SELECT 3, 'Name2' --Duplicate
UNION ALL SELECT 4, 'Name4'
UNION ALL SELECT 5, 'Name5'
UNION ALL SELECT 6, 'Name6'

INSERT SubjectRelations
SELECT 1, 2
UNION ALL SELECT 1, 3
UNION ALL SELECT 4, 5
UNION ALL SELECT 4, 6

First here is a query that I think will get all duplicates:

SELECT Parent, DisplayName, COUNT(*)
FROM Subjects
INNER JOIN SubjectRelations
ON Path = Child
GROUP BY Parent, DisplayName
HAVING COUNT(*) > 1

Here is some other queries that might help:

DECLARE @Parent INT
DECLARE @DisplayName VARCHAR(50)
DECLARE @Child INT

SET @Parent = 1
SET @DisplayName = 'Name2'
SET @Child = 2

-- Test for Insert: if COUNT is greater than 0, constraint violated.
SELECT 
	COUNT(DisplayName)
FROM 
	Subjects s
INNER JOIN
	SubjectRelations sr1
	ON s.Path = sr1.Child
WHERE
	sr1.Parent = @Parent
	AND s.DisplayName = @DisplayName

-- Test for Update: if COUNT is greater than 0, constraint violated.
SELECT 
	COUNT(DisplayName)
FROM 
	Subjects s
INNER JOIN
	SubjectRelations sr1
	ON s.Path = sr1.Child
WHERE
	sr1.Parent = @Parent
	AND s.DisplayName = @DisplayName
	AND s.Path <> @Child

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources