Drop unique - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Drop unique

Author

Topic

Bellus
Starting Member

29 Posts

Posted - 2007-10-15 : 06:36:29

How can I drop/delete unique to a column in a table?

tried with alter table table1 drop unique(column1);

but it didnt work out..

:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-15 : 06:40:30

Try CONSTRAINT.
Or ALTER TABLE ALTER COLUMN.

E 12°55'05.25"
N 56°04'39.16"

Bellus
Starting Member

29 Posts

Posted - 2007-10-15 : 06:44:40

How to use constraint, alter table - alter column didnt work (if I did it right...)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-15 : 06:50:17

Are you trying to drop the complete column, or just the UNIQUE index/constraint for the column?

E 12°55'05.25"
N 56°04'39.16"

Bellus
Starting Member

29 Posts

Posted - 2007-10-15 : 06:53:09

just the unique index/constraint for the column..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-15 : 07:01:01

sp_help tablename

look for the constraint name-

alter table drop constraint <name>

E 12°55'05.25"
N 56°04'39.16"

Bellus
Starting Member

29 Posts

Posted - 2007-10-15 : 07:07:43

bellus=# alter table meta_host_types drop constraint value;
ERROR: constraint "value" does not exist

?? sp_help tablename.

the table looks like this, I want to delete unique for value and id:

Table "public.meta_host_types"
Column | Type | Modifiers
-------------+---------+---------------------------------------------------------------------
id | integer | not null default nextval('meta_types_application_id_seq'::regclass)
application | integer |
value | text |
comments | text |
types | text |
Indexes:
"meta_types_application_pkey" PRIMARY KEY, btree (id)
"meta_host_types_id_key" UNIQUE, btree (id)
"meta_host_types_value_key" UNIQUE, btree (value, application)
"meta_host_types_value_key1" UNIQUE, btree (value)
Foreign-key constraints:
"meta_types_application_application_fkey" FOREIGN KEY (application) REFERENCES appsmarteye(id)

Bellus
Starting Member

29 Posts

Posted - 2007-10-15 : 07:09:03

And this is the reason why I started to mess with the unique, maybe you can help me solve this problem???

I get this error when I try to references to the table meta_host_types:

there is no unique constraint matching given keys for referenced table "meta_host_types"

the table looks like this:

Table "public.meta_host_types"
Column | Type | Modifiers
-------------+---------+---------------------------------------------------------------------
id | integer | not null default nextval('meta_types_application_id_seq'::regclass)
application | integer |
value | text |
comments | text |
types | text |
Indexes:
"meta_types_application_pkey" PRIMARY KEY, btree (id)
"meta_host_types_id_key" UNIQUE, btree (id)
"meta_host_types_value_key" UNIQUE, btree (value, application)
Foreign-key constraints:
"meta_types_application_application_fkey" FOREIGN KEY (application) REFERENCES appsmarteye(id)

Whats the problem??

Can someone please help!:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-15 : 07:25:23

Please post table creation code so that we can mimic your environment.

E 12°55'05.25"
N 56°04'39.16"

Bellus
Starting Member

29 Posts

Posted - 2007-10-15 : 07:29:51

Ok, thanks a lot for helping!!:)

Here is the code for the meta_host_types table:

bellus=# create table meta_host_types(id serial,application integer references appsmarteye,
types text,
value text,
comments text,
PRIMARY KEY(id));

-------end of meta_host_types table--------------------

Here are the code for the table that I want to be a references to the meta_host_types table:

bellus=# create table host_application_definition(id serial,type_value integer references meta_host_types,
connection_value integer references meta_connection_methods,
group_value integer references meta_host_grouptypes,
application_value integer references meta_host_types(application),PRIMARY KEY(id)
);

thanks for helping!!!:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-15 : 07:33:47

And which of the contraints are you trying to remove?

E 12°55'05.25"
N 56°04'39.16"

Bellus
Starting Member

29 Posts

Posted - 2007-10-15 : 07:44:41

value and id:

"meta_host_types_value_key1" UNIQUE, btree (value)
"meta_host_types_id_key" UNIQUE, btree (id)

and I am trying to make a references from host_application_definition to the meta_host_types,

:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-15 : 08:04:35

I don't know which DBMS yuo are using, but this is a way for Microsoft SQL Server

-- Create the table
create table	#meta_host_types
		(
			id INT IDENTITY(1,1) primary key,
			[application] int,
			types text,
			value varchar(8000),
			comments text
		)

-- create the indexes
create unique index meta_host_types_value_key1 on #meta_host_types (value)
create unique index meta_host_types_id_key on #meta_host_types (id)

-- drop the indexes
drop index #meta_host_types.meta_host_types_value_key1
drop index #meta_host_types.meta_host_types_id_key

-- clean up
drop table #meta_host_types

E 12°55'05.25"
N 56°04'39.16"

Bellus
Starting Member

29 Posts

Posted - 2007-10-15 : 08:14:53

Okay, thanks a lot I will look at it - I use postgres.

Did you by any change have a tip for the other problems, where I get the error there are no unique constraint, I have made a lot of tables and refernces befor, but never faced this problem...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-15 : 08:16:58

try www.dbforums.com

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources