Alter table switch statement failed due to check constraints

Question

I have a scenario where i load the flat file into 3 staging tables and switch to partitioned table.

When i try to switch from staging to partition table, am getting the following error. Could you help?

Here are the file group names

PRIMARY
FG1
FG2
FG3
FG4

and here is the partition schema,

CREATE PARTITION FUNCTION PAR_FN (bigint) AS RANGE LEFT FOR VALUES (1,2,3,4)

CREATE PARTITION SCHEME PAR_SCHEMA AS PARTITION PAR_FN TO (
FG1, FG2, FG3, FG4, PRIMARY
)

CREATE TABLE TEMP1
       (O_ORDERKEY bigint NOT NULL,
        O_CUSTKEY  int NOT NULL)
ON PAR_SCHEMA (O_ORDERKEY)

CREATE TABLE TEMP2
       (O_ORDERKEY bigint NOT NULL,
        O_CUSTKEY int NOT NULL)
ON PAR_SCHEMA (O_ORDERKEY)

CREATE TABLE TEMP3
       (O_ORDERKEY bigint not null,
        O_CUSTKEY int not null)
ON PAR_SCHEMA (O_ORDERKEY)

create table TEMP4
       (O_ORDERKEY bigint NOT NULL,
        O_CUSTKEY int NOT NULL)
ON PAR_SCHEMA (O_ORDERKEY)

ALTER TABLE TEMP1 WITH CHECK ADD CONSTRAINT check_TEMP1 
CHECK (O_ORDERKEY <=N'1')

ALTER TABLE TEMP2 WITH CHECK ADD CONSTRAINT check_TEMP2 
CHECK (O_ORDERKEY >N'1' AND O_ORDERKEY <=N'2')

ALTER TABLE TEMP3 WITH CHECK ADD CONSTRAINT check_TEMP3 
CHECK (O_ORDERKEY >N'2' AND O_ORDERKEY <=N'3')

ALTER TABLE TEMP4 WITH CHECK ADD CONSTRAINT check_TEMP4 
CHECK (O_ORDERKEY >N'3' AND O_ORDERKEY <=N'4')

And finally for switching partition,

ALTER TABLE TEMP1  SWITCH TO MAIN PARTITION 1;
ALTER TABLE TEMP2  SWITCH TO MAIN PARTITION 2;
ALTER TABLE TEMP3  SWITCH TO MAIN  PARTITION 3;
ALTER TABLE TEMP4  SWITCH TO MAIN  PARTITION 4;

The only difference between main and staging tables is staging tables have default value on O_ORDERKEY. (i.e 1 for Temp 1 and 2 for temp2 etc)

Answer 1

The definition of the tables to switch data between must be EXACTLY identical (requirements). Any deviation from the table definition will prevent to switch data.

At the moment of switching NO processing will be triggered (which is why switching is so usefull), and the data must still comply to all constraints.

The only way to comply to the conditions of partition switching is to generate both tables with exactly the same DDL script. Only the name of the table and the names of its constraints and indexes must be different.