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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
Raj
Still a very poor choice.
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
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
chapmandew,
These are my thoughts :),
the second one was implemented in our project and working.
I think, 1st one also should work.
Raj
These are my thoughts :),
the second one was implemented in our project and working.
I think, 1st one also should work.
Raj
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
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
Oh. Missed a word.
@canuckconsulting,
Do you want the settings that user saves finally ?
Or just one save?
Raj
@canuckconsulting,
Do you want the settings that user saves finally ?
Or just one save?
Raj
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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