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
ADVERTISEMENT
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
View Related
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
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
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
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
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
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
May 28, 2002
Is there a way we can prevent a object owner from dropping his tables. Example:
There is a user called 'tom' who is given create table permissions, 'tom' creates the table completes his dev and then the table is moved into production where 'tom' is still the owner. However, in production, 'tom' does not have the create table privs. Because 'tom' is the owner of the table, he is still able to drop the table in production. This is what I am trying to avoid. I would like to retain 'tom' as the owner and want to take away his create/drop privs.
View 1 Replies
View Related
Nov 17, 2000
Does anyone have any generic scripts that Drop all the statistics that SQL auto generates?? I have hundreds of '_WA_....'
indicies that are auto created by SS7 and I just want to get rid of ALL of them. Thanks!
View 3 Replies
View Related
Dec 7, 1999
Simple heres the syntax Alter Table XX Drop PRIMARY KEY.
This is not working whats up?
Thanks,
Phil
View 3 Replies
View Related
Apr 12, 2001
Whats the quickest way to drop a column from a table with 1 million rows.
This is for SQL Server 6.5
Thanks for all the responses,
Chan
View 1 Replies
View Related
May 22, 2001
I need to drop a database but cannot because there are 2 open connections that I cannot drop via EM. Is there a way to do this without starting and stopping??
View 2 Replies
View Related
Jul 20, 1998
Hi,
I`m new at SQL Server so excuse me if this question sounds dumb.
Does anybody know how to drop columns in a table. I know I can drop and re-build the table,
but I would prefer and easier way.
Thanks
-Z
View 1 Replies
View Related
Dec 21, 1998
Hi,
After I drop the database, I backed up the master database, I recreate the database with new name. When I tried to do the import on the command level,
It gives me the error:
Attempt to locate entry in the sysdatabases for database 'db1' by name,
failed -- no entry found in that name.
What should I do? Thanks.
View 1 Replies
View Related
Jul 6, 2001
I have run the following script to create a default on a table:
alter table TableXYY
add default 0 for ColumnX
Now, how do I delete this default (without re-creating the table)??
Thanks
View 1 Replies
View Related
Jul 23, 2001
Anyone know how to drop all users associated with a particular login not matter what database they're contained in?
ie login AdminUser / User AdminUser (in databases master, Dev1)
....
Thus, is there a way to drop the AdminUser login above and associated user
AdminUser in databases master, Dev1 in example above.
Thanks
View 1 Replies
View Related
Sep 7, 2007
Hi friends,
How I can drop a database insatance from a different server ( data base is SQL Server 2005) from my code
Hoping a early reply ....
Thanks :)
View 5 Replies
View Related
Feb 22, 2004
Hi
I am using SQL server 7 database and ASP as front end. I run an application where a text file is loaded into database. After this is done procedures are run to create a set of tables that have snapshots of the data in the text file.. each time i load a new text file i want to create the snapshots.. i hve written a stored procedure to create tables and insert values into the tables.. however how do i delete the tables i created the previous time.. the number of snapshots and their names will depend on the size of the text file.. how do i refer to all the snapshots created and drop them all before creating new ones?
plese guide
regds
View 1 Replies
View Related
Jan 3, 2007
Looking for suggestions.
I have a database that is giving me a bad Index error. When I go to drop the necessary Index it is telling me that it either does not exist or cannot be dropped. However when I try to build that index, it tells me one already exists.
Is there any way to drop all of the indexes or at the very least see what the indexes are? This particular database is using 2005 Express.
Any help would be great!
Shawn
View 4 Replies
View Related
Oct 15, 2007
How to remove the constraints on a table in sql server 2000? I need to drop the column, so i need to drop the constraint before that.
Alter Table Table_Name NO CHECK constraints ALL.
Once I execute the above one, it says, "Successfully Executed" but when I try to drop it doesnt allow me and gives me the following error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF_Users_Created_by' is dependent on column 'CREATED_BY'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN CREATED_BY failed because one or more objects access this column.
Any ideas?
Thanks,
View 2 Replies
View Related
Mar 10, 2007
I have trouble dropping a job that was a maintence plan.
I get the following error. Is there a way around this?
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for Job 'Maintenne Plan'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
View 3 Replies
View Related
Jan 16, 2008
I am trying to run a dymanic DROP DATABASE <dbname> command in a stored procedure called from ASP.NET. I typically connect from the .net using a SQL builtin account. I am getting this error:
System.Data.SqlClient.SqlException: Cannot drop the database 'db_testco', because it does not exist or you do not have permission.
The database is there for sure asI can browse it via the Studio manager. What permission do I need to set for my .net login to allow it to drop ? I tried adding the Delete permission to the database and still no go...
Setup: SQL 2005 SP2 2 Node cluster.
Thanks.
View 3 Replies
View Related
Oct 11, 2007
Hi,
I have a table with multiple column and has millions of rows in it(say about 50 millions ) and its in production.
What is the best way of dropping the a column from the table without impacting the systems performance. Remeber this table will be used some application to get some data out of it.
~Mohan
View 1 Replies
View Related
Apr 11, 2007
I am having following issue recreating my service broker
This could be a trivial fix...
when i execute the following query..
open master key decryption by password = 'pass1234$'
alter master key drop encryption by password = 'pass1234$'
close master key
drop master key
go
I get the following result..
Msg 15558, Level 16, State 1, Line 3
Cannot drop encryption by password '********'.
Msg 15580, Level 16, State 1, Line 5
Cannot drop master key because certificate 'BackofficeServiceAccount' is encrypted by it.
But 'BackofficeServiceAccount' was already dropped.
Can any one help me how to drop master key?
I appreciate any input..
regards...
View 3 Replies
View Related
Apr 12, 2006
Hi:
I think I am doing something wrong. I am trying to make the trigger fire when I want to drop the same trigger. For example I have the following trigger defined:
USE AdventureWorks2000
GO
CREATE TRIGGER Incheckstock
ON DATABASE
FOR DROP_TRIGGER
AS
PRINT 'You must disable Trigger "Incheckstock" to drop Trigger!'
ROLLBACK
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER Incheckstock ON DATABASE
DROP TRIGGER Incheckstock
ON DATABASE
I created the trigger Incheckstock and i want to see if the same trigger fires itself if when i am trying to drop the trigger with the same name (kind of recursive one). Is this possible in SQL 2005?. If I asked a dumb question, please correct me.
Thanks
View 1 Replies
View Related
Jan 20, 2007
I'm having a problem updating my Client profile table. I intially put
the data in with the "INSERT" SQL command that part works great. but
when I retrive the data to edit the profile it returns the correct
details and phone numbers. The problem is when I use the "UPDATE" SQL
command it saves all the data correctly except for the mobile phone
number which it drops off the last number all the time. Field sizes etc
are okay. Any help appreaciated
View 2 Replies
View Related
May 14, 2002
I guess nobodys heard of this? I'm using DTS to transform data to Excel spreadsheet. I have a DROP TABLE `data$` then a CREATE TABLE `data$` the old data is cleared but the new data is appended to the blank rows of the old data. So if I had 5 rows before now I have 10. And the new data has 5 blank rows before it.
I've tried deleting the excel file & replacing it with a new one.
I've used the wizard thinking it was me but no good, it still happens.
HELP!
View 1 Replies
View Related
Jul 17, 2001
I am using Access 2000 to link tables from SQL Server. The tables are linked using ODBC DSN`s. I am running a query to return some rows to Access, but once I have returned the rows, I want to drop the linked table connection to SQL Server, because it is locking a table on the server. I think I need to write a bit of VBA code, but I don`t know where to start. Any ideas would be greatly appreciated. Thanks.
View 1 Replies
View Related
Sep 20, 2001
SQL 7 created by default a clustered index on my primary key field. I would like to drop this index and recreate it on another field, but it is not allowing me. Error message states: "An explicit DROP INDEX is not allowed... It is being used for PRIMARY KEY CONSTRAINT enforcement." Can anybody advise how I can solve this? TIA
View 1 Replies
View Related
Mar 2, 2000
Hi
Has anyone heard of MS SQL Server 7 dropping indexes?
I had created an index on a table. The next day
the index had disappeared. Has anyone expirienced such
a problem?
Thanks in advance
Winston
View 1 Replies
View Related
May 2, 2000
i would like to drop a distribution database after disabling both susbscriber and publisher .but i get a message saying distribution database is still in use .and as far as i can tell its there is no one using it .also stopped sql agent but no luck . and when i do select @@ servername i get servername is null how do i correct it .
thanks
View 1 Replies
View Related
Dec 23, 1999
Is there a way in SQL to drop any users from a database. I am trying to schedule some nightly DB maintenance and some users are still in there sometimes. I need to be able to kick the out to do dbcc checkdbs, etc. Is there a way for me to do this without stopping the SQL services?
View 3 Replies
View Related
