Home » SQL & PL/SQL » SQL & PL/SQL » table should allow to insert only one row (Oracle 11.2.0.1.0)
icon5.gif  table should allow to insert only one row [message #621696] Sat, 16 August 2014 10:25 Go to next message
ecivgamer
Messages: 145
Registered: May 2011
Location: Ukraine
Senior Member
Hi all,

my need is to create table that allows to insert only one row.

How do I perform it?


Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Re: table should allow to insert only one row [message #621703 is a reply to message #621696] Sat, 16 August 2014 10:48 Go to previous messageGo to next message
John Watson
Messages: 4858
Registered: January 2010
Location: Global Village
Senior Member
You could insert your row, and then make the table read only.
Re: table should allow to insert only one row [message #621704 is a reply to message #621703] Sat, 16 August 2014 10:49 Go to previous messageGo to next message
ecivgamer
Messages: 145
Registered: May 2011
Location: Ukraine
Senior Member
John Watson, thanks.

Here is another decision:

CREATE UNIQUE INDEX table_x_u ON table_x (NVL2 (col_1, 1, 1));
Re: table should allow to insert only one row [message #621705 is a reply to message #621704] Sat, 16 August 2014 10:57 Go to previous messageGo to next message
John Watson
Messages: 4858
Registered: January 2010
Location: Global Village
Senior Member
A good solution! I jusst came up with this really clumsy one:
orclz>
orclz> create table t1(c1 varchar(10),c2 number as (1));

Table created.

orclz> alter table t1 modify c2 invisible;

Table altered.

orclz> alter table t1 add constraint t1pk primary key (c2);

Table altered.

orclz> insert into t1 values('one row');

1 row created.

orclz> insert into t1 values('one row');
insert into t1 values('one row')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.T1PK) violated


orclz>
Re: table should allow to insert only one row [message #621707 is a reply to message #621696] Sat, 16 August 2014 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do you allow the row to be updated or deleted?

Re: table should allow to insert only one row [message #621709 is a reply to message #621705] Sat, 16 August 2014 11:25 Go to previous messageGo to next message
ecivgamer
Messages: 145
Registered: May 2011
Location: Ukraine
Senior Member
Michel Cadot,
yes, the row can be updated or deleted.
Re: table should allow to insert only one row [message #621711 is a reply to message #621696] Sat, 16 August 2014 11:33 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
I thought of following, https://community.oracle.com/message/4531345

P.S. Why aren't we asking the posters about what they tried and searched? What about the forum guidelines?

[Updated on: Sat, 16 August 2014 11:35]

Report message to a moderator

Re: table should allow to insert only one row [message #621714 is a reply to message #621711] Sat, 16 August 2014 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
P.S. Why aren't we asking the posters about what they tried and searched? What about the forum guidelines?


Because we do not do it systematically because in the end it is bothering. I don't see any problem there.
In the opposite, I see a problem when some remarks are done to someone that have already apologized or fulfilled the rules by himself. Wink
And here OP gave one solution.


[Updated on: Sat, 16 August 2014 11:59]

Report message to a moderator

Re: table should allow to insert only one row [message #621715 is a reply to message #621709] Sat, 16 August 2014 12:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ecivgamer wrote on Sat, 16 August 2014 18:25
Michel Cadot,
yes, the row can be updated or deleted.


OK in this case the main solutions have been given.

Re: table should allow to insert only one row [message #621716 is a reply to message #621715] Sat, 16 August 2014 12:05 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
https://community.oracle.com/thread/1121928?start=15&tstart=0
Re: table should allow to insert only one row [message #621721 is a reply to message #621704] Sat, 16 August 2014 13:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2078
Registered: January 2010
Senior Member
ecivgamer wrote on Sat, 16 August 2014 11:49
Here is another decision:


Or simpler:

CREATE UNIQUE INDEX table_x_u ON table_x(1);

SY.

[Updated on: Sat, 16 August 2014 13:41]

Report message to a moderator

Re: table should allow to insert only one row [message #621725 is a reply to message #621721] Sat, 16 August 2014 15:33 Go to previous message
John Watson
Messages: 4858
Registered: January 2010
Location: Global Village
Senior Member
That has to be the best solution. I've just tried to improve on it by using an IOT so that only one segment is needed, but I can't find a syntax that works.
Previous Topic: get hour value from sysdate in 24 hour format
Next Topic: What we should use for better function or procedure
Goto Forum:
  


Current Time: Thu Dec 18 19:42:23 CST 2014

Total time taken to generate the page: 0.10708 seconds