您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > id列创建主键

id列创建主键

发布时间:2019-10-17 20:47编辑:威尼斯官方网站登录浏览(74)

    约束

    主关键字约束(Primary Key Constraint)

    用来指定表中的一列或几列组合的值在表中具有唯一性。建立主键的目的是让外键来引用。

    Primary Key的创建方式

    在创建表时创建Primary Key

    CREATE TABLE table1(
        t_id VARCHAR(12) ,
        t_name VARCHAR(20),
        t_phone VARCHAR(20),
        CONSTRAINT t_idss PRIMARY KEY(t_id)
    );
    

    对t_id列创建主键,约束名为t_idss。

    删除Primary Key
    ALTER TABLE table1
    DROP CONSTRAINT t_idss;
    

    约束名与列名不一致,此处填写约束名

    向已有表中添加Primary Key
    ALTER TABLE table1
    ADD CONSTRAINT t_idss
    PRIMARY KEY(t_id);
    
    添加Primary Key的另一种示例
    ALTER TABLE Products
    ADD PRIMARY KEY(prod_id);
    

    虽然上述代码运行没问题,查看表格设计也可以看到Primary Key设置成功,但是在删除Primary Key操作时会提示:
    消息3728,级别16,状态1,第1 行
    'prod_id' 不是约束。
    消息3727,级别16,状态0,第1 行
    未能删除约束。请参阅前面的错误信息。
    原因是添加Primary Key语句中没有用CONSTRAINT指明约束名,系统自动生成了主键名和约束名,要先查看主键名和约束名,删除时填写的也是约束名。
    这种情况的正确删除方法

    ALTER TABLE Products
    DROP CONSTRAINT CK__Products__prod_p__1A14E395;
    ALTER TABLE Products
    DROP CONSTRAINT PK__Products__56958AB222AA2996;
    
    多列组合添加主键约束
    CREATE TABLE table1(
        t_id VARCHAR(12),
        s_id VARCHAR(20),
        score FLOAT,
        CONSTRAINT ts_id PRIMARY KEY(t_id,s_id)
    );
    
    外关键字约束(Foreign Key Constraint)

    定义了表之间的关系,用来维护两个表之间的一致性的关系。
    在创建表时创建Foreign Key Constraint

    CREATE TABLE table2(
        s_id VARCHAR(20),
        s_name VARCHAR(12),
        s_tellphone VARCHAR(11),
        s_address VARCHAR(20),
        CONSTRAINT PK_s_id PRIMARY KEY(s_id),
    );--首先新建table2,设置s_id为主键
    
    CREATE TABLE table1(
        t_id VARCHAR(12),
        s_id VARCHAR(20),
        score FLOAT,
        CONSTRAINT pk_ts_id PRIMARY KEY(t_id,s_id),--新建table1,对t_id和s_id设置联合主键,键名pk_ts_id
        CONSTRAINT fk_s_id FOREIGN KEY(s_id)--对s_id设置外键fk_s_id
        REFERENCES table2(s_id)--外键fk_s_id外键关联table2的列s_id
        ON DELETE CASCADE--设置在table1的s_id删除时table2的s_id同时删除
        ON UPDATE CASCADE--设置在table1的s_id更新时table2的s_id同时更新
    );
    

    注:对table1设置外键关联table2,在插入数据时需要先插入table2的数据,才能成功插入table1的数据。更改table2.s_id数据,table1.s_id数据也会自动改变。但是更改table1.s_id数据,执行时报外键冲突。总之对table1设置外键关联table2后,table1的数据跟着table2走,不能反着来。

    添加和删除外键约束同主键。

    UNIQUE约束

    除主键外另一种可以定义唯一约束的类型,允许空值。添加UNIQUE的方法同上,这里只简单举例。

    USE test
    GO
    ALTER TABLE table2
    ADD CONSTRAINT uk_s_tellphone
    UNIQUE(s_tellphone);
    
    CHECK约束

    分为表约束和列约束,用于限制字段值在某个范围。

    添加CHECK约束
    ALTER TABLE table2
    ADD sex CHAR(2);--在table表中添加sex,数据类型为CHAR,用来存放性别
    GO
    ALTER TABLE table2
    ADD CONSTRAINT ck_sex CHECK(sex in('男','女'));
    

    注:此时sex列数据类型不能是bit,如果填写bit,只能存储0和1,用CHECK约束限制结果为男和女就会报错。

    在创建表的时候添加CHECK 约束
    CREATE TABLE table3(
        t3_id VARCHAR(12),
        t3_name VARCHAR(20),
        t3_class VARCHAR(12),
        t3_type VARCHAR(12),
        CONSTRAINT ck_t3_type CHECK(t3_type in('类型1','类型2','类型3')) 
    )
    

    添加了约束后如果插入不符合约束的数据

    INSERT INTO table3(
        t3_id,
        t3_name,
        t3_class,
        t3_type
    )VALUES(
    '2018038219',
    '李建',
    '社会与科学',
    '任何数据'
    );
    

    消息547,级别16,状态0,第1 行
    INSERT 语句与CHECK 约束"ck_t3_type"冲突。该冲突发生于数据库"test",表"dbo.table3", column 't3_type'。
    语句已终止。

    删除CHECK约束
    ALTER TABLE table3
    DROP CONSTRAINT ck_t3_type;
    
    DEFAULT约束

    通过定义列的默认值或使用数据库的默认值对象绑定表列,来指定列的默认值。

    在建表时添加DEFAULT约束
    CREATE TABLE table3(
        t3_id VARCHAR(12),
        t3_name VARCHAR(20),
        t3_class VARCHAR(12),
        t3_type VARCHAR(12) DEFAULT '类型1' 
    )
    
    删除DEFAULT约束
    ALTER TABLE table3
    DROP CONSTRAINT DF__table3__t3_type__3D5E1FD2;
    --DF__table3__t3_type__3D5E1FD2是DEFAULT约束的约束名
    
    添加约束不指定约束名
    ALTER TABLE table3
    ADD DEFAULT '类型2' FOR t3_type;
    GO
    
    添加约束指定约束名
    ALTER TABLE table3
    ADD CONSTRAINT df_t3_type
    DEFAULT '类型2' FOR t3_type;
    GO
    
    NOT NULL约束

    约束字段值不为空。

    建表时设置NOT NULL约束
    CREATE TABLE table3(
        t3_id VARCHAR(12) NOT NULL,
        t3_name VARCHAR(20) NOT NULL,
        t3_class VARCHAR(12) NOT NULL,
        t3_type VARCHAR(12) NOT NULL 
    )
    
    为已存在的列添加NOT NULL约束
    ALTER TABLE table3
    ALTER COLUMN t3_type VARCHAR(12) NOT NULL;
    
    删除NOT NULL约束
    ALTER TABLE table3
    ALTER COLUMN t3_type VARCHAR(12) NULL;
    GO
    

    自定义默认值对象维护数据完整性

    CREATE DEFAULT date_today AS GETDATE();
    --新建默认值对象名date_today,默认值为getdate()函数,获取当前日期
    GO
    EXEC sp_addtype date_time,'date','NULL';
    --利用存储过程新建自定义数据类型date_time,参照系统数据类型date
    GO
    EXEC sp_bindefault 'date_today','date_time';
    --将默认值对象date_today绑定到自定义数据类型date_time上
    GO
    CREATE TABLE table3(--新建table3,设置字段t3_date的数据类型为date_time
        t3_id VARCHAR(12),
        t3_name VARCHAR(20),
        t3_class VARCHAR(12),
        t3_type VARCHAR(12),
        t3_date date_time
    );
    GO
    INSERT INTO table3(--为新建表table3插入一条数据,不指定t3_date的值,看默认值是否有效
        t3_id,
        t3_name,
        t3_class,
        t3_type
    )VALUES(
    '2018038220',
    '李建',
    '社会与科学',
    '类型1'
    );
    GO
    SELECT * FROM table3;--查询table3数据,看t3_date是否有默认值为当前日期
    

    查询结果如下
    图片 1

    ALTER TABLE table3
    ADD t3_date1 DATE;--在table3表中新增一列t3_date1,数据类型为DATE
    GO
    EXEC sp_bindefault 'date_today','table3.t3_date1';
    --直接将默认值对象date_today绑定到table3的t3_date1列
    GO
    INSERT INTO table3(--为新建表table3插入一条数据,不指定t3_date和t3_date1的值,看默认值是否有效
        t3_id,
        t3_name,
        t3_class,
        t3_type
    )VALUES(
    '2018038221',
    '李建',
    '社会与科学',
    '类型'
    );
    GO
    SELECT * FROM table3;
    GO
    

    查询结果如下
    图片 2

    存储过程查询默认值对象的所有者
    USE test
    EXEC sp_help date_today;
    GO
    

    结果如图所示
    图片 3

    删除默认值对象
    DROP DEFAULT date_today;
    

    删除不成功,提示以下信息:
    消息3716,级别16,状态3,第1 行
    无法删除默认值'date_today',因为它已绑定到一个或多个列。
    那么我们知道,当一个默认值对象绑定了列之后,就无法删除它,如果想要删除,就必须先解绑。在上面的操作中,我们的默认值对象date_today绑定了test数据库table3表的t3_date1字段。

    对列解绑默认值对象
    USE test
    GO
    EXEC sp_unbindefault 'table3.t3_date1';
    

    此时我们再次尝试删除默认值对象,发现还是不行,此时要注意,在上面的操作中,默认值对象date_today不止绑定了t3_date1列,还绑定了自定义数据类型date_time,并将该数据类型定义给了t3_date列,我们还需要对date_time解绑默认值对象。

    EXEC sp_unbindefault 'date_time';
    

    此时再次删除默认值对象,就可以成功删除。删除默认值对象后,原先绑定的字段不会再有默认值。

    自定义规则维护数据完整性

    规则是对列或自定义数据类型的值的规定和限制。自定义规则的表达式一定要返回布尔类型的值,并且表达式中不能包含多个变量。

    CREATE RULE score_rule AS @math_score>=0;
    GO--新建规则score_rule,参数@math_score
    EXEC sp_addtype 'score_type','float','NULL';
    GO--新建自定义数据类型score_type
    CREATE TABLE table_score(--新建表table_score,预设mt_score和at_score字段用于绑定规则
    s_id VARCHAR(4),
    s_name VARCHAR(10),
    mt_score float,--该字段将用于规则score_rule绑定到列
    at_score score_type--该字段将用于规则score_rule绑定到自定义数据类型
    );
    GO
    EXEC sp_bindrule 'score_rule','score_type';
    GO--将score_rule规则绑定到自定义数据类型score_type
    EXEC sp_bindrule 'score_rule','table_score.mt_score';
    GO--将score_rule规则绑定到table_score表的mt_score列
    
    ----以下进行规则测试
    INSERT INTO table_score(
    s_id,
    s_name,
    mt_score,
    at_score
    )VALUES(
    '0001',
    '张华',
    '-1',
    '-1'
    );
    GO
    

    进行违反规则的插入后,数据库报错
    消息513,级别16,状态0,第1 行
    列的插入或更新与先前的CREATE RULE 语句所指定的规则发生冲突。该语句已终止。冲突发生于数据库'test',表'dbo.table_score',列'mt_score'。
    语句已终止。
    很明显,mt_score的插入值为-1,违反了必须大于等于0的规则,数据库报错。将mt_score的插入值改成符合规则的数据,再次运行插入语句,数据库依然会报错,因为at_score字段的插入值也是违反规则的。将两个数据改成符合规则的返回,执行成功。

    注:新建规则时表达式一定要是返回布尔类型的值,否则会报错

    消息4145,级别15,状态1,过程sum_score,第1 行
    在应使用条件的上下文(在';' 附近)中指定了非布尔类型的表达式。

    删除自定义规则

    和自定义默认值对象一样,删除自定义规则要求该规则先与字段和自定义数据类型解绑。在上面的操作中,score_rule规则与自定义数据类型score_type以及列mt_score已绑定。因此执行以下语句:

    EXEC sp_unbindrule 'score_type';
    GO--解除规则score_rule与score_type之间的绑定
    EXEC sp_unbindrule 'table_score.mt_score';
    GO--解除规则score_rule与表table_score的mt_score列的绑定
    DROP RULE score_rule;--删除score_rule规则
    

    注:经过试验,一个列只能绑定1条规则,如果对一个列绑定2条规则,前一条规则会被后一条规则顶替。

    查看自定义规则
    EXEC sp_help 'score_rule';
    

    结果如图所示
    图片 4

    查看自定义规则的定义信息
    EXEC sp_helptext 'score_rule';
    GO
    

    结果如图所示
    图片 5

    本文由威尼斯官方网站发布于威尼斯官方网站登录,转载请注明出处:id列创建主键

    关键词: