?
Solved

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

Posted on 2010-01-11
18
Medium Priority
?
346 Views
Last Modified: 2012-05-08
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
Comment
Question by:Richard Quadling
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 2
  • +2
18 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 26283256
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
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 200 total points
ID: 26283266
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
 
LVL 3

Assisted Solution

by:JohnSansom
JohnSansom earned 200 total points
ID: 26283279
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 11

Expert Comment

by:rajvja
ID: 26283302
Or else,

    Before insert, check that

IF NOT EXISTS(select ....)
begin
  'insert statement
end
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26283326
No, the coding check that way is not the best way to do this...
0
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 0 total points
ID: 26283338
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 26283367
Butif you enter another row and do not supply a uniqueid, it is entered...no violation

insert into control(sessionduration)
select 5
0
 
LVL 3

Expert Comment

by:JohnSansom
ID: 26283395
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
 
LVL 40

Author Comment

by:Richard Quadling
ID: 26283669
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 26283692
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
 
LVL 23

Expert Comment

by:Snarf0001
ID: 26283763
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 26283797
good call....I missed that.  :)
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 26283799
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 26283807
You're spot on...I missed the check constraint.  nice work!
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 26283852
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 26283864
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
 
LVL 40

Author Comment

by:Richard Quadling
ID: 26283872
How should I close this question?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26283878
however you wanna...doesn't matter to me.  
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question