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]
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
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!
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
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
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
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?
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
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.
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?
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.....................
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
using alter table syntax how can i insert the field TramingChoiceCd Extend the constraint on NetwkChannel table UQ__TetwkChannel__50FB042B to include TramingChoiceCd
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.
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.
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.
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
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.
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!!
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.
