0

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!

  • 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. – fwkion 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. – fwkion 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.