Drop And Reacreate CONSTRAINT

Oct 9, 2001

ALTER TABLE doc_exe ADD

/* Add a PRIMARY KEY identity column. */
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,

/* Add a column referencing another column in the same table. */
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a)

go

ALTER TABLE doc_exe
DROP CONSTRAINT column_c_fk

How to recreate CONSTRAINT column_c_fk without droping and reacreating column column_c or add new CONSTRAINT on column column_c ?

Thanks a lot .

View 3 Replies


ADVERTISEMENT

Can't Drop Constraint...?

Aug 30, 2006

Hello, I have hit the wall here...can't make sense of this one.

I have a script that creates a PRIMARY KEY constraint called PK_tblDRG
CODE:

ALTER TABLE [dbo].[tblDRG]
ALTER COLUMN [record_id] Uniqueidentifier NOT NULL
Go
ALTER TABLE [dbo].[tblDRG]
WITH NOCHECK ADD PK_tblDRG PRIMARY KEY CLUSTERED
(
[record_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]

All is fine with that. I run this to verify:

EXEC sp_pkeys @table_name = 'tblDRG'
,@table_owner = 'dbo' ,@table_qualifier = 'Relational_05Q3'

which returns this:

TABLE_QUALIFIERTABLE_OWNERTABLE_NAMECOLUMN_NAMEKEY_SEQPK_NAME
Relational_05Q3dbotblDRGrecord_id1PK_tblDRG

Now I want to drop the constriant if it exists:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PK__tblDRG]') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE [dbo].[tblDRG] DROP CONSTRAINT PK__tblDRG

AND I get this in return:

The command(s) completed successfully.

So, lets double check:

EXEC sp_pkeys @table_name = 'tblDRG'
,@table_owner = 'dbo' ,@table_qualifier = 'Relational_05Q3'

AND I STILL GET THIS:

TABLE_QUALIFIERTABLE_OWNERTABLE_NAMECOLUMN_NAMEKEY_SEQPK_NAME
Relational_05Q3dbotblDRGrecord_id1PK_tblDRG

Hmmmmm. Looks like the IF statement didn't do it's job. Ok fine. I'll just kill it myself:

ALTER TABLE [dbo].[tblDRG] DROP CONSTRAINT PK__tblDRG

AND I GET THIS?!?!?!?!

Server: Msg 3728, Level 16, State 1, Line 1
'PK__tblDRG' is not a constraint.

What am I not getting here? Is it me...I can take If I am a bone head here.

Any help would be appreciated. Thanks!

View 1 Replies View Related

Drop Constraint Never Finishes

Oct 13, 2002

I need to make some changes to tables in a database. To do this requires that I "alter table" and "drop constraint" All constraints drop properly, except one. The last one will churn away forever in query analyzer and never drop. I am unable to make the necessary changes until this completes. I am guessing that there is a data issue that is keeping the constraint from being dropped. Any ideas on how to proceed? Thanks!

View 2 Replies View Related

Drop Column With Constraint

Sep 25, 2007



Hello !

I am using Microsoft SQL Server 2000

I am trying to drop a column that has a constraint, executing the script inside a transaction:

BEGIN TRANSACTION

ALTER TABLE MOPTeste DROP CONSTRAINT FK_IDMOPPais


ALTER TABLE MOPTeste DROP COLUMN IDMOPPais

COMMIT

If i dont commit the drop constraint, it wont let me drop the column

Solutions?



View 3 Replies View Related

Urgent!drop Foreign Key Constraint

Mar 1, 2001

I have to drop foreign key constraint from one table. How can I do that? Is it possible?
Also,

Is it possible to add a foreign key constraint on the table once it has a data?

View 1 Replies View Related

How To Drop UNIQUE CONSTRAINT On Only One Column

May 5, 2014

I am very new to sql and I have got this doubt.

CREATE TABLE employee
{ EMP-ID VARCHAR UNIQUE,
DESIG VARCHAR UNIQUE,
SALARY INT };

Probably this is worst table ever created :).Now How to drop UNIQUE constraint on only column lets DESIG.

if i write

ALTER TABLE employee
DROP CONSTRAINT myuniqueconstraint;

will drop constraints on both columns which I dont want.

Any query that remove UNIQUE CONSTRAINT only on DESIGN column.

and one more clarification,

ALTER TABLE employee
MODIFY SALARY INT NOT NULL;

ALTER TABLE employee
ALTER COLUMN SALARY INT NOT NULL;

which of the above two is right query to add NOT NULL constraint to the above employee TABLE?

View 1 Replies View Related

Drop Primary Key Constraint Of (#) Hash

Jul 20, 2005

Hi there,I have created a hash table. After using it, somehow the primary keyconstraint of this hash table still exist in database. Which causeerror.When I delete this constraint with Alter table Drop con....It gives no table exist error.Can anybody give any idea.Thanks in Adv.,T.S.Negi

View 2 Replies View Related

UNABLE To ADD Or DROP A Constraint - SQL Complains!

Apr 21, 2008

Hi all, I am trying to create a CONSTRAINT but for some reason T-SQL does not allow me to do that.

When I try to DROP a CONSTRAINT it says:

Msg 3728, Level 16, State 1, Line 13'DF_TBL_SyncTable_DEVUK' is not a constraint.Msg 3727, Level 16, State 0, Line 13Could not drop constraint. See previous errors.

When I try to ADD a CONSTRAINT it says:
Msg 1781, Level 16, State 1, Line 14Column already has a DEFAULT bound to it.Msg 1750, Level 16, State 0, Line 14Could not create constraint. See previous errors.


For some reason I can't win here. Can't drop it nor can I create one. Any solution?




Code Snippet
ALTER TABLE TBL_SyncTable DROP CONSTRAINT DF_TBL_SyncTable_DEVUK
ALTER TABLE TBL_SyncTable ADD CONSTRAINT GOD_TBL_SyncTable_DEVUK DEFAULT 2 FOR DEVUK


Thanks for comments + suggestions.

*UPDATE*

I am trying to use the following code to check if the SCHEMA exists but still no luck. For some reason when I create it and wrap an IF statement around it, it doesn't detect the SCHEMA. Is something wrong with my code?






Code Snippet

IF EXISTS(

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'dbo'


AND CONSTRAINT_NAME = 'DF_TBL_SyncTable_DEVUK'


AND TABLE_NAME = 'TBL_SyncTable'

)
SELECT * FROM TABLE_1
ELSE
ALTER TABLE TBL_SyncTable ADD CONSTRAINT DF_TBL_SyncTable_DEVUK DEFAULT 2 FOR DEVUK

View 4 Replies View Related

Whats Wrong Here, Drop Constraint Primary Key ? Help Pls...

Jun 14, 2000

select * from sysobjects where type = 'K'

name
------
pk_dtproperties
1 row(s) affected)

When I say - DROP CONSTRAINT pk_dtproperties

i get this error
-------------------
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONSTRAINT'.

NOTE:

In tables, I don't have any tables all tables i deleted but wonder from where this creature comes from,.........

View 2 Replies View Related

How To Alter Table Drop Constraint @variable?

Jun 12, 2003

I need to run the alter table to drop a default. However, the default name is kind of 'dynamic' from around 1000 databases, thus I need to run the following sql to get the name to a variable. Now, it looks the alter table statement does not like to drop a vaiable, is there a solution about it?

declare @radius_default varchar(40)
select @radius_default = (select sobj.name from sysobjects sobj
inner join syscolumns scolumn
on sobj.ID = scolumn.cdefault
where scolumn.name = 'radius' and sobj.name like '%LandMarks%')
print 'Need To Drop @radius_default: ' + @radius_default
--====================================
alter table LandMarks drop constraint @radius_default

thanks
David

View 1 Replies View Related

Database Diagrams: FOREIGN KEY Constraint Fell Off; Can't Drop/recreate It...

Mar 25, 2006

Hello,

I have two tables Person & Location where Location has a primary key LocationId and Person has a foreign key LocationId.

Sometime ago I used the Database Diagrams visual tool of SQL Server Management Studio Express to create the foreign key relationship between the two tables - i.e. "visually" (drawing a line between the PK & FK LocationId elements of both tables).

Time has passed and I recently noticed that, upon retrieving my saved diagram, the foreign key relationship had "fallen off" (i.e. the many-to-one line was no longer showing in the diagram).

After recreating the relationship (redrawing the line) I find that I get an error message when I try to save the diagram:

Post-Save Notifications
[!] Errors were encountered during the save process. Some database objects were not saved.

'Location' table saved successfully
'Person' table
- Unable to create relationship 'FK_Person_Location'. 
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Person_Location". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

When I go back to the object explorer and view the dependencies for the two tables, there is no dependency (between these two tables) revealed.  When I try to create the foreign key constraint manually (T-SQL) it again says can't add the constraint.  It comes up with an error as follows:

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__Person__LocationId__793DFFAF". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

(Note:  Each time I do this, the 8 hexadecimal character suffix changes.)

When I try to drop the foreign key:

