Home » SQL & PL/SQL » SQL & PL/SQL » finding default constraint (oracle 10g)
finding default constraint [message #409678] Tue, 23 June 2009 08:08 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Hi,
how to find the default constraint name on atbale in oracle using a stored procedure.
this i have achieved in sql server 2005 like
IF EXISTS (SELECT name FROM sysobjects WHERE  name  = 'SP_DROP_DEFAULT_CONSTRAINT' AND type = 'P')
DROP PROCEDURE SP_DROP_DEFAULT_CONSTRAINT
GO

CREATE PROCEDURE SP_DROP_DEFAULT_CONSTRAINT 
(
	@table_name sysname, 
	@column_name sysname
)
AS

declare @default_constraint_name sysname, @sql nvarchar(max)

if exists (
	select * 
	from sys.default_constraints 
	where 
		parent_object_id = OBJECT_ID(@table_name)
		AND type = 'D'
		AND parent_column_id = (
			select column_id 
			from sys.columns 
			where 
			object_id = OBJECT_ID(@table_name)
			and name = @column_name
	 )
)
begin
	select @default_constraint_name = name 
	from sys.default_constraints 
		where 
			parent_object_id = OBJECT_ID(@table_name)
			AND type = 'D'
			AND parent_column_id = (
				select column_id 
				from sys.columns 
				where 
				object_id = OBJECT_ID(@table_name)
				and name = @column_name
			)

	SET @sql = N'ALTER TABLE ' + @table_name + ' DROP Constraint ' + @default_constraint_name
        exec sp_executesql @sql
	select @sql = 'ALTER TABLE [' + @table_name + '] ADD CONSTRAINT DF_' + @table_name + '_' + @column_name + ' DEFAULT (0) FOR [STATUS]'
        execute sp_executesql @sql
end
GO


Re: finding default constraint [message #409679 is a reply to message #409678] Tue, 23 June 2009 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Default constraint is not a notion that exists in Oracle.
Explain what it is.

Regards
Michel
Re: finding default constraint [message #409683 is a reply to message #409679] Tue, 23 June 2009 08:19 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
--for sql server
ALTER TABLE [dbo].[EMRAllergies] ADD
	CONSTRAINT [DF__EMRAllerg__STATU__7B7B4DDC] DEFAULT (1) FOR [STATUS],
	CONSTRAINT [PK_EMRAllergies] PRIMARY KEY  CLUSTERED
	(
		[ALLERGIC_ID]
	)  ON [PRIMARY]
GO

--for oracle
ALTER TABLE  EMRAllergies    ADD 
	CONSTRAINT  "PK_EMRAllergies"  PRIMARY KEY    
	(
		 ALLERGIC_ID 
	)     
/



like in sql server i am adding a constraint on the table
Re: finding default constraint [message #409684 is a reply to message #409683] Tue, 23 June 2009 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All constraints parameters are in DBA/ALL/USER_CONSTRAINTS and DBA/ALL/USER_CONS_COLUMNS.

Regards
Michel
Re: finding default constraint [message #409685 is a reply to message #409678] Tue, 23 June 2009 08:44 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Setting default value on column is not considered as a constraint by Oracle, it is a simple property of that column.
Even for me it does not make sense - default value does not restrict anything as other constraints do. However I am not sure if I did not misunderstand the question.

Anyway, setting default value on column looks like
ALTER TABLE dbo.EMRAllergies MODIFY (STATUS DEFAULT 1);

Alternatively the default value may be set at the time of table creation:
CREATE TABLE dbo.EMRAllergies (STATUS number DEFAULT 1);
Re: finding default constraint [message #409691 is a reply to message #409684] Tue, 23 June 2009 09:00 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Default values on column can be found in DBA/ALL/USER_TAB_COLUMNS views.

Regards
Michel
Previous Topic: Problem in generating XML
Next Topic: CREATE TABLE s_dept gets ORA-00900 ERR
Goto Forum:
  


Current Time: Sat Dec 03 20:23:15 CST 2016

Total time taken to generate the page: 0.07876 seconds