Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

How do I enforce that only a single row exists in an SQL table?

Hi.

I have a control record which contains data which only occurs once in the system (session duration, for example).

I want to have a single row in a table.

How can I enforce this? I'd rather not play around with permissions - simply as I don't fully get all the nuances/levels of security, nor do I know who will have access to the data (I'm the developer, not the user/installer).

So, I thought a constraint would be perfect.

I've got a identity column.

Is there a way to never allow the value for the identity to be anything other than 1?

Or is there another way to enforce a single row for a table?

Hopefully a simple enough question...

Using MS SQL 2005.
0
Richard Quadling
Asked:
Richard Quadling
  • 9
  • 5
  • 2
  • +2
3 Solutions
 
chapmandewCommented:
Put a trigger on the table and ensure that there is only one row.  Put your row in the table, and in an insert trigger, roll back the transaction.  Same with the delete.  so...

create trigger mytrigger
on mytable
for insert, delete
as
begin
rollback transaction
begin transaction  --order important here.
end
0
 
chapmandewCommented:
actually, it would be better to use an instead of...

create trigger mytrigger
on mytable
INSTEAD OF insert, delete
as
begin
PRINT 'no operation completed'
end
0
 
JohnSansomCommented:
Hi,

First create a Primary Key consraint on you row identifier column so that your table can only support unique key values.

Then you could implement your desired logic by using an INSTEAD OF INSERT TRIGGER.

See:
http://msdn.microsoft.com/en-us/library/ms175089.aspx

Regards,
John
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
rajvjaCommented:
Or else,

    Before insert, check that

IF NOT EXISTS(select ....)
begin
  'insert statement
end
0
 
chapmandewCommented:
No, the coding check that way is not the best way to do this...
0
 
Richard QuadlingSenior Software DeverloperAuthor Commented:
Thanks for the trigger idea.

I looked around the GUI for creating a table and saw the constraint option.

I entered UniqueID=1 and ran a test ...

And it worked!

In the end I tried this ...

CREATE TABLE [dbo].[Control](
      [UniqueID] [int] IDENTITY(1,1) NOT NULL,
      [SessionDuration] [int] NOT NULL,
 CONSTRAINT [PK_Control] PRIMARY KEY CLUSTERED
(
      [UniqueID] 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].[Control]  WITH CHECK ADD  CONSTRAINT [CK_Control] CHECK  (([UniqueID]=(1)))
GO
ALTER TABLE [dbo].[Control] CHECK CONSTRAINT [CK_Control]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Singe row only' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Control', @level2type=N'CONSTRAINT',@level2name=N'CK_Control'

I now have either no rows or a single row.

Trying to insert a second row ...

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_Control". The conflict occurred in database "CAMELOT", table "dbo.Control", column 'UniqueID'.
The statement has been terminated.

Which is fine for me.

0
 
chapmandewCommented:
Butif you enter another row and do not supply a uniqueid, it is entered...no violation

insert into control(sessionduration)
select 5
0
 
JohnSansomCommented:
chapmandew is correct and this is why I suggested in the second post using both a Primary Key constraint and a Trigger to implement the desited behaviour.
0
 
Richard QuadlingSenior Software DeverloperAuthor Commented:
I think you've missed that the UniqueID column is an Identity column.

truncate table Control -- Remove all the data and reset the identity
INSERT INTO Control(SessionDuration) VALUES(300) -- UniqueID = 1 now exists.
INSERT INTO Control(SessionDuration) VALUES(300) -- Cannot insert another row.
SELECT * FROM Control

outputs ...

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the CHECK constraint "CK_Control". The conflict occurred in database "CAMELOT", table "dbo.Control", column 'UniqueID'.
The statement has been terminated.

(1 row(s) affected)

and

UniqueID SessionDuration
1      300


INSERT INTO Control(UniqueID, SessionDuration) VALUES(5,300)

Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'Control' when IDENTITY_INSERT is set to OFF.


So, for me this LOOKS like it is working, but I'm not an expert in this area.

0
 
chapmandewCommented:
No no no...you can still enter records into the table...just as I showed before.  It is an identity column, so if you enter another record, the value will just increment.  

The only way you can enforce that only 1 record is in the table is through a trigger.
0
 
Snarf0001Commented:
But he also added a check constraint (= 1) against the column, so even though it is an identity, any further attempts to insert will violate the check and fail.

It should work fine.
0
 
chapmandewCommented:
good call....I missed that.  :)
0
 
Richard QuadlingSenior Software DeverloperAuthor Commented:
Nope! I'm not seeing that ...

TRUNCATE TABLE Control
INSERT INTO Control(SessionDuration) VALUES(1)
INSERT INTO Control(SessionDuration) VALUES(2)
INSERT INTO Control(SessionDuration) VALUES(3)
INSERT INTO Control(SessionDuration) VALUES(4)
SELECT * FROM Control

results in ...


(1 row(s) affected)
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the CHECK constraint "CK_Control". The conflict occurred in database "CAMELOT", table "dbo.Control", column 'UniqueID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the CHECK constraint "CK_Control". The conflict occurred in database "CAMELOT", table "dbo.Control", column 'UniqueID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 5
The INSERT statement conflicted with the CHECK constraint "CK_Control". The conflict occurred in database "CAMELOT", table "dbo.Control", column 'UniqueID'.
The statement has been terminated.

(1 row(s) affected)


and 1 row in the result set of ...

UniqueID SessionDuration
1      1

The check constraint seems to be working.

The constraint says the UniqueID column must have a value of 1. The identity(1,1) says start at 1 and increment, so every insert after the first will NOT be 1 so should be rejected.

Even with IDENTITY_INSERT set this should work.

With this constraint, I am seemingly unable to enter new records.

Every attempt (other than the first) is getting the error and "The statement has been terminated.".

0
 
chapmandewCommented:
You're spot on...I missed the check constraint.  nice work!
0
 
Richard QuadlingSenior Software DeverloperAuthor Commented:
Well. It seems my tests are successful (so far).

My question was about other ways and I didn't even comment on those, sorry.

But, the trigger would seem to be a fine approach on a table without the Identity column. I don't actually NEED the identity, so maybe I could/should go with the trigger approach.

My only concern is that the row must be created first ...

Unless an INSTEAD OF INSERT is used in conjunction with the trigger testing to see if the row already exists.

But using an INSERT on a table with an INSTEAD OF INSERT trigger, looks, on the surface at least, to be a recursive headache.


0
 
chapmandewCommented:
In that situation, I usually enter the record first and then put the trigger on the table.

To be honest, I like your approach better than the trigger approach.  :)
0
 
Richard QuadlingSenior Software DeverloperAuthor Commented:
How should I close this question?
0
 
chapmandewCommented:
however you wanna...doesn't matter to me.  
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 9
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now