alter table Person
drop constraint FK__Person__LocationId

it comes back with the error:

Msg 3728, Level 16, State 1, Line 2
'FK__Person__LocationId' is not a constraint.
Msg 3727, Level 16, State 0, Line 2
Could not drop constraint. See previous errors.

So it seems that there's some kind of goof up here.  Can anybody shed light on this / tell me how to fix it?

View 6 Replies View Related

Drop All Indexes In A Table, How To Drop All For User Tables In Database

Oct 9, 2006

Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob

View 2 Replies View Related

Named Constraint Is Not Supported For This Type Of Constraint (not Null)

May 13, 2008

Hi, all.

I am trying to create table with following SQL script:





Code Snippet

create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) constraint NN_Prj_Name not null,
Creator nvarchar (255),
CreateDate datetime
);

When I execute this script I get following error message:

Error source: SQL Server Compact ADO.NET Data Provider
Error message: Named Constraint is not supported for this type of constraint. [ Constraint Name = NN_Prj_Name ]

I looked in the SQL Server Books Online and saw following:

CREATE TABLE (SQL Server Compact)
...
< column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ] | REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]

As I understand according to documentation named constraints should be supported, however error message says opposite. I can rephrase SQL script by removing named constraint.





Code Snippet

create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) not null,
Creator nvarchar (255),
CreateDate datetime
);
This script executes correctly, however I want named constraints and this does not satisfy me.

View 1 Replies View Related

Unique Constraint Error When There Is No Constraint

May 13, 2008

We are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get the following error:
A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle
But the only PK on this table is RegTitleID.

