Create a table with data validation based on 2 or more columns-CodePudding

I have been trying to create a production ERP using C# and SQL Server.

I want to create a table where the insert statement should only occur when at least one of the 3 main columns have a different value.

The main columns are prod_date, order_no, mach_no, shift_no, prod_type. If all the values are repeated a second time the data must not be entered.

create table p1_order(id int not null,
                      order_no int not null,
                      prod_date date notnull,
                      prod_type nvarchar(5),
                      shift_no int not null,
                      mach_no nvarchar(5) not null,
                      prod_qty,
                      float not null)

CodePudding user response：

Based on the information you provided, You should check for the identical values when executing insert query, while writing your code. for example you can write:

if(prod_date == order_no == mach_no)// or any other 3 variables
{
 //error of identical values
}
else{
// your insert query
}

CodePudding user response：

The best way to implement this is by creating a unique constraint on the table.

alter table p1_order
add constraint UC_Order unique (prod_date,order_no,mach_no,shift_no,prod_type);

Due to some reason, if you are not able to create a unique constraint, you can write your query like the following using NOT EXISTS

insert into p1_order (order_no , prod_date , prod_type <remaining columns>) 
select 123, '2022-09-20 15:11:43.680', 't1' <remaining values>
where not exists 
    (select 1
     from p1_order 
     where order_no = 123 AND prod_date = '2022-09-20 15:11:43.680' <additional condition>)

CodePudding user response：

A trigger can rollback the insert transaction:

CREATE TRIGGER myTrigger
ON dbo.p1_order
AFTER INSERT
AS
BEGIN
    DECLARE @prod_date DATE, @order_no INT, @mach_no NVARCHAR(5), @shift_no INT, @prod_type NVARCHAR(5);

    SELECT @prod_date = Inserted.prod_date, @order_no = Inserted.order_no, @mach_no = Inserted.mach_no, @shift_no = Inserted.shift_no, @prod_type = Inserted.prod_type
    FROM Inserted;

    IF (conditions does NOT meet)
        THEN RAISERROR
END;