CHECK contraint

Hi,
Hi, this is the situation.. I have somewhat simplified the tables here that
are insignificant to the structure.
There are organizations, who can be assigned different codes and users (by
an administrator).
These users can access all codes that are assigned to the same organization
that the user himself belongs to.
However, these users (parents) can create sub-users, and assign them a
SUBSET of all the codes that the parent user itself has access to.
So the administrator creates an organization "O" and and assignes user "A".
And assignes codes 1,2 and 3 to this organization. So user A has access to
codes 1, 2 and 3.
Then user A may create one or more users, such as user B, and assign it a
subset of the codes it has access to, such as 2 and 3.
While this type of nesting could go on, in reality we only have 3 types of
users, Admin, A-type users (created by Admin), and B-type users (created by
A-type users).
This is what I came up with:
-- organizations
CREATE TABLE organizations (
`organizationid` INT UNSIGNED PRIMARY KEY
`name` VARCHAR(100)
)
-- each organization can be assigned multiple codes
CREATE codes (
`codeid` INT UNSIGNED PRIMARY KEY,
`organizationid` INT UNSIGNED FOREIGN KEY organizations (`organizationid)
)
-- admins, A-type users and B-type users
CREATE TABLE users (
`userid` INT UNSIGNED PRIMARY KEY,
`userparentid` INT UNSIGNED FOREIGN KEY users (`userid`),
`organizationid` INT UNSIGNED FOREIGN KEY organizations (`organizationid`)
`name` VARCHAR(50)
)
-- map B-users to subset of codes that its parent has access to
CREATE users_codes (
`userid` INT UNSIGNED NOT NULL FOREIGN KEY users (`userid`),
`codeid` INT UNSIGNED NOT NULL FOREIGN KEY codes (`codeid`)
)
This is how I differentiate between Admins, A-type users and B-type users.
Admins don't have a parent id (NULL) and no organization id (NULL).
A-type users have Admins as parent id or NULL, and non-null
organizationid's.
B-type users have parentid's pointing to A-type users and non-null
organization id's.
There's some duplication because B-type id's can get the organizationid from
its parent (A-type) user too.
codes are assigned to organizations, and to B-type users only, not to A-type
users or Admins.
Somehow I am not comfortable with this model. It seems like I am doing
something wrong and there's a better solution.
Anyway, if I do use this model, I need some CHECK constraints on
users_codes, that makes sure that B-type users (with parent-id) can only be
assigned codeids that their parents (A-type users) have access to.
How do I put this in some kind of CHECK constraint?
Please advise on the structure / model, as well as the CHECK constraint(s)
required.
LisaHow about a standard nested set model with nodes that hold the various
codes
CREATE TABLE Tree
(node_id INTEGER NOT NULL
REFERENCES Nodes(node_id),
lft INTEGER NOT NULL UNIQUE
CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE,
CHECK (lft < rgt));
CREATE TABLE Nodes
(node_id INTEGER NOT NULL PRIMARY KEY,
code_1 INTEGER DEFAULT 0 NOT NULL
CHECK (code_1 IN (0,1)),
code_2 INTEGER DEFAULT 0 NOT NULL
CHECK (code_2 IN (0,1)),
code_3 INTEGER DEFAULT 0 NOT NULL
CHECK (code_3 IN (0,1)),
code_4 INTEGER DEFAULT 0 NOT NULL
CHECK (code_4 IN (0,1)),
code_5 INTEGER DEFAULT 0 NOT NULL
CHECK (code_5 IN (0,1))
);
The rule seems to be that you can only inherit codes from a superior.
You could write this with a CREATE ASSERTION in SQL-92; SQL Server
needs a stored procedure that checks for allowed codes before doing an
insertion of a subordinate.|||Interesting concept, but I am having some difficulty grasping how this fits
with my situation. Is the treestructure just for codes only? Users point to
a certain node? An organization (and subusers) could be assigned up to 65536
codes. So it has to be a set, not fields in a table (like the 5 code fields
in your example).
I think my model is more like what I need, but what makes me feel
uncomfortabe is that 3 types of users, Admins, A-type and B-type users, are
all in the same users table. The type of user can only be recognized and
distinguished indirectely by whether organizationid and/or userparentid is
NULL or not.
And A-type users are not linked directely to codes, but are linked to
organizations, and codes are also linked to organizations and via the
organization, A-type users are INDIRECTELY linked to these codes.
But B-type users require a subset of codes, thus they have to be linked
DIRECTELY to the codes table:
( A-type users )===>( Organizations )<===( Codes )<===( B-type users )
Anyway, let me ask you another question... can check constrains refer to
other tables?
CREATE TABLE A (
val INT
)
CREATE TABLE B (
val INT CHECK( val > A(val))
)
something like this?
Lisa
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141180347.823645.141760@.t39g2000cwt.googlegroups.com...
> How about a standard nested set model with nodes that hold the various
> codes
> CREATE TABLE Tree
> (node_id INTEGER NOT NULL
> REFERENCES Nodes(node_id),
> lft INTEGER NOT NULL UNIQUE
> CHECK (lft > 0),
> rgt INTEGER NOT NULL UNIQUE,
> CHECK (lft < rgt));
>
> CREATE TABLE Nodes
> (node_id INTEGER NOT NULL PRIMARY KEY,
> code_1 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_1 IN (0,1)),
> code_2 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_2 IN (0,1)),
> code_3 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_3 IN (0,1)),
> code_4 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_4 IN (0,1)),
> code_5 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_5 IN (0,1))
> );
> The rule seems to be that you can only inherit codes from a superior.
> You could write this with a CREATE ASSERTION in SQL-92; SQL Server
> needs a stored procedure that checks for allowed codes before doing an
> insertion of a subordinate.
>

Check Constraint fails!

Hi Everybody,
Can anybody help me on the following query...
I have a table structure as follows
CREATE TABLE [dbo].[event_logs] (
[WSE_Idx] [int] NULL ,
[WSE_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Date_Generated] [datetime] NULL ,
[WSE_lDate_Generated] [datetime] NULL ,
[WSE_Date_Written] [datetime] NULL ,
[WSE_lDate_Written] [datetime] NULL ,
[WSE_tzname] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Source] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Category] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Event] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_User] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_User_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Computer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Agent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Log_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
go
It contains data. I tried to create the following 'check constraint' to the above table
alter table event_logs
add constraint ck_event_logs
check((WSE_Category = 'application' and wse_log_type in ('Audit Success','error')) OR
(WSE_Category = 'system' and wse_log_type in ('Warning')) OR
(WSE_Category = 'security' and wse_log_type in ('Audit Failure')))
It is giving the following error...
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE CHECK constraint 'ck_event_logs'.
The conflict occurred in database 'slm', table 'event_logs'.
Even I modified the above alter table script as follows, still it is giving the same error.
alter table event_logs
add constraint ck_event_logs
check(WSE_Category like '%applica%')
I created the similar table structure with different table name and applied the check constraint,
it works. No error. Ofcourse table doesn't have data (Empty table).
I have created RULE on this 'event_logs' table (with data). It works fine. No Error.
Can anybody tell me why this 'Check Constraint' is giving problem?.
tks in advance,
vasumData in a table are not valid for 'check constraint' that you specified.
You mast correct data in your table or in ALTER TABLE statement put WITH
NOCHECK option.
Look ALTER TABLE in BOL.
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:748BD3CB-E545-4DEA-B05B-103EEF45BFAE@.microsoft.com...
> Hi Everybody,
> Can anybody help me on the following query...
> I have a table structure as follows
> CREATE TABLE [dbo].[event_logs] (
> [WSE_Idx] [int] NULL ,
> [WSE_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Date_Generated] [datetime] NULL ,
> [WSE_lDate_Generated] [datetime] NULL ,
> [WSE_Date_Written] [datetime] NULL ,
> [WSE_lDate_Written] [datetime] NULL ,
> [WSE_tzname] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Source] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Category] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Event] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_User] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_User_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Computer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Agent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Log_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> go
> It contains data. I tried to create the following 'check constraint' to
the above table
> alter table event_logs
> add constraint ck_event_logs
> check((WSE_Category = 'application' and wse_log_type in ('Audit
Success','error')) OR
> (WSE_Category = 'system' and wse_log_type in ('Warning')) OR
> (WSE_Category = 'security' and wse_log_type in ('Audit Failure')))
> It is giving the following error...
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE CHECK constraint
'ck_event_logs'.
> The conflict occurred in database 'slm', table 'event_logs'.
> Even I modified the above alter table script as follows, still it is
giving the same error.
> alter table event_logs
> add constraint ck_event_logs
> check(WSE_Category like '%applica%')
> I created the similar table structure with different table name and
applied the check constraint,
> it works. No error. Ofcourse table doesn't have data (Empty table).
> I have created RULE on this 'event_logs' table (with data). It works fine.
No Error.
> Can anybody tell me why this 'Check Constraint' is giving problem?.
> tks in advance,
> vasum
>|||thanks for the timely help. I works. I used 'with nocheck' option. Able to create new check constraint and this new check constraint is validating the any new rows coming into the table

Char or varchar for a primary key?

Hi,
I'm planning the structure of a SqlServer 2005 database for a new
application.
The requirement is that primary keys must be "natural"; i.e. in the table
Customers the primary key will be a max. 10 characters string (but the
string may be filled i.e. with only 5 charachters).

Should I define these primary keys as char[5] or varchar[5]?
I'm interested in your opinion in particular about performace issue, because
there will be tables with millions of records...

Thanks,
Davide.>

Quote:

Originally Posted by

Should I define these primary keys as char[5] or varchar[5]?

Sorry, I intended char[10] or varchar [10]|||D. (d@.d.com) writes:

Quote:

Originally Posted by

I'm planning the structure of a SqlServer 2005 database for a new
application.
The requirement is that primary keys must be "natural"; i.e. in the table
Customers the primary key will be a max. 10 characters string (but the
string may be filled i.e. with only 5 charachters).
>
Should I define these primary keys as char[5] or varchar[5]?
I'm interested in your opinion in particular about performace issue,
because there will be tables with millions of records...

char(10) would make sense if key values are almost always 10 characters
long, but if the distribution varies with, say, 5 as the average varchar
would be better.

What sort of strings do you expect? If the values will be digits and upper-
case characters, you way want to consider a binary collation for the column,
at least if your default collation is a Windows collation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"D." wrote:

Quote:

Originally Posted by

>
Hi,
I'm planning the structure of a SqlServer 2005 database for a new
application.
The requirement is that primary keys must be "natural"; i.e. in the table
Customers the primary key will be a max. 10 characters string (but the
string may be filled i.e. with only 5 charachters).
>
Should I define these primary keys as char[5] or varchar[5]?
I'm interested in your opinion in particular about performace issue, because
there will be tables with millions of records...
>
Thanks,
Davide.

Assuming you will not change existing primary key values often (or
ever), the performance between CHAR and VARCHAR comes down to the space
requirements.

VARCHAR claims two bytes for the string length, plus the actual number
of characters in the string. So if the average primary key length
exceeds 8, you are better off with CHAR(10), otherwise you could use
VARCHAR(10).

Because of some other considerations (the 'first' VARCHAR column will
cost an additional 5 bytes per row), when in doubt, I would choose CHAR
over VARCHAR. In your case, I would choose VARCHAR(10) if the average
length is 6 or smaller. Otherwise I would choose CHAR(10).

Gert-Jan|||>

Quote:

Originally Posted by

What sort of strings do you expect? If the values will be digits and
upper-
case characters, you way want to consider a binary collation for the
column,
at least if your default collation is a Windows collation.
>

It sounds good!
Yes, my keys will be only uppercase and digits (some other symbols are
allowed, like dot and hyphen)

How do I set binary collation on a single column?
Do you think that this will improve performance on lookups?
Do you think that having a single column with a different collation will not
decrease performance?

Davide.|||>

Quote:

Originally Posted by

Because of some other considerations (the 'first' VARCHAR column will
cost an additional 5 bytes per row), when in doubt, I would choose CHAR
over VARCHAR. In your case, I would choose VARCHAR(10) if the average
length is 6 or smaller. Otherwise I would choose CHAR(10).
>

Usually the key is fully filled, so I think I'll try to change varchar to
char.

Davide.|||D. (d@.d.com) writes:

Quote:

Originally Posted by

How do I set binary collation on a single column?

With the COLLATE clause:

CREATE TABLE mytable (
col char(10) COLLATE Latin1_General_BIN2 NOT NULL,
...

Quote:

Originally Posted by

Do you think that this will improve performance on lookups?

Yes, since comparison is a straight byte-comparison you gain some cycles,
particularly if your default collation is a Windows collation. It's
diffiuclt to say exactly how much you will gain, because there is a lot
of if depends. For a simple lookup, it's may be only 5-10%. For an
operation as "col LIKE '%str%' it may be drastic as a factor of seven.

If your default collation is an SQL collation (one there the name starts
with SQL), the gain is likely to be so small, that it's not worth the
pain. Note that this only applies if you use char/varchar. For
nchar/nvarchar there is no difference between SQL and Windows collations.

Quote:

Originally Posted by

Do you think that having a single column with a different collation will
not decrease performance?

It will not, but there will be more hassle with programming. And it
would not be the only column with that collation, if there are other
tables with foreign keys to this table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Without more specs, I would go with CHAR(n) and a CHECK() constraint
that uses a regular expression to validate it.