ALTER TABLE ... CONSTRAINT
-
Got this problem I find strange...
If I try to alter a constraint (in QA), with what I believe is the correct way:
ALTER TABLE Eq.dbo.ent ADD
constraint CK_code_e
CHECK (code_e IS NULL OR code_e LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')
GO
I get the following message:
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN CHECK constraint 'CK_code_e'. The conflict occurred in database 'Eq', table 'ent', column 'code_e'.
So far, so good. Except that I can enter data that differ from the constraint.
Most importantly, if I try to drop it:
ALTER TABLE Eq.dbo.ent drop
constraint CK_code_e
I get:
Server: Msg 3728, Level 16, State 1, Line 1
'CK_code_e' is not a constraint.
Server: Msg 3727, Level 16, State 1, Line 1
Could not drop constraint. See previous errors.
And it loops... (If I try to alter again, same message, and on and on...)
The constraint doesn't appear in EM, or in search.
Thanks for any clue
Elno
ps: SQL server 2000 on 2k, I'm coding on NT4.
-
Elno,
The reason you can't drop the constraint and you can enter data that violates the constraint is that the constraint was never created. When you said you were trying to alter a constraint, did you mean alter a table and add a constraint? Is this the only alter statement in the script? What are the table properties (e.g. nulls, other constraints on this particular column)?
Lori
-
Although I have the strong impression I created the constraint, I may be wrong.
However, I don't understand why I can't create a constraint on a table (altering it) on the reason that this constraint already exists (but I can't see it or drop it).
There was a 'null' default on the column, that I removed (no default), and it doesn't seem to have an influence.
I first create the table with the pk, then did one script for each constraint. Haven't test them all, but they look like they work...
Elno
-
Try running
SELECT * FROM Eq..sysobjects WHERE [name] = 'CK_code_e'
I tested your statement and works fine for me in my test DB, must be something it sees.
-
run sp_help ent
to see if the constraint is listed for the table. It sounds like it wasn't created where you thought it was.
-
I also tested the constraint statement, and it worked successfully for me. If the constraint actually existed, your error message would have been "there is already an object named xxx in the database". Could you post a script of the table, including all constraints?
Lori
-
Thanks for the input.
Problem is still there...
I ran the 'SELECT... sysobjects' command, and the result is null.
But the messages I get when I run the add or drop commands are the same.
The constraint is not listed for the table (with sp_help).
The code for the table is rather long...
Here it is:
USE Eq
GO
IF EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'ent')
and id = object_id(N'ent'))
DROP TABLE Eq.dbo.ent
GO
CREATE TABLE Eq.dbo.ent
(
cle_e int IDENTITY NOT NULL,
code_e nchar(6) NULL,
mne_e nvarchar(3) NULL,
nm_e nvarchar(128) NULL,
n_civ nvarchar(7) NULL,
su nvarchar(5) NULL,
r nvarchar(30) NULL,
c_p nvarchar(7) NULL,
vil nvarchar(30) NULL,
pro nvarchar(25) NULL,
ps nvarchar(20) NULL,
tel nvarchar(11) NULL,
tec nvarchar(11) NULL,
cour nvarchar(45) NULL,
web nvarchar(30) NULL,
no_ts nvarchar(15) NULL,
no_tq nvarchar(15) NULL,
no_ct nvarchar(20) NULL,
no_cq nvarchar(20) NULL,
no_ps nvarchar(20) NULL,
dt_e_p datetime NULL,
dt_e_p datetime NULL,
a1_e nvarchar(30) NULL,
a2_e nvarchar(45) NULL,
a3_e nvarchar(60) NULL
)
GO
ALTER TABLE Eq.dbo.ent ADD
CONSTRAINT PK_ent PRIMARY KEY (cle_e)
GO
ALTER TABLE Eq.dbo.ent ADD
CONSTRAINT UN_nm_e
UNIQUE (nm_e)
GO
ALTER TABLE Eq.dbo.ent ADD
constraint CK_code_e
CHECK (code_e IS NULL OR code_e LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')
GO
There are 4 defaults:
2 dates null
2 to values (pro & ps)
2 indexes: PK and nm_e (there was one on code_e, but I removed it - doesn't change anything).
Now, you know the table as well as I do
Inputs appreciated,
Elno
-
Elno,
I ran the create table statement (you have one column in there twice, so I deleted it), then the three alter table statements. The code completes successfully. What were you referring to by : 2 to values (pro & ps)?? I'm assuming these two columns get some type of default value. (btw, I didn't use any of the defaults when I tested this, i used the create and alter table scripts). Can you create an official script (right click the object, then chose All tasks, Generate script). On the options tab, select all option under Table Scripting Options (4 checks). Email it to me if you don't want to post it here.
I just don't see anything from your last post that could be causing the problem.
Lori
-
You forgot to mention you already have data in your table, didn't you? Your check constraint is validating the data when you try to add it. add with nocheck to your create statement.
alter table [name]
with nocheck add constraint [ckName2] check([Name] like '[0-9][0-9][0-9][0-9][0-9][0-9]')
Edited by - don1941 on 12/04/2002 1:31:18 PM
-
Yeah.
Problem solved. Didn't work yesterday, but works today. Won't touch it now... even if I don't understand everything.
Don't get why 'with nocheck' is needed when data in the table is coherent with the check, but I can live with it.
Other funny note: I couldn't generate an 'official script' because of a 916 error (not a valid user of the db) when I'm the owner. But I'm going to follow don1941's advice on timeouts. Maybe it's all linked, somehow...
Elno
Edited by - elno on 12/04/2002 2:37:29 PM
-
quote:
Don't get why 'with nocheck' is needed when data in the table is coherent with the check, but I can live with it.Maybe your data isn't as pristine as you think. Try running this on your table
select *
from table
where column not like {your check constraint here}
You can do this through EM if you check the "check data on creation" check box too.
-
not pristine indeed...
Thanks for the help. Really appreciated,
Elno
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply