One Row Only

Captain Ramius: Re-verify our range to target... one ping only.

Capt. Vasili Borodin: Captain, I - I - I just...

Captain Ramius: Give me a ping, Vasili. One ping only, please.

Capt. Vasili Borodin: Aye, Captain.

Here's a snippet of code from the SQL Anywhere database setup script for a small application. The settings table is a place to store global configuration parameters separate from the application code:

CREATE TABLE settings (
   one_row_only   INTEGER NOT NULL DEFAULT 1 
                     CHECK ( one_row_only = 1 )  
                     PRIMARY KEY,
   no_picture_yet_image_folder     LONG VARCHAR NOT NULL,
   no_picture_yet_image_file_name  LONG VARCHAR NOT NULL );

CREATE TRIGGER one_row_only BEFORE DELETE ON settings
FOR EACH ROW
BEGIN
   ROLLBACK TRIGGER WITH RAISERROR 99999 
      'Do not attempt to delete from settings';
END;

INSERT settings VALUES (
   DEFAULT,
   'C:\\Inetpub\\images',  
   'no_picture_yet.jpg' );  
COMMIT;

Once this code's been run, the settings table is available for SELECT and UPDATE but the following operations are impossible:

• inserting another row (the CHECK and PRIMARY KEY constraints),

• changing the primary key value (the CHECK constraint), or

• deleting the row (the TRIGGER).

INSERT settings VALUES ( 1, 'Hello', 'World' ); -- primary key violation
INSERT settings VALUES ( 2, 'Hello', 'World' ); -- check condition is false
UPDATE settings SET one_row_only = 3; -- check condition is false
DELETE settings; -- trigger catches it 

SELECT * FROM settings;

one_row_only,no_picture_yet_image_folder,no_picture_yet_image_file_name
1,'C:\\Inetpub\\images','no_picture_yet.jpg'

UPDATE settings SET no_picture_yet_image_file_name = 'future.jpg';

one_row_only,no_picture_yet_image_folder,no_picture_yet_image_file_name
1,'C:\\Inetpub\\images','future.jpg'

In other words, there will always be exactly one row in this table, and that fact is guaranteed by the the database itself... perfect for a global configuration settings table.