Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following 2 tables (simplified):

CREATE TABLE dbo.[StagingTable] (
  HostName [nvarchar] (max) NULL
  ,UserName [nvarchar] (max) NULL
  ,AccountType [nvarchar] (max) NULL
)

CREATE TABLE dbo.[FinalDestination] (
  HostName [nvarchar] (128) NOT NULL
  ,UserName [nvarchar] (128) NOT NULL
  ,AccountType [nvarchar] (16) NULL
  CONSTRAINT [PK-FinalDestination] PRIMARY KEY (HostName, UserName)
  ,CONSTRAINT [CK-FinalDestination-AccountType] CHECK ([AccountType] IN ('Personal', 'Shared', 'Service'))
)

The check constraints work just fine for statements like the following where it wouldn't allow the transaction to complete as 'XXX' is not a valid 'AccountType'

INSERT INTO FinalDestination (HostName, UserName, AccountType)
VALUES
('host1', 'user1', 'Personal'),
('host1', 'user2', 'XXX')

But the check constraint is apparently ignored in the following scenario:

INSERT INTO StagingTable (HostName, UserName, AccountType)
VALUES
('host1', 'user1', 'Personal'),
('host1', 'user2', 'XXX')

INSERT INTO FinalDestination (HostName, UserName, AccountType)
SELECT * FROM StagingTable

And I end up with 'XXX' as an account type on 'FinalDestination'.

Is there any way to enforce the CHECK constraint on my INSERT INTO ... SELECT statement? I already have the following options set to Yes:

  • Check Existing Data On Creation Or Re-Enabling = Yes
  • Enforce For INSERTs AND UPDATEs = Yes
  • Enforce For Replication = Yes

Thanks!

share|improve this question
1  
I can't reproduce this. Running the code in your question gives The INSERT statement conflicted with the CHECK constraint "CK-FinalDestination-AccountType". The conflict occurred in database "tempdb", table "dbo.FinalDestination", column 'AccountType'. –  Martin Smith Oct 31 '13 at 10:52
    
Sorry, that was a typo as @AbijithPrakash points out below. I just edited the post to correct it. –  fokion Oct 31 '13 at 12:57
    
My comment wasn't about the typo. I fixed that but the code in the question does not reproduce the issue. Sure your check constraint hasn't been disabled? –  Martin Smith Oct 31 '13 at 12:59
    
That's interesting.. No, my check constraint is not disabled, as I described in my question, trying to insert bad data in the first example produces a conflict as expected, although the second example doesn't... And I have this issue on all my tables where I have check constraints in place. –  fokion Oct 31 '13 at 13:04
    
So are you saying that if you take the code in the question "as is" and run it in Management Studio (in say tempdb) you don't get any error? –  Martin Smith Oct 31 '13 at 13:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.