[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Restrict table to one single row

Posted on 2010-03-27
12
Medium Priority
?
313 Views
Last Modified: 2012-05-09
I have a table named Settings which I use to store configurable application settings.  Is there a way to restrict the table to have only a single row?  The application is a .Net 3.5 c# app.
0
Comment
Question by:canuckconsulting
12 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 1600 total points
ID: 28819099
Sure...couple of ways. One way is to create an int col with the value 1 in it...make it the primary key. Then add a check constraint that the column must equal the value 1.  That way, no new records can be added because they'd have to be different than 1 and your constraints disallow it.

Another way is through a trigger.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 28827952
Another way.

Before save, always check the count of records in the table. If 0, then insert else update existing record.
This will keep maximum of 1 record.

Raj
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 28828311
Thats a poor way to do it though because there is nothing in the system enforcing the fact that there can only be 1 record in the table.  It is left to the developer...and that never works.  
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 28828337
Tnis is better to do in a stored procedure . so for a programmer calling this procedure, is no need to be aware of this check.

Raj
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 28828454
Still a very poor choice.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 28829253
Another way.

Always keep one record in the table with default values or NULL value.
Then always do updation.

As I mentioned before, sql queries are better to maintain inside stored procedure, so that developer need to just call it.

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 28829664
chapmandew,

These are my thoughts :),
the second one was implemented in our project and working.

I think, 1st one also should work.

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 28830161
chapmandew,

I have a doubt. You said,
>> no new records can be added because they'd have to be different than 1 and your constraints disallow it.

When user try to save, will it crash? If so will the new data will loss? (since constraint not allows this save)

canuckconsulting,
Do you final settings that user saves?
Or just one save?

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 28830847
Oh. Missed a word.

@canuckconsulting,
Do you want the settings that user saves finally ?
Or just one save?

Raj
0
 
LVL 3

Assisted Solution

by:jgarzon
jgarzon earned 200 total points
ID: 28835587
I'd approach this by using a trigger that will raise an exception if there is an attempt to insert more than one row.. the exception can also have the text that will help the caller understand that there should only be one record.
This way you cannot bypass the check wether you are using a sproc, straight sql or whatever
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 28888583
Have you considered that using a table may be a bad choice in the first place and perhaps it should be relegated to an app.config file.
0
 

Author Closing Comment

by:canuckconsulting
ID: 31708080
chapmandew: This is what I was looking for; thank you!

acperkins: I had considred this but while the configuration settings are to control a Windows .Net application the changing of the settings is done via a web interface.  The web server may not be on the same system as the web application.  I think this is a very good point though and normally one that I would follow!

RajkumarGS: I was looking for a way for the db to restrict the table to a single row.  So if someone (developer or someone directly fiddling in the db) makes a mistake the db will not allow another row to exist.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

590 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