Dropping A Constraint

Sep 20, 2006

I think the answer to this question will be something like 'you'll have to re-initialize the subscribers', but I need to ask anyway...

I created a publication where my foreign key constraint we NOT created with NOT FOR REPLICATION. My publication is configured to replicate DDL changes. Is there any way I can drop and re-create the constraint in the publisher and get replication to push the change to the subscribers?

Thanks for your help

Graham

View 1 Replies

Question About Dropping A PK Constraint

Mar 31, 1999

Hello Everyone,

I'm implementing a web-based Employee Self Service product that uses SQL Server as its backend. The product uses a fairly extensive data model which was automatically generated via a custom written schema.

I received a message from the vendor today that states that I need to ADD an additional field to an existing COMPOUND PK in one of their tables.

I wanted to check on one item before I DROP the existing 4 field PK and rebuild it as a 5 field PK.

Since the PK from the table in question is used as a FK in three other tables, will I be allowed to DROP then RECREATE the PK ?

If I do so, will it effect the FKs in the other 3 tables ?

Thanks in advance.

Mike

View 2 Replies View Related

Dropping Constraint On Temporary Table

Mar 20, 2007

I made a constraint on a temporary table in a stored procedure but now i can't delete it.

Here's what happened:
I ran this in a stored procedure

CREATE TABLE #TeFotograferen (RowID int not null identity(1,1) Primary Key,Stamboeknummer char(11) ,Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum)

next time i ran the stored procedure it gave me
There is already an object named 'UniqueFields' in the database.

but since the temporary table is out of scope i cannot delete the constraint
I tried
delete from tempdb..sysobjects where name = 'UniqueFields'
and
declare @name
set @name=(SELECT name from sysobjects where id=(Select parent_obj from sysobjects where name='UniqueFields'))
drop table @name

giving me
Ad hoc updates to system catalogs are not allowed.
or
Cannot drop the table '#TeFotograferen__________________________________ __________________________________________________ _________________000000000135', because it does not exist or you do not have permission.

View 7 Replies View Related

Temporarily Dropping Not-null Constraint

Nov 15, 2007

I am doing a data conversion in which I drop constraints before inserting data and then reapply at the end. I would like to also drop the not null constraints for certain columns, transfer the nulls and then fill them in later before reapplying the constraints. Is there a relatively simple way to drop and reapply the not null constraints?

View 4 Replies View Related

Unable To Update Table Even After Dropping Constraint

Jan 16, 2006

In Microsoft SQL Server, I have a documents table and a table whichcategorizes the documents, which we'll call categories.I tried running UPDATE statements on the categories table previouslyand I ran into a foreign key constraint. The error given was "UPDATEstatement conflicted with COLUMN REFERENCE constraint FK..."So I got rid of the Foreign Key relationship, and tried running anUPDATE statement against the categories table again.I'm now getting the following message:'Cannot UPDATE "categories" because "documents" exists.'There must be something hanging around maintaining that relationship,but I'm not sure where it would be found.I was thinking about dropping the table and then adding it back again,but I'm not entirely sure what that would do.Any help is appreciated in advance.thanks,Geoff

View 3 Replies View Related

DB Engine :: Dropping A Primary Key Constraint - 35 Minutes And Counting

Apr 4, 2011

I have a copy of this database table and the first thing I noticed was that the Primary Key was pretty much useless and there were no sensible indexes. Every query hitting this table ended up table scanning.So I thought I would try dropping the existing Primary Key constraint and then creating a more natural key that would make data retrieval quicker (hopefully). I understand that creating a clustered index on this table is going to take a long time as ALL the data will need to be reorganised (I estimate this will take at least 1 hour). However, just dropping the existing Primary Key constraint is taking forever.I can see that the server is doing a lot of disk reading/ writing and the wait type in Activity Monitor is PAGEIOLATCH_EX.I would have thought that just dropping a primary key would not change the data in the table, just delete the associated index.

View 4 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.