September 19, 2011 at 1:29 am
The SQL server allows us to add multiple Check constraints for the same column in a table.
I have added the same and gave conflicting range values, surprizingly the table definition was created successfully.
When i try to insert rows into the table the check constraint error is thrown.
Below is the Table definition.
CREATE TABLE cust_sample
(
cust_id bigint PRIMARY KEY,
cust_name char(50),
cust_address char(50),
CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 1000),
CONSTRAINT chk_id1 CHECK (cust_id BETWEEN 100000 and 10000000 )
)
go
insert into cust_sample values (1001,'test','test')
When we try to insert a new row the below error is trown ,
The INSERT statement conflicted with the CHECK constraint "chk_id". The conflict occurred in database "master", table "dbo.cust_sample", column 'cust_id'.
The error is thrown based on the Constraint I had declared on the table, but my question is wont there be any validation on the contraint declaration.
Also the SQL Server allows the check range to be declared beyond the column data type range
thanks
sarat 🙂
Curious about SQL
September 19, 2011 at 2:32 am
The constraint has been defined - the values entered conflict with your constraints
SQL has done exactly what you defined...:w00t:
gsc_dba
September 19, 2011 at 2:52 am
gsc_dba (9/19/2011)
The constraint has been defined - the values entered conflict with your constraintsSQL has done exactly what you defined...:w00t:
A stored procedure could be written to drop constraints to conditionally add the values...
CREATE TABLE cust_sample
(
cust_id BIGINT PRIMARY KEY
, cust_name CHAR(50)
, cust_address CHAR(50)
, CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 AND 1000)
, CONSTRAINT chk_id1 CHECK (cust_id BETWEEN 100000 AND 10000000)
)
go
ALTER TABLE cust_sample DROP CONSTRAINT chk_id1
go
INSERT INTO cust_sample
VALUES
(100, 'test', 'test')
gsc_dba
September 19, 2011 at 2:52 am
On the same line, you can also define default values that don't fit the column type/length:
CREATE TABLE #test (
testColumn char(3) DEFAULT 'longtext'
)
INSERT INTO #test DEFAULT VALUES
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
The statement has been terminated.
-- Gianluca Sartori
September 19, 2011 at 3:18 am
I hope there is a confusion with my question.
I am not asking why my insert was falied ...am asking why SQL server accepts such a conflicting range of values..isn't there any validation for that???
thanks
sarat 🙂
Curious about SQL
September 19, 2011 at 4:00 am
hunt (9/19/2011)
I hope there is a confusion with my question.I am not asking why my insert was falied ...am asking why SQL server accepts such a conflicting range of values..isn't there any validation for that???
There may be a genuine reason for accepting such check constraints...
As I mentioned you may want to conditionally insert values for certain events.
gsc_dba
September 19, 2011 at 6:45 am
hunt (9/19/2011)
I hope there is a confusion with my question.I am not asking why my insert was falied ...am asking why SQL server accepts such a conflicting range of values..isn't there any validation for that???
you are certainly able to create constraints that prevent new records if it fits your business logic.
conflicting ranges of values are not something the server would validate...that requires a logical decision... at creation time, SQL can check the references required to apply the constraint, along with it's syntax;
a classic example might be you add a cosntraint simply to make sure no more records get added until you decide to remove the constraint...SQLallows you to do that.
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply