Posted by Tony Rogerson on 10/31/06 08:12

> I can insert the same user data 1000 times and you will not
> detect the redundancy. You have no defaults or constraints. What the

Then put a unique constraint on the column that has the IDENTITY propety.

> email_addr VARCHAR(50) NOT NULL
> CHECK (<<grep pattern match>>),

How on earth are you going to do a <<grep pattern match>> when SQL Server
can only access external stuff like that via CLR? Remember you are the
advocate who says there should be no CLR, everything should be standard SQL.

Like the other post in this group we are waiting on an answer - this can
easily, supported and maintainable oh and re-useable outside the database
using a CLR function and the regex .NET class.

> password VARCHAR(20) NOT NULL
> CHECK (LEN(password) > 5), -- other rules?

Again, for implementing a strict password (simulate windows strict policy)
how would you do that in standard SQL in a constraint without resorting to
lots of LIKES, CASTS and CASE statements? Short answer- you can't without
using a CLR function again.

> What you did was mimic a deck of punch cards or a magnetic tape file.
> Clean up the data element and get yourself a key and constraints, more
> like this:

What you advocate is dated programming techniques, not following Microsofts
recommendations on product use and not following sound and professional
strategies for development, maintainability and support - sounds like a
cowboy approach to me.

> email_addr VARCHAR(50) NOT NULL

Where is it defined that an email address can only be 50 characters long? Is
that an industrial standard?

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials


"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1162243618.005560.50800@f16g2000cwb.googlegroups.com...
> Did you notice that you have put the prefix "fld-" on all the columns?
> This is not just a great way to destroy a data dictioanry and violate
> ISO-11179 rules, but it also tells us that you have not idea waht
> columns are nothing like fields. Likewise, the silly, redundant "tbl-"
> prefix.
>
> You have no key on the table. Identity cannot ever be a relational
> key. I can insert the same user data 1000 times and you will not
> detect the redundancy. You have no defaults or constraints. What the
> he3ck is a batch? It looks like a flag of some kind, but we do not use
> those in SQL.
>
> What you did was mimic a deck of punch cards or a magnetic tape file.
> Clean up the data element and get yourself a key and constraints, more
> like this:
>
> CREATE TABLE AdminUsers
> (user_name VARCHAR(20) NOT NULL PRIMARY KEY,
> password VARCHAR(20) NOT NULL
> CHECK (LEN(password) > 5), -- other rules?
> full_name VARCHAR(50) NOT NULL, -- trim spaces?
> permission_code INTEGER DEFAULT 0 NOT NULL,
> email_addr VARCHAR(50) NOT NULL
> CHECK (<<grep pattern match>>),
> user_initials VARCHAR(3) DEFAULT ' ' NOT NULL,
> lastlogon_date DATETIME
> DEFAULT CURRENT_TIMESTAMP NOT NULL,
> batch_foobarflag CHAR(1) NOT NULL); what is it?
>

[Back to original message]