The table structure is:
[RegTitleID] [int] IDENTITY(1,1) NOT NULL,
[RegTitleNumber] [int] NOT NULL,
[RegTitleDescription] [varchar](200) NOT NULL,
[FacilityTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL,

The problem occurs when a Title Number is inserted and a record with that number already exists. There is no unique constraint on Title Number.
Has anyone else experienced this?

View 3 Replies View Related

Rollback Will Drop Created Tables And Drop Created Tables In Transaction..?

Dec 28, 1999

Hi folks.

Here i have small problem in transactions.I don't know how it is happaning.
Up to my knowldge if you start a transaction in side the transaction if you have DML statements
Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5
It is rolling back all the commands including DDL witch it shouldn't please let me know on that
If any one can help this please tell me ...........Please............
For Example
begin transaction t1
create table t1
drop table t2

then execute bellow statements
select * from t1
this query gives you table with out data

select * from t2
you will recieve an error that there is no object

but if you rollback
T1 willn't be there in the database

droped table t2 will come back please explain how it can happand.....................

Email Address:
myself@ramkistuff.8m.com

View 1 Replies View Related

Help-Constraint

May 29, 2001

Hi,
I am trying to figure out how to do this.
For each row, only one out of two columns(id1,id2) should be populated. So if the id1 column is already populated and the application tries to fill in something for id2 then we just simply don't want to allow that and vice versa.

I am thinking triggers is the way to go. What do you think?
thanks
Rozina

View 1 Replies View Related

Constraint Help!!

Sep 21, 2000

using alter table syntax how can i insert the field TramingChoiceCd
Extend the constraint on NetwkChannel table UQ__TetwkChannel__50FB042B to include TramingChoiceCd

View 1 Replies View Related

Constraint

Nov 30, 2001

Which is the preferred method Rule, Check Constraint or Trigger?
I want to set a column to todays date when the column is = "T"
else when "F" set it to a future date. Each time there is a insertion into
the table.

View 1 Replies View Related

Constraint

Jun 7, 2004

I have a varchar field in a table.I want to restrict the entries in that field as "yes" or "no" nothing else.No record will be allowed for this field without yes or no.My question is is it possible without using any trigger for the table?I want to do it with the help of a constraint.

View 1 Replies View Related

Constraint Ddl

Oct 13, 2004

When I see desing table option in enterprise manager of a table I don't see any constraints, but when I extract ddl I can see all 6 of them. They are all unique constraints not the check constraints. Is this normal. I am new to SQL Server and would appreciate some explanation.

Thanks

View 2 Replies View Related

Constraint Help

Nov 15, 2007

Hi, i want to put a contraint on a table which much check agains two values in the same column for the same member.


For example, i don't want a male to get information based on breast cancer, and i don't want a female to get information based on prostate cancer.

I have included some sample data. Just copy and paste.



Code Block

DECLARE @MemberLookupValues TABLE (OptionID INT, ValueID INT, Description VARCHAR(20))
INSERT @MemberLookupValues VALUES (3, 10, 'Male')
INSERT @MemberLookupValues VALUES (3, 11, 'Female')
INSERT @MemberLookupValues VALUES (7, 69, 'Prostate Cancer')
INSERT @MemberLookupValues VALUES (7, 70, 'Breast Cancer')

DECLARE @MemberValues TABLE (MemberID INT, OptionID INT, ValueID INT)
INSERT @MemberValues VALUES (1, 3, 10)
INSERT @MemberValues VALUES (1, 7, 69)
INSERT @MemberValues VALUES (1, 7, 70)
INSERT @MemberValues VALUES (2, 3, 11)
INSERT @MemberValues VALUES (2, 7, 69)

SELECT * FROM @MemberLookupValues
SELECT * FROM @MemberValues






I've highlighted the values that must be stopped.

So the basic check would be, IF OptionID = 3 AND ValueID = 10 then it must not allow you to insert the values OptionID = 7 AND ValueID = 70

I hope that makes sense.

Any help will be greatly appreciated, if you need any more informaiton then just ask,

Kind Regards
Carel Greaves

View 4 Replies View Related

How To Drop PK?

Sep 5, 2001

How can I drop Primary Key? If I do/don't have data in the table.Thanks!
Ravi.

View 2 Replies View Related

Cant Drop

Apr 26, 2007

Why can't I drop the database? Is this query correct Drop database databasename?

View 1 Replies View Related

Need Help In Drop UDD

Jul 28, 2007

Dear Friends,

i want to drop UDD.which i have used in 1000 of sps and tables. it is impossible to drop all objects and create.so please give any sugessions.

View 1 Replies View Related

Drop Last Bit

Sep 7, 2007

Hi,

I have an sql script, which updates some values by using BIT operations..

ex:

UPDATE table1
SET myValue = myValue & ~mask | (availability - mask)...

Problem is, this returns a value, when written to binary, it's one bit too long!

ex:







0
1
1
1
1
1
(= 62)

which should be:







0
1
1
1
1
0

(=31)

How can I "drop" this last BIT?
(Keep in mind that I store these values as LONGINT's)...

Been fighting with this one all day..

View 1 Replies View Related

Check Constraint

Mar 28, 2007

Hi I was wodering how to add an OR statment right in the Check Constraint expression.
This is what I am starting with in the database
([zip] like '[0-9][0-9][0-9][0-9][0-9]')
and what I want well not exact but this would answer my question
([zip] like '[0-9][0-9][0-9][0-9][0-9] || [A-Z][A-Z][A-Z][A-Z][A-Z]')
 Thanks for any help

View 5 Replies View Related

Constraint Question

Oct 18, 2007

Is it possible to set an index of no duplicates on a column other than the primary key of a table? If yes, how is this done?

View 2 Replies View Related

Delete Constraint

Jan 3, 2005

how can i implement delete constraint? i mean i don't want the rows of the primary key table to be deleted if they are used as foreign key in some other table. so i want to check if that PK is used as foreign key in other tables before deleting.

View 10 Replies View Related

Add UNIQUE Constraint

May 13, 2006

Hi,I want to add unique contraint to EXISTING column in EXISTING table.I have ms sql 2005.How to do that?

View 7 Replies View Related

Check Constraint

Oct 31, 2000

hi, I want to implement a constraint on a talbe for two fields
phone numbers should b (###)###-####
and ss# should be ###-##-####

How can I create such constraint. I tried, but got an error message and could not save the table with the new changes.

Thanks
Ahmed

View 2 Replies View Related

Foreign Key Constraint

Aug 11, 1999

student --- enrollment is 1- to - many . ssn is pk in student. ssn and courseid is pk in enrollment. later I added the foreign key constraint FK_SSN in enrollment table reference student table. it is ok.
enrollment --- lessonhistory is 1-to -many, ssn, courseid and lessonid is pk in lessonhistory . I tried to set FK_SSN foreign key constraint in lessonhistory table reference enrollment table, it always show error message " no primary key in referewnce talbe enrollment "
I don't know how to fix it. could you help me out, thanks!!

View 1 Replies View Related

Delete Constraint

Dec 7, 2003

Can somebody tell me about on delete constraint and where should it be used - table having foriegn key?

coz want i want is- the moment i delete the data from the table whose primary key is been referenced as foreign key , The Data in all the tables where its primary key is beeen used as forein key should be deleted.

:confused:

View 2 Replies View Related

Unique Constraint

Nov 19, 2001

Does anyone have any Idea on how I could enforce a unique constrait across multiple tables?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved