July 31, 2019 at 2:16 am
Here is my code:
CREATE SCHEMA Costpoint AUTHORIZATION iDrago;
GO
CREATE TABLE Costpoint.ProjectCodes
(
isActivetinyintNOT NULL,
projectCodevarchar(28)NOT NULL,
projectNamevarchar(25)NULL,
leveltinyintNOT NULL,
projectMgrIDchar(6)NULL,
projectMgrNamevarchar(22)NULL,
projectClassvarchar(14)NULL,
projectTypevarchar(11)NOT NULL,
isBillabletinyintNOT NULL,
allowChargingtinyintNOT NULL,
exportProjectClassvarchar(23)NOT NULL,
accountGroupchar(3)NOT NULL,
owningOrgchar(10)NOT NULL,
customerIDvarchar(12)NOT NULL,
customerNamevarchar(20)NOT NULL,
startDatedateNULL,
endDatedateNULL,
primeContractNochar(20)NULL,
taskOrderNochar (20)NULL,
CONSTRAINT CK_isActiveCHECK (isActive = 0 OR isActive = 1),
CONSTRAINT CK_levelCHECK (level BETWEEN 0 AND 7),
CONSTRAINT CK_isBillableCHECK (isBillable = 0 OR isActive = 1),
CONSTRAINT CK_allowChargingCHECK (allowCharging = 0 OR isActive = 1)
);
Then I am trying to do this:
ALTER TABLE Costpoint.ProjectCodes
DROP CONSTRAINT CK_isActive;
ALTER TABLE Costpoint.ProjectCodes
DROP COLUMN isActive;
I am running these two blocks of code separately. After I run the ALTER TABLE statements I get this error:
Msg 5074, Level 16, State 1, Line 43
The object 'CK_isBillable' is dependent on column 'isActive'.
Msg 5074, Level 16, State 1, Line 43
The object 'CK_allowCharging' is dependent on column 'isActive'.
Msg 4922, Level 16, State 9, Line 43
ALTER TABLE DROP COLUMN isActive failed because one or more objects access this column.
I intended for CK_isActive to be applied to the isActive column. So I tried to drop that constraint before I can drop the isActive column.
The CK_isBillable constraint references the isBillable and isActive columns, so I guess SQL could apply it to either one.
The CK_allowCharging constraint references the allowCharging and isActive columns, so I guess SQL could apply it to either one.
So why does SQL think that the CK_isBillable and CK_allowCharging check constraints are applied to the isActive column?
July 31, 2019 at 4:18 am
Edit: I ran this part of the code only to see if I can isolate the problem:
ALTER TABLE Costpoint.ProjectCodes
DROP CONSTRAINT CK_isActive;
and I got this error message:
Msg 3728, Level 16, State 1, Line 41
'CK_isActive' is not a constraint.
Msg 3727, Level 16, State 0, Line 41
Could not drop constraint. See previous errors.
The error says 'CK_isActive' is not a constraint but I did define it as a constraint (see the code in my original post). Why is SQL not recognizing 'CK_isActive' as a constraint?
July 31, 2019 at 6:15 am
Edit: I ran this part of the code only to see if I can isolate the problem:
ALTER TABLE Costpoint.ProjectCodes
DROP CONSTRAINT CK_isActive;and I got this error message:
Msg 3728, Level 16, State 1, Line 41
'CK_isActive' is not a constraint.
Msg 3727, Level 16, State 0, Line 41
Could not drop constraint. See previous errors.The error says 'CK_isActive' is not a constraint but I did define it as a constraint (see the code in my original post). Why is SQL not recognizing 'CK_isActive' as a constraint?
You can't drop the column because you have 2 check constraints that are dependent on the column
CONSTRAINT CK_isBillableCHECK (isBillable = 0 OR isActive = 1),
CONSTRAINT CK_allowChargingCHECK (allowCharging = 0 OR isActive = 1)
July 31, 2019 at 1:07 pm
Here is my code:
CREATE SCHEMA Costpoint AUTHORIZATION iDrago;
GO
CREATE TABLE Costpoint.ProjectCodes
(
isActivetinyintNOT NULL,
projectCodevarchar(28)NOT NULL,
projectNamevarchar(25)NULL,
leveltinyintNOT NULL,
projectMgrIDchar(6)NULL,
projectMgrNamevarchar(22)NULL,
projectClassvarchar(14)NULL,
projectTypevarchar(11)NOT NULL,
isBillabletinyintNOT NULL,
allowChargingtinyintNOT NULL,
exportProjectClassvarchar(23)NOT NULL,
accountGroupchar(3)NOT NULL,
owningOrgchar(10)NOT NULL,
customerIDvarchar(12)NOT NULL,
customerNamevarchar(20)NOT NULL,
startDatedateNULL,
endDatedateNULL,
primeContractNochar(20)NULL,
taskOrderNochar (20)NULL,
CONSTRAINT CK_isActiveCHECK (isActive = 0 OR isActive = 1),
CONSTRAINT CK_levelCHECK (level BETWEEN 0 AND 7),
CONSTRAINT CK_isBillableCHECK (isBillable = 0 OR isActive = 1),
CONSTRAINT CK_allowChargingCHECK (allowCharging = 0 OR isActive = 1)
);
Michael,
Nice job on the vertical alignment but consider changing the setting in SSMS so that TABs are converted to spaces so you don't get the visual skew when you paste the code into something that has a different TAB length setting.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 1, 2019 at 12:22 am
Jeff,
It looks really nice now.
CREATE TABLE Costpoint.ProjectCodes
(
isActive tinyintNOT NULL,
projectCode varchar(28) NOT NULL,
projectName varchar(25) NULL,
level tinyint NOT NULL,
projectMgrID char(6) NULL,
projectMgrName varchar(22) NULL,
projectClass varchar(14) NULL,
projectType varchar(11) NOT NULL,
isBillable tinyint NOT NULL,
allowCharging tinyint NOT NULL,
exportProjectClass varchar(23) NOT NULL,
accountGroup char(3) NOT NULL,
owningOrg char(10) NOT NULL,
customerID varchar(12) NOT NULL,
customerName varchar(20) NOT NULL,
startDate date NULL,
endDate date NULL,
primeContractNo char(20) NULL,
taskOrderNo char (20) NULL,
CONSTRAINT CK_isActive CHECK (isActive = 0 OR isActive = 1),
CONSTRAINT CK_level CHECK (level BETWEEN 0 AND 7),
CONSTRAINT CK_isBillable CHECK (isBillable = 0 OR isActive = 1),
CONSTRAINT CK_allowCharging CHECK (allowCharging = 0 OR isActive = 1)
);
GO
I'm using SSMS v17.9.1. For anybody who wants to know how to do this, here is what you do:
Thanks again Jeff.
August 1, 2019 at 12:44 am
DesNorton,
I see what you are saying now. I was reading it wrong. But what about my second post where SQL says 'CK_isActive' is not a constraint? I don't understand why SQL doesn't view CK_isActive as a constraint (even though it is) when I try to run only the ALTER TABLE and DROP CONSTRAINT part of the code.
August 1, 2019 at 4:34 am
DesNorton,
I see what you are saying now. I was reading it wrong. But what about my second post where SQL says 'CK_isActive' is not a constraint? I don't understand why SQL doesn't view CK_isActive as a constraint (even though it is) when I try to run only the ALTER TABLE and DROP CONSTRAINT part of the code.
I suspect that you dropped CK_isActive the first time that you ran the DROP command.
You can check the list if check constraints as follows
SELECT cc.object_id
, cc.name
, cc.definition
FROM sys.check_constraints AS cc
WHERE cc.parent_object_id = OBJECT_ID(N'Costpoint.ProjectCodes', N'U');
August 2, 2019 at 12:27 am
It looks really nice now.
Awesome job, Michael!
I know it'll sound strange for me to say so but I'm also impressed that you took the time to discover the setting that converts TABs to spaces especially since you claim to be a newbie. You're going to do VERY well in the world of databases. Keep it up!
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2019 at 12:40 am
To add to what DesNorton posted, you can also check for existence of a constraint and take a conditional action in T-SQL. For example...
IF OBJECT_ID('CK_isActive','C') IS NOT NULL --The "C" identifies constraints
PRINT 'Exists'
;You can replace the print statement with ...
IF OBJECT_ID('CK_isActive','C') IS NOT NULL --The "C" identifies constraints
ALTER TABLE Costpoint.ProjectCodes DROP CONSTRAINT CK_isActive
;Also, just because you got some errors in your previous run doesn't mean that all of the didn't run. Some of it could have run including the DROP CONSTRAINT for CK_IsActive.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2019 at 1:19 am
DesNorton,
That makes sense. I have found that when I execute more than one statement at once, some of them seem to execute even though some may error out.
August 2, 2019 at 2:47 am
michael.leach2015 wrote:DesNorton,
I see what you are saying now. I was reading it wrong. But what about my second post where SQL says 'CK_isActive' is not a constraint? I don't understand why SQL doesn't view CK_isActive as a constraint (even though it is) when I try to run only the ALTER TABLE and DROP CONSTRAINT part of the code.
I suspect that you dropped CK_isActive the first time that you ran the DROP command.
You can check the list if check constraints as follows
SELECT cc.object_id
, cc.name
, cc.definition
FROM sys.check_constraints AS cc
WHERE cc.parent_object_id = OBJECT_ID(N'Costpoint.ProjectCodes', N'U');
This has lead me to look into functions a bit. The Object Name ( ) returns Object ID and the Object ID ( ) returns the Object name.
August 2, 2019 at 2:49 am
Jeff Moden,
Thanks for your input. I am familiar with IF statements and looping statements a bit. Your example will help me to get use to the programming side of SQL using these programming structures.
August 2, 2019 at 4:26 am
DesNorton wrote:michael.leach2015 wrote:DesNorton,
I see what you are saying now. I was reading it wrong. But what about my second post where SQL says 'CK_isActive' is not a constraint? I don't understand why SQL doesn't view CK_isActive as a constraint (even though it is) when I try to run only the ALTER TABLE and DROP CONSTRAINT part of the code.
I suspect that you dropped CK_isActive the first time that you ran the DROP command.
You can check the list if check constraints as follows
SELECT cc.object_id
, cc.name
, cc.definition
FROM sys.check_constraints AS cc
WHERE cc.parent_object_id = OBJECT_ID(N'Costpoint.ProjectCodes', N'U');This has lead me to look into functions a bit. The Object Name ( ) returns Object ID and the Object ID ( ) returns the Object name.
<li style="list-style-type: none;">
- I looked at the SQL documentation for the function definition of the OBJECT_ID function. Regarding the first occurrence of N' does that refer to the database name or schema name? The N'U' is supposed to refer to the object's type, but I didn't see a type that corresponds to N'U' (I noticed that U represents a user-defined table). I guess the N and the single quotes are throwing me off.
<li style="list-style-type: none;">
- Is there a function that can return the object's type, starting with the Object name?
<li style="list-style-type: none;">
- Is there a function that can return the object's type, starting with the Object's ID?
The N'xxx' is an indicator that the contents between the quotes is unicode (which is what the function is expecting). The N'U' does in fact indicate a user-defined table.
I am unaware of any function that will return an object's type. However, you can get the type from sys.objects
select o.object_id
, o.name
, o.type
, o.type_desc
from sys.objects as o;
August 2, 2019 at 1:55 pm
Jeff Moden,
Thanks for your input. I am familiar with IF statements and looping statements a bit. Your example will help me to get use to the programming side of SQL using these programming structures.
You don't want to use "looping" for much. It makes things horribly slow and efficient. In most cases, looping should be limited to control structures rather than being used for processing data. And, there's hidden "RBAR" (see my signature line for definition) in the form of Recursive CTEs (rCTE), functions and procs that call themselves (recursion), and even some of the built in functionality of SQL Server (sys.sp_MSforeachdb and sys.sp_MSforeachtable, for example).
To get you started, please see the following article that provides and introduction to a "Tally" Table. If you look at a lot of the forum questions, you'll find out that there are many ways to pull of the same kind of thing using Cascading CTEs (cCTE). A lot of people miss the underlying meaning of the "Tally" table and that is the concept of "Pseudo-Cursors". Every SELECT (and INSERT, UPDATE, and DELETE) is actually a very high performance "loop" at the machine language level and you can do some rather remarkable things with the knowledge of the underlying "Pseudo-Cursors", which you won't find in any Microsoft Documentation (heh... because even a lot of MS programmers don't get it).
Here's the article on the "Tally" table...
And here's an article that compares some hidden RBAR in the form of an rCTE to three classic "Pseudo-Cursor" methods.
https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes
There's a lot more to it... if you understand the concept of "Pseudo-Cursors" and the fact that the smallest unit of data that SQL Server can read is a "page" (8192 bytes), you'll be able to understand things like JOINs and INDEXes a whole lot better to help you write some nasty fast code and to solve problems in T-SQL that most people think you'd need a cursor, While loop, CLR, PowerShell, of a snippet of .Net code.
The first step (IMHO) to writing really good T-SQL is also in my signature line below...
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2019 at 11:36 pm
DesNorton,
You mentioned:
The N'xxx' is an indicator that the contents between the quotes is unicode (which is what the function is expecting). The N'U' does in fact indicate a user-defined table.
Thank you for clarifying this. The N being Unicode sounds like the difference between char ( ) and nchar( ) as well as varchar( ) vs. nvarchar( ).
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply