How to "hide" a row in Oracle SQL database but not delete it-CodePudding

Is there a way to "hide" a row in Oracle SQL database without deleting it, So that it is still in the database but does not show up on the webpage as a search result?

For example, I want to hide a specific city in my database from being displayed in the results when searching for cities on the webpage.

CodePudding user response：

Add a column to the table with a flag as to whether to show or hide the row and then add a WHERE filter to your query to filter out rows where the row should be hidden.

If you have the table:

CREATE TABLE cities (name) AS
SELECT 'Berlin'    FROM DUAL UNION ALL
SELECT 'Lisbon'    FROM DUAL UNION ALL
SELECT 'Prague'    FROM DUAL UNION ALL
SELECT 'Amsterdam' FROM DUAL UNION ALL
SELECT 'Kiev'      FROM DUAL;

Then you can do something like:

ALTER TABLE cities
  ADD visibility NUMBER(1,0)
                 INVISIBLE
                 DEFAULT 1
  CONSTRAINT cities__visibility__chk CHECK (visibility IN (0,1));

(Note: INVISIBLE will cause a column not to be shown when you use SELECT * FROM cities, it can still be shown if you name the columns SELECT name, visibility FROM cities.)

Then set a row to not be visible:

UPDATE cities
SET    visibility = 0
WHERE  name = 'Amsterdam';

Then:

SELECT *
FROM   cities
WHERE  visibility = 1;

Outputs:

NAME

Berlin

Lisbon

Prague

Kiev

NAME
Berlin
Lisbon
Prague
Kiev

db<>fiddle here