1. <legend id='J94Qt'><style id='J94Qt'><dir id='J94Qt'><q id='J94Qt'></q></dir></style></legend><tfoot id='J94Qt'></tfoot>

    <small id='J94Qt'></small><noframes id='J94Qt'>

    <i id='J94Qt'><tr id='J94Qt'><dt id='J94Qt'><q id='J94Qt'><span id='J94Qt'><b id='J94Qt'><form id='J94Qt'><ins id='J94Qt'></ins><ul id='J94Qt'></ul><sub id='J94Qt'></sub></form><legend id='J94Qt'></legend><bdo id='J94Qt'><pre id='J94Qt'><center id='J94Qt'></center></pre></bdo></b><th id='J94Qt'></th></span></q></dt></tr></i><div id='J94Qt'><tfoot id='J94Qt'></tfoot><dl id='J94Qt'><fieldset id='J94Qt'></fieldset></dl></div>
      <bdo id='J94Qt'></bdo><ul id='J94Qt'></ul>

    1. 添加检查单独(链接)表的值的约束

      时间:2023-11-28
        <tbody id='THFNm'></tbody>

      <small id='THFNm'></small><noframes id='THFNm'>

      <tfoot id='THFNm'></tfoot>

          <legend id='THFNm'><style id='THFNm'><dir id='THFNm'><q id='THFNm'></q></dir></style></legend>

              <bdo id='THFNm'></bdo><ul id='THFNm'></ul>
            • <i id='THFNm'><tr id='THFNm'><dt id='THFNm'><q id='THFNm'><span id='THFNm'><b id='THFNm'><form id='THFNm'><ins id='THFNm'></ins><ul id='THFNm'></ul><sub id='THFNm'></sub></form><legend id='THFNm'></legend><bdo id='THFNm'><pre id='THFNm'><center id='THFNm'></center></pre></bdo></b><th id='THFNm'></th></span></q></dt></tr></i><div id='THFNm'><tfoot id='THFNm'></tfoot><dl id='THFNm'><fieldset id='THFNm'></fieldset></dl></div>
                本文介绍了添加检查单独(链接)表的值的约束的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                我有两张桌子:

                Book(BookID, Title, Author, Decision)

                BookShipment(BookID, ShipmentID)

                CREATE TABLE BookShipment(
                BookID CHAR(4),
                ShipmentID(7)
                CONSTRAINT pk_BookShipment PRIMARY KEY (BookID, ShipmentID),
                CONSTRAINT fk_BookShipment_Book FOREIGN KEY (BookID) REFERENCES Book(BookID));
                

                这个想法是一本书需要批准";在将其添加到货件之前.如果它被拒绝"它不会被添加.

                The idea is that a Book needs to be "Approved" before it's added to a Shipment. If it's "Rejected" it won't be added.

                有没有办法向 BookShipment 添加额外的约束,当添加新的 BookID 时,会检查 Decision 下的 DecisionBook 表等于 Approved(对于那个 BookID)?

                Is there a way to add an additional constraint to BookShipment that, when a new BookID is added, would check that Decision under the Book table is equal to Approved (for that BookID)?

                推荐答案

                如果你总是有一个状态要检查,这可以通过 FK 约束的一些小技巧来完成:

                If you'll always have a single status to check, this can be done with little tricks on FK constraint:

                • Books(BookId, Decision) 上创建虚拟唯一索引.
                • 将计算列添加到 BookShipment,值为 Approved.
                • 在 FK 约束中引用创建的唯一索引.
                • Create dummy unuque index on Books(BookId, Decision).
                • Add calculated column to BookShipment with value Approved.
                • Reference the created unique index in FK constraint.

                CHECK约束中定义UDF应该是更灵活的方式.

                Defining UDF in CHECK constraint should be more flexible way for this.

                create table book (
                  BookID int identity(1,1) primary key,
                  Title varchar(100),
                  Author varchar(100),
                  Decision varchar(100),
                  
                  --Dummy constraint for FK
                  constraint u_book unique(bookid, decision)
                );
                
                CREATE TABLE BookShipment(
                  BookID int,
                  ShipmentID varchar(7),
                  --Dummy column for FK
                  approved as cast('Approved' as varchar(100)) persisted
                  
                  CONSTRAINT pk_BookShipment PRIMARY KEY (BookID),
                  CONSTRAINT fk_BookShipment_Book_Approved
                    FOREIGN KEY (BookID, approved)
                    REFERENCES Book(BookID, decision)
                );
                
                insert into book (Title, Author, Decision)
                select 'A', 'B', 'Approved' union all
                select 'A', 'B', 'New'
                ;
                
                --2 rows affected
                
                insert into BookShipment values(1, 1);
                
                --1 rows affected
                
                insert into BookShipment values(2, 2);
                
                /*
                
                insert into BookShipment values(2, 2);
                
                
                Msg 547 Level 16 State 0 Line 1
                The INSERT statement conflicted with the FOREIGN KEY constraint "fk_BookShipment_Book_Approved". The conflict occurred in database "fiddle_ea408f09b06247a78b47ea9c353eda10", table "dbo.book".
                Msg 3621 Level 0 State 0 Line 1
                The statement has been terminated.
                */
                

                db<>fiddle 这里

                这篇关于添加检查单独(链接)表的值的约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                上一篇:SQL Server:一张表有 400 列还是 40 表有 10 列? 下一篇:多少数据库索引太多了?

                相关文章

                <tfoot id='VuRiS'></tfoot>

                1. <i id='VuRiS'><tr id='VuRiS'><dt id='VuRiS'><q id='VuRiS'><span id='VuRiS'><b id='VuRiS'><form id='VuRiS'><ins id='VuRiS'></ins><ul id='VuRiS'></ul><sub id='VuRiS'></sub></form><legend id='VuRiS'></legend><bdo id='VuRiS'><pre id='VuRiS'><center id='VuRiS'></center></pre></bdo></b><th id='VuRiS'></th></span></q></dt></tr></i><div id='VuRiS'><tfoot id='VuRiS'></tfoot><dl id='VuRiS'><fieldset id='VuRiS'></fieldset></dl></div>

                    <bdo id='VuRiS'></bdo><ul id='VuRiS'></ul>
                    <legend id='VuRiS'><style id='VuRiS'><dir id='VuRiS'><q id='VuRiS'></q></dir></style></legend>
                  1. <small id='VuRiS'></small><noframes id='VuRiS'>