December 3, 2013 at 8:22 am
Hi all
i've executed the following
CREATE DATABASE practise
USE practise
CREATE TABLE dbo.SUPPLY1 (
supplyID INT CONSTRAINT SUPPLY1_pk PRIMARY KEY CONSTRAINT SUPPLY1_chk CHECK (supplyID BETWEEN 1 and 150),
supplier CHAR(50)
);
SELECT unique_index_id, is_system_named, * FROM sys.key_constraints WHERE name=N'SUPPLY1_pk'
AND SCHEMA_NAME(schema_id)='dbo'
SELECT name, parent_column_id, definition, is_system_named, * FROM sys.default_constraints WHERE name=N'SUPPLY1_chk'
EXEC sp_help @objname='SUPPLY1'
but what is suprising is when I execute
SELECT name, parent_column_id, definition, is_system_named, * FROM sys.default_constraints
There's not even SUPPLY1_chk being mentioned.
while I understand there's bugs associate with sys.default_constraints from http://www.sqlservercentral.com/Forums/Topic1359991-3077-1.aspx
the situation above is different as it is a permanent table
if I execute
EXEC sp_help @objname='SUPPLY1'
......
CHECK on column supplyIDSUPPLY1_chk(n/a)(n/a)EnabledIs_For_Replication([supplyID]>=(1) AND [supplyID]<=(150))
PRIMARY KEY (clustered)SUPPLY1_pk(n/a)(n/a)(n/a)(n/a)supplyID
SUPPLY1_chk appears in the list
Am I missing something here?
thanks!
December 3, 2013 at 8:32 am
You haven't created a default constraint. You have a check constraint (not the same thing). Per BOL sys.default_constraints only returns objects that have a sys.objects.type of 'D'. If you run this:
select * from sys.objects where name = 'SUPPLY1_chk'
You will notice that the type is 'C' for check.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply