Droping A CONSTRAINT

Nov 30, 2005

I am having problem to find the right syntax to DROP a column with contrainst and recrate it
I get an error

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='myTable'
and COLUMN_NAME='myDate' )
ALTER TABLE [dbo].[myTable] DROP COLUMN myDate
GO

ALTER TABLE [dbo].[myTable] WITH NOCHECK ADD
myDate datetime CONSTRAINT [DF_myDate] DEFAULT (GetDate())
GO

Query Analyser says :
Server: Msg 5074, Level 16, State 1, Line 5
The object 'DF_myDate' is dependent on column 'myDate'.
Server: Msg 4922, Level 16, State 1, Line 5
ALTER TABLE DROP COLUMN myDate failed because one or more objects access this column.
Server: Msg 2705, Level 16, State 4, Line 2
Column names in each table must be unique. Column name 'myDate' in table 'dbo.myTable' is specified more than once.

thank you for helping

View 3 Replies


ADVERTISEMENT

Droping Log File

Jan 14, 2004

I have two transaction log files, one on C drive and other on D drive. I want to drop the file from that database that is on C drive. Can anyone help me out on this with complete syntax.

I have taken a down time of one and half hour to accomplish this task

Here is what think

Take a full database backup
Take transaction log backup
Shrinkfile using DBCC with truncateonly option
emptyfile using dbcc or drop file using alter database remove

thanks for your input guys


Rome

View 1 Replies View Related

Droping And Adding New User

Aug 23, 2005

Hi All,I am having a serious problem of removing and adding again an user in adatabase.Microsoft SQL Server 2000 - 8.00.760 (Intel X86)Dec 17 2002 14:22:05Copyright (c) 1988-2003 Microsoft CorporationEnterprise Edition on Windows NT 5.2 (Build 3790: Service PackSomehow, the user was created earlier but was not able to run queryassigned to him. As a result I wanted to drop and recreate the user.I have done many possible things to create the users again with thesame user id but failed.1. I tried to delete this user from the enterprsie manager security-->logins-->user1It deletes but when I try to add again, it gives me error message(Error 15023: user or role u'user1' already exist).2. Then I tried in the db:delete sysusers where name='user1'it deletes the user1.3. Again tried adding, got the message in 1.4. Then I trieduse db1EXEC sp_change_users_login 'Update_One', 'user1', 'user1'Server: Msg 15291, Level 16, State 1, Procedure sp_change_users_login,Line 88Terminating this procedure. The User name 'user1' is absent or invalid.I also tried master database and ran the following.EXEC sp_droplogin 'user1'The login 'user1' does not exist.But If I try to add the login user1, get the error message.Error 15023: user or role u'user1' already existI also ran the following when I got Ad hoc error messageexecute sp_configure "allow updates",1goreconfigure with overridegoCould you please tell me how I can solve this problem.I do highly appreciate your help.Thanks a million in advance.best regards,mamun

View 1 Replies View Related

Draging And Droping Scripts

Feb 14, 2006

I just upgraded from SQL2000 to SQL 2005. When I have a query opened for a database already and I want to drag and drop a script in, I get the login box. Is there anyway to get this to not happen? I would like it that when I drag and drop the script it is just loaded in a new query with the database connection that I was using already. (This is what it did in SQL2000) Thanks.

View 4 Replies View Related

Error While Droping Unique Index

Apr 4, 2002

Hi,

I get the following error message while droping unique index.

Server: Msg 3723, Level 16, State 5, Line 1
An explicit DROP INDEX is not allowed on index 'dbo.ALEG.IX_ALEG'. It is being used for UNIQUE KEY constraint enforcement.

Could you please how the index can be droped.

Thanks

View 1 Replies View Related

Droping System Generated Index

Dec 26, 1999

Anybody had experience dropping the system generated index?

I tried to drop some auto-generated index which usually have name like _WA_Sys_[column name}_07F6335A. Then I follow the table.index name rule. It always show no such index exists in the database. But I can query these indexes in sysindexes and verify they are there. What went wrong?

Help appreciated very much.

wilson

View 1 Replies View Related

Checking For Existence Of A Temp Table Before Droping It

Dec 31, 2003

I'm familiar with how to check for the existence of a table before dropping it using the following command:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[xxx]

How does one check for the existence of a temp table (using # syntax) before dropping it? I've tried various flavors of this command and none work. One flavor is

use tempdb
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#xxx]

CREATE TABLE #xxx (
NumID INTEGER IDENTITY(1,1),
Exhibitor_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL
)

Thanks.

Nick

View 2 Replies View Related

Sql2005 Replication, Droping It Takes Forever

Apr 20, 2007


I have a database that is about 300 gig. I am setting up replication to a reporting server. We are doing a series or mock loads and I will need drop the tables and reload the main database a few times before we go live. To do that I plan to stop replication and drop all the articles, drop the subscription, then load the new data, then reinitialize and restart replication.




The first time I tried to do this, when I drop the articles, it seems to be trying to "clean up" the distribution database on the reporting server and that is taking a couple of hours to do. The disruption database is about 40 gig.



Is this correct behavior in SQL2005 replication? Is there a way to avoid this? I have all the replication pieces scripted out and would like to just drop replication, reload, and then run my scripts to recreate replication. But this "clean up" is going to cause me a lot of headache if I don't figure out what is going on.



Am I going down the wrong road here? Is there an easier way to do this? Any comments would be great!!!!



Thanks in advance for any help.



Jim Youmans

St. Louis Missouri

View 1 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

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

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

Constraint Question

Mar 28, 2007

My company does medical billing for several clients. Each client may have a contract with several insurance companies (Payors) for rates for different procedures. I have a table named FeeSchedule with four (relevant) columns: Client, CPTCode (procedure code), Payor, and Fee.

Some Payors don't really negotiate, and have the same FeeSchedule for all clients. I know the right way to track that is have a separate set of records for each client, even for payors like medicare where every client will have the same set of records. However, the data-entry burden for populating that table would be very large, and I would like to use be able to have use a NULL client like a wildcard.

The problem is I then can't use client as part of the key, and I need to be able to restrict it so that if I have a NULL client for a specific CPT and Payor and I can't also have a another Client for that CPT and Payor.

Any ideas how to accomplish this or the equivalent functionality?

View 1 Replies View Related

Constraint Problem

Oct 27, 2004

Hi everybody



we have the following tables

1)

Country

Countryid CountyNAme
INIndia
MYMalaysia
UKUnitedKingdom
here Countryid is the primary key.


2) MainDept

DeptID Deptname Countryid
CMCashManagementIN
CBConsumerBankingMY
CSCustomer SupportIN
IBInternetBankIN
here deptid is the primary key

3) UserMaster

Uid Uname Deptid Countryid
001 Chris CMIN
002 Raja CSIN
003 Ram CBMY
here Uid is the primary key.

The problem is when i change the countryid from one country to another for a deptname.
THe change is not reflected in the usermaster table as it still shows the previous countryid.
For eg. user Chris belongs to dept Cash management which is situated in india.
Now if i change in mainDept table the cash management from india(IN) to say malaysia(MY).the corresponding change is not reflected in usermaster table.it still shows india. So when i query for chris in usermaster i get an error
as i am searching in india for cash mangement.
i tried using on update cascade but here it did not work as i have to make DeptID & countryID in MainDept table as composite key & use Deptid & countryid in usermaster as refernce key.
Since i have 20-25 tables also referencing the above 2 tables i have to set reference key in all these tables & these tables are in turn referenced elsewhere in other tables. Thus i end up creating a large no. of composite keys.
IS there any other way to solve this problem?
note : In sqlserver we can give on update cascade still it has the above problem
but in Oracle on update cascade is not possible

Can anybody suggest a solution for this in both sql server and in oracle

Thanks u verymuch

View 1 Replies View Related

Semantic Constraint

Mar 23, 2006

Hi all:

Just joined forums, so a big hello to you all :)

Just wondering if someone could explain the following two terms too me?

*Semantic Constraint
*Access Control

Are there limitations each can impose within a relational database?

Many thanks

Olly :D

View 14 Replies View Related

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

Constraint Or Index?

May 9, 2007

Suppose I have a table called "Languages" with two fields. One field is an Identity field that acts as the Primary Key. The second field is "LanguageName" which would naturally be "English", "Spanish", etc. What I want to do is put a constraint on the LanguageName field so that someone cannot enter the same name twice.Is it better to create an Index --> Create UNIQUE and use:a) Constraint?b) Index with Ignore duplicate key checked?Is there any benefit of one over the other for my purpose? Thanks.

View 4 Replies View Related

Constraint On Column

Dec 13, 2007

Hmm,

I'm creating a table that contains information from two other tables. I'd like to have a constraint on some columns so that thay can only contain information that exists in the other two tables. This is what I got:

create table dbo.KUSE_Bills
(
SERVICE_ID int IDENTITY(1,1) NOT NULL primary key,
BILLDATE datetime NOT NULL,
CNAME varchar(100) NOT NULL,
SNAME varchar(100) NOT NULL,
PRICE money NOT NULL,
UNIT varchar(50) NOT NULL,
NUMBER integer NOT NULL,
BILLSUM money NOT NULL
)
GO

I tried something like this:

create table dbo.KUSE_Bills
(
SERVICE_ID int IDENTITY(1,1) NOT NULL primary key,
BILLDATE datetime NOT NULL,
CNAME varchar(100) NOT NULL,
SNAME varchar(100) NOT NULL,
PRICE money NOT NULL,
UNIT varchar(50) NOT NULL,
NUMBER integer NOT NULL,
BILLSUM money NOT NULL

CONSTRAINT chk_cname CHECK (CNAME NOT IN (
SELECT CNAME FROM KUSE_Customer))
)
GO

But subqueries are not allowed...

So how can I do it?

View 3 Replies View Related

Foreign Key Constraint

Nov 20, 2011

I have a created a database with nearly 20 tables in it, so I cannot post all of my statements. I am having an issue though with two tables' referential integrity when it comes to foreign key constraints. I want to constrain them to cascade updates from the primary key tables, but I keep getting the error msg 1785

Introducing FOREIGN KEY constraint 'FK__PURCHASE___ITEM___64F971E5' on table 'PURCHASE_ORDER_LINE' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Here is the table with the primary key:
create table MERCHANDISE (
ITEM_ID varchar (8) NOT NULL PRIMARY KEY,
DESCRIPTION CHAR (60),
STANDARD_PRICE MONEY,

[code]...

I have tried making an update trigger but it does not work.

View 3 Replies View Related

Change Name Of Constraint

Mar 15, 2012

i got a problem

i add a constraint PK1
Use test
GO
ALTER TABLE tablo1 ADD CONSTRAINT PK1 PRIMARY KEY (a1)
GO

how do I Change the name of the constraint as PK1 PK2 Table1 a1 area

View 6 Replies View Related

Trigger + CONSTRAINT ???

Feb 9, 2004

I have 2 tables

Primary_Table (Key, ........)

Foreign_Table (Key_1, Key_2, ............)

I want this:

If I update the Key in Primary_Table then
Key_1 is Update where Key_1 = Key

AND

If I update the Key in Primary_Table then
Key_2 is Update where Key_2 = Key

I can not use CONSTRAINT because I can not use ON UPDATE CASCADE for this case (It not possible to use 2 CONSTRAINT WITH ON UPDATE CASCADE).

I WANT TO USE TRIGGERS

This is my trigger (but I am not sure it is the best way ?)

CREATE TRIGGER Test ON [Primary_table]
FOR UPDATE
AS

Declare @NewValue varchar(30)
Declare @OldValue varchar(30)

SET @NewValue = (Select Key From Inserted)
SET @OldValue = (Select Key From Deleted)

UPDATE [Foreign_Table]
SET [Foreign_Table].[Key_1] = @NewValue
FROM [Foreign_Table] WHERE [Key_1] = @OldValue

UPDATE [Foreign_Table]
SET [Foreign_Table].[Key_2] = @NewValue
FROM [Foreign_Table] WHERE [Key_2] = @OldValue


Sorry for my english.
Please consider I am a beginner

View 1 Replies View Related







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