Professional Web Applications Themes

Can I lock only Column? - Microsoft SQL / MS SQL Server

Can I lock a bit column to be ONLY 1 and NEVER 0 for WHATEVER reason ? Thx :) -- == fabriZio...

  1. #1

    Default Can I lock only Column?

    Can I lock a bit column to be ONLY 1 and NEVER 0 for WHATEVER reason ?

    Thx :)

    --
    ==
    fabriZio




    fabriZio Guest

  2. #2

    Default Re: Can I lock only Column?

    Use Check constraint.

    --
    Dejan Sarka, SQL Server MVP
    FAQ from Neil & others at: http://www.sqlserverfaq.com
    Please reply only to the newsgroups.
    PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org

    "fabriZio" <it> wrote in message
    news:phx.gbl... 


    Dejan Guest

  3. #3

    Default Re: Can I lock only Column?

    Yes, no problem, create a CHECK CONSTRAINT on it:

    ALTER TABLE myTable ADD CONSTRAINT CK_Always_1 CHECK (my_bit_column =1)

    Now, why would you want to have a column that always has the same value?
    This doesn't give you any more information then if it wouldn't be there, so
    you might as well not have it there?

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "fabriZio" <it> wrote in message
    news:phx.gbl... 


    Jacco Guest

  4. #4

    Default Re: Can I lock only Column?

    Jacco Schalkwijk (co.uk) writes: 

    I have to confess that I have a couple of columns in my database which
    have check conststraints on the form "col = 3". The serve as foreign keys
    together with another column.

    Say that you have a bunch of entities that all they consist of is an id and
    a name and maybe some more standard attrributes. Rather than having one
    table for each table, you slap them all in the same table:

    CREATE TABLE entities (entitytype char(3) NOT NULL,
    id int NOT NULL,
    name varchar(30) NOT NULL,
    PRIMARY KEY (entitytype, id))

    Then you might have references from another table:

    CREATE TABLE some_other_table
    (someotherid int NOT NULL,
    curentcode char(3) NOT NULL
    CHECK (curentcode = 'CUR') DEFAULT 'CUR',
    curid id NULL,
    fooentcode char(3) NOT NULL
    CHECK (curentcode = 'FOO') DEFAULT 'FOO',
    fooid it NOT NULL,
    ...
    FOREIGN KEY (curentcode, curid)
    REFERENCES (entitytype, id),
    FOREIGN KEY (fooentcode, fooid)
    REFERENCES (entitytype, id))

    I would not say that this is very good database design, and I would
    encourage anyone who considers this idea to forget and go for different
    tables instead. (The situation where we use this in our database is
    different. Still a dubious design, but our solution might be flip
    columns into rows in subtable.)


    Note: in the sample above I am not naming my constraints to save space.
    In real life, you should always name your constraints.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  5. #5

    Default Re: Can I lock only Column?

    > I would not say that this is very good database design, and I would 

    We have a table like that in our database as well, and it gives no end of
    troubles. All kinds of stuff is stored in there which is only vaguely
    related to each other and Referential Integrity is implemented, but not in
    the way you have done it, so it is actually possible to reference the wrong
    codes :S

    You actually more or less confirm my point: if you have a column that only
    contains one value, you should rethink your database design, because it will
    be flawed, or at best 'dubious'. Unfortunately it is not always feasible the
    correct flawed or dubious designs you have inherited, so that's why I am
    stuck for the moment with the table I mentioned above.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Erland Sommarskog" <se> wrote in message
    news:0.0.1... 
    >
    > I have to confess that I have a couple of columns in my database which
    > have check conststraints on the form "col = 3". The serve as foreign keys
    > together with another column.
    >
    > Say that you have a bunch of entities that all they consist of is an id[/ref]
    and 


    Jacco Guest

  6. Moderated Post

    Default Re: Can I lock only Column?

    Removed by Administrator
    Delbert Guest
    Moderated Post

Similar Threads

  1. Converting a 3 column pdf address list to a one column MS word doc
    By Jay_Jump@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 0
    Last Post: February 9th, 06:46 PM
  2. Column chart need column to exceed axis maximum value
    By zanedev in forum Macromedia Flex General Discussion
    Replies: 2
    Last Post: May 8th, 04:26 PM
  3. Bound Column or Template Column (w dAdapater?) in DataGrid
    By Ravichandran Mahalingam in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: January 9th, 03:30 PM
  4. Lock Column Width in Datasheet view?
    By Dale in forum Microsoft Access
    Replies: 3
    Last Post: September 10th, 09:34 PM
  5. Replies: 0
    Last Post: July 7th, 08:32 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139