用于检查两个大写字母后跟数值的 SQL 约束

发布于 2024-09-10 09:05:47 字数 438 浏览 14 评论 0原文

我有以下 sql 检查，它接受的值应该是两个大写字母后跟 3 个数字值。

CREATE TABLE Project(
projectID       NCHAR(5)         NOT NULL       PRIMARY KEY,
                                       CHECK(projectID LIKE '[A-Z][A-Z][0-9][0-9][0-9]'),
projectName   NVARCHAR(20)  NOT NULL        UNIQUE,
budget      MONEY           NOT NULL)

如何才能使其只接受大写字母后跟 3 个数字作为项目 ID？ 例如。它应该拒绝像“au123”这样的值并接受像“AU123”这样的值。

原文

i have the following sql check and it accepts values that are supposed to be two uppercase letters followed by 3 numeric values.

CREATE TABLE Project(
projectID       NCHAR(5)         NOT NULL       PRIMARY KEY,
                                       CHECK(projectID LIKE '[A-Z][A-Z][0-9][0-9][0-9]'),
projectName   NVARCHAR(20)  NOT NULL        UNIQUE,
budget      MONEY           NOT NULL)

how do you make it so that it will only accept uppercase letters followed by 3 numbers for the projectID? ex.
it should reject values like 'au123' and accept values like 'AU123'.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

瞄了个咪的 2024-09-17 09:05:47

您需要在 CHECK 约束中声明区分大小写的排序规则：

Create Table Project    
    (
    ProjectId nchar(5) not null Primary Key
    , ProjectName nvarchar(20) not null Unique
    , Budget money not null
    , Constraint CK_Project 
         Check ( ProjectId Like '[A-Z][A-Z][0-9][0-9][0-9]' Collate Latin1_General_CS_AS )
    )

You need to declare a case-sensitive collation in your CHECK constraint:

Create Table Project    
    (
    ProjectId nchar(5) not null Primary Key
    , ProjectName nvarchar(20) not null Unique
    , Budget money not null
    , Constraint CK_Project 
         Check ( ProjectId Like '[A-Z][A-Z][0-9][0-9][0-9]' Collate Latin1_General_CS_AS )
    )

回复收藏 0 原文

十年九夏 2024-09-17 09:05:47

甲骨文10g：

SQL> CREATE TABLE CHECK_CHECK (PROJECT_ID NCHAR(5) NOT NULL);

Table created.

SQL> ALTER TABLE CHECK_CHECK ADD CONSTRAINT CHECK_CHECK_CK01
  2   CHECK (REGEXP_LIKE(PROJECT_ID, '[A-Z][A-Z][0-9][0-9][0-9]', 'c'));

Table altered.

SQL> insert into check_check values ('au123');
insert into check_check values ('au123')
*
ERROR at line 1:
ORA-02290: check constraint (MED_AUDIT.CHECK_CHECK_CK01) violated

SQL> insert into check_check values ('AU123');

1 row created.

Oracle 10g:

SQL> CREATE TABLE CHECK_CHECK (PROJECT_ID NCHAR(5) NOT NULL);

Table created.

SQL> ALTER TABLE CHECK_CHECK ADD CONSTRAINT CHECK_CHECK_CK01
  2   CHECK (REGEXP_LIKE(PROJECT_ID, '[A-Z][A-Z][0-9][0-9][0-9]', 'c'));

Table altered.

SQL> insert into check_check values ('au123');
insert into check_check values ('au123')
*
ERROR at line 1:
ORA-02290: check constraint (MED_AUDIT.CHECK_CHECK_CK01) violated

SQL> insert into check_check values ('AU123');

1 row created.

回复收藏 0 原文

~没有更多了~