Restrict table to one single row

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.
canuckconsultingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rajkumar GsSoftware EngineerCommented:
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
chapmandewCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Rajkumar GsSoftware EngineerCommented:
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
chapmandewCommented:
Still a very poor choice.
0
Rajkumar GsSoftware EngineerCommented:
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
Rajkumar GsSoftware EngineerCommented:
chapmandew,

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

I think, 1st one also should work.

Raj
0
Rajkumar GsSoftware EngineerCommented:
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
Rajkumar GsSoftware EngineerCommented:
Oh. Missed a word.

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

Raj
0
jgarzonCommented:
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
Anthony PerkinsCommented:
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
canuckconsultingAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.