to prevent duplicate values - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

to prevent duplicate values

Author

Topic

krasnokojiy
Starting Member

18 Posts

Posted - 2008-02-23 : 07:35:12

Dear friends !! i have a problem so:
i have created a database about questions ,one question has five choices , questions and choices are placed in different tables ;
in the table which choices are placed , i inserted a column to determine the answer (field type is bit) and i want this field to take 'true' value just one time for each question, but i couldnt do it therefore i appeal help from friends who have knowledge about this subject, thanks from now on..

Note:Normally no problems with table(i can insert and update value) but someone can insert 'true' value more time by mistake or unawares
thus the question has more answer..

MC

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-23 : 08:22:37

Are you asking:
If a question has more than one "true" answer, how do you just return one of them?
If that is your question, do you care which correct answer you return?

Please post the table structure of the tables that contain questions and choices so someone can offer a solution.

Be One with the Optimizer
TG

krasnokojiy
Starting Member

18 Posts

Posted - 2008-02-23 : 09:00:23

No i want just one answer for each question but to prevent adding more answer by mistake
here are my tables structures :

CREATE TABLE [dbo].[tblQuestions](
[QID] [int] IDENTITY(1,1) NOT NULL,
[CLASS] [nchar](10) COLLATE Turkish_CI_AS NOT NULL,
[UNIT] [nchar](10) COLLATE Turkish_CI_AS NOT NULL,
[SESSION] [nchar](10) COLLATE Turkish_CI_AS NOT NULL,
[CONTENT] [nvarchar](500) COLLATE Turkish_CI_AS NOT NULL,
CONSTRAINT [PK_tblQuestions] PRIMARY KEY CLUSTERED
)

CREATE TABLE [dbo].[tblCHOICES](
[ChID] [int] IDENTITY(1,1) NOT NULL,
[QID] [int] NOT NULL,
[LETTER] [nchar](1) COLLATE Turkish_CI_AS NOT NULL,
[CONTENT] [nvarchar](500) COLLATE Turkish_CI_AS NOT NULL,
[STATUS] [bit] NOT NULL,
CONSTRAINT [PK_tblCHOICES] PRIMARY KEY CLUSTERED
(
[ChID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tblCHOICES] WITH CHECK ADD CONSTRAINT [FK_QID] FOREIGN KEY([QID])
REFERENCES [dbo].[tblQuestions] ([QID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblCHOICES] CHECK CONSTRAINT [FK_QID]
GO
ALTER TABLE [dbo].[tblCHOICES] WITH CHECK ADD CONSTRAINT [CK_Letter] CHECK (([Letter]='E' OR [Letter]='D' OR [Letter]='C' OR [Letter]='B' OR [Letter]='A'))
GO
ALTER TABLE [dbo].[tblCHOICES] CHECK CONSTRAINT [CK_Letter]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-23 : 09:34:55

I would do two things:
1)
Add a unique constraint on letter to prevent multiple version of the same choice.
(maybe one on [content] as well)

2)
Use a function as the CHECK constraint on Status to prevent more than 1 correct answer:


go
alter table tblChoices add constraint UQ_Letter unique ([LETTER])
go
create function dbo.fn_OnlyOneCorrectAnswer(@QID int, @CHID int, @status bit) 
returns bit
as
begin
	if @status = 1
	begin
		if exists (select 0 
			from tblChoices 
			where qid = @qid
			and chid <> @chid
			and status = 1)
		begin
			return 0
		end
	end
	return 1
end
go

--add the constraint
alter table tblChoices add constraint CK_Status CHECK (dbo.fn_OnlyOneCorrectAnswer(QID, CHID, status) = 1)
go

Be One with the Optimizer
TG

krasnokojiy
Starting Member

18 Posts

Posted - 2008-02-23 : 10:27:08

thanks a lot for this i practise this function and i took what i want
but your first offer about using unique constraint for letter column ;
i dont understand why
if i use Unique Key Constraint for Letter column, i can insert choices for the first question only but not for second, cant i?

krasnokojiy
Starting Member

18 Posts

Posted - 2008-02-23 : 10:41:07

besides ,if this will not be extra for you ; i have another question about this database

i have a stored procedure running this query;

SELECT L.QID ,L.CLASS,L.SESSION,L.UNIT,L.CONTENT,K.LETTER,K.CONTENT FROM
tblQuestions L JOIN tblCHOICES K ON L.QID = K.QID

then i take this resultset;

QID CLASS SESSION UNIT QUESTION LETTER CHOICE
32 7.CLASS 2.SESSION 2.UNIT asdadasdasd A ffffff
32 7.CLASS 2.SESSION 2.UNIT asdadasdasd B lllll
32 7.CLASS 2.SESSION 2.UNIT asdadasdasd C kkkkk
32 7.CLASS 2.SESSION 2.UNIT asdadasdasd D mmmmm
32 7.CLASS 2.SESSION 2.UNIT asdadasdasd E fgggg

but i want to take resultset so ;

QID CLASS SESSION UNIT QUESTION LETTER CHOICE
32 7.CLASS 2.SESSION 2.UNIT asdadasdasd A,B,C,D,E ffffff,Kelime,Cevap,Küçük,Büyük

WHAT I HAVE TO DO TO TAKE THIS RESULTSET?

Thanks from now on..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-23 : 13:22:14

Have answered it here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97796

krasnokojiy
Starting Member

18 Posts

Posted - 2008-02-23 : 15:24:22

thanks a lot

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-23 : 20:22:31

quote:
Originally posted by krasnokojiy

but your first offer about using unique constraint for letter column ;
i dont understand why
if i use Unique Key Constraint for Letter column, i can insert choices for the first question only but not for second, cant i?

oh yes - of course you're right. You would need to make the unique constraint for (QID,Letter)

Be One with the Optimizer
TG

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-02-25 : 19:55:23

Wouldn't it make more sense to have the ChID of the correct answer in the question table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources