Home Database Mysql Tutorial SQL Server 2008 查看与创建约束图文教程

SQL Server 2008 查看与创建约束图文教程

Jun 07, 2016 pm 05:49 PM
Check

本文章以图文的方式给大家详细介绍SQL Server 2008 查看与创建约束过程,有需要学习sql约束的朋友可参考本文章哦。


约束主要有一下几种:

NOT NULL : 用于控制字段的内容一定不能为空(NULL)。

UNIQUE : 控件字段内容不能重复,一个表允许有多个 Unique 约束。

PRIMARY KEY: 也是用于控件字段内容不能重复,但它在一个表只允许出现一个。

FOREIGN KEY: FOREIGN KEY 约束用于预防破坏表之间连接的动作,FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。

CHECK: 用于控制字段的值范围。

DEFAULT: 用于设置新记录的默认值。

 

1. PRIMARY KEY约束

在表中常有一列或多列的组合,其值能唯一标识表中的每一行。这样的一列或多列成为表的主键(Primary Key)。一个表只能有一个主键,而且主键约束中的列不能为空值。

查看PRIMARY KEY约束可以在object explorer中依次展开Databases –> 选择你要查看的(在我的例子中是testdatabase) –> Tables –> 你要查看的表(在我的例子中是company)-> Columns

image

如上图所示,Primary Key 有一把金色的小钥匙。companyid 即为company表的primary key。

 

创建PRIMARY KEY约束可以右键点击表,然后选择Design,打开表设计器

image

选中column,点击上面的金色小钥匙,来创建Primary Key。

image

也可以右键点击column,然后选择Set Primary Key。

image

 

 

2. FOREIGN KEY约束

外键(Foreign Key)是用于建立和加强两个表(主表与从表)的一列或多列数据之间的连接的。创建约束的顺序是先定义主表的主键,再对从表定义外键约束。

查看FOREIGN KEY约束,展开Columns,可以看到灰色的小钥匙为Foreign Key;展开Keys,可以看到Foreign Key约束的名字为FK_contact_company。

image

在表设计器中,也可以点击上面的Relationshi按钮,这样就可以查看到所有的Foreign Key约束

image

image

上面的例子可以看到contact 表的companyid为外键,company 表的companyid为主键。

 

下面来演示一下如何创建的该Foreign Key约束。

同样是点击完Relationships按钮之后,在弹出的对话框中选择Add

image

然后点击下面的红色圆圈内的按钮:

image

按下图中那样设置主表、主键和从表、外键

image

然后点击 OK,不要忘记保存你的设计。

image

 

 

3. UNIQUE约束

UNIQUE约束用于确保表中的两个数据行在非主键中没有相同的列值。与PRIMARY KEY约束类似,UNIQUE约束也强制唯一性,但UNIQUE约束用于非主键的一列或多列组合,且一个表可以定义多个UNIQUE约束,另外UNIQUE约束可以用于定义多列组合。

 

还以company table为例,假设我们要约束company name为唯一的,点击Manage Indexes and Keys

image

然后点击Add来添加Unique约束

image

选择column为companyname, Is Unique为Yes。

image

关闭并保存你的设计,这样一个Unique约束就创建好了。

 

 

4. DEFAULT约束

若在表中某列定义了DEFAULT约束,用户在插入新的数据行时,如果该列没有指定数据,那么系统将默认值赋给该列,当然该默认值也可以是空值(NULL)。

 

以contact表为例,在表设计器中,为性别(sex)列填写属性默认值 (‘M’)。

image

 

 

5. CHECK约束

CHECK约束用于限制输入一列或多列的值的范围,通过逻辑表达式来判断数据的有效性。一个列的输入内容必须满足CHECK约束的条件,否则数据无法正常输入。

 

还以contact表为例, 我们要限制sex列的值只能为 ´M´ 或者 ´F´。在表设计器中点击Manage Check Constraints

image

点击Add添加新的constraint

image

点击红圈内的按钮,填写表达式。我们例子中用的表达式是 SEX='M' OR SEX='F'

image

关闭并保存设计。向contact表中插入一行数据,如果sex列的值不为´M´ 或 ´F´, 插入就会报错


删除:

 代码如下 复制代码

在Sql Server、Oracle、MS 删除 Unique 约束语法:drop constraint UniqueName;
在My Sql 删除 Unique 约束语法:drop index UniqueName;

修改:

 代码如下 复制代码
ALTER TABLE EPlatform
ADD CONSTRAINT Unique_EPlatform
unique ([UserId],[Platform]);
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Check out the steps to delete a logged-in device on Douyin Check out the steps to delete a logged-in device on Douyin Mar 26, 2024 am 09:01 AM

1. First, click to open the Douyin app and click [Me]. 2. Click the three-dot icon in the upper right corner. 3. Click to enter [Settings]. 4. Click to open [Account and Security]. 5. Select and click [Log in to device management]. 6. Finally, click to select the device and click [Remove].

How to easily check the installed version of Oracle How to easily check the installed version of Oracle Mar 07, 2024 am 11:27 AM

How to easily check the installed version of Oracle requires specific code examples. As a software widely used in enterprise-level database management systems, the Oracle database has many versions and different installation methods. In our daily work, we often need to check the installed version of the Oracle database for corresponding operations and maintenance. This article will introduce how to easily check the installed version of Oracle and give specific code examples. Method 1: Through SQL query in the Oracle database, we can

How to check your own ID on Xianyu_Introduction to how to check your personal nickname on Xianyu How to check your own ID on Xianyu_Introduction to how to check your personal nickname on Xianyu Mar 22, 2024 am 08:21 AM

As a trading platform, Xianyu requires you to register and log in to your account before using it. Users can set an ID name for their account. What if they want to check what their ID is? Let’s find out together below! Introduction to how to view personal nicknames on Xianyu. First, start the Xianyu app. After entering the homepage, switch to the page of selling idle, messages, and me, and click the [My] option in the lower right corner. 2. Then on my page we need to click [Avatar] in the upper left corner; 2. Then when we go to the personal homepage page we can see different information, we need to click the [Edit Information] button here; 4. Finally click We can see it later on the page where we edit information;

Where to check music rankings on NetEase Cloud Music_How to check music rankings on NetEase Cloud Music Where to check music rankings on NetEase Cloud Music_How to check music rankings on NetEase Cloud Music Mar 25, 2024 am 11:40 AM

1. After turning on the phone, select NetEase Cloud Music. 2. After entering the homepage, you can see the [Ranking List] and click to enter. 3. In the ranking list, you can select any list and click [New Song List]. 4. Select your favorite song and click on it. 5. Return to the previous page to see more lists.

How to check how many groups you have joined on WeChat: a simple step How to check how many groups you have joined on WeChat: a simple step Mar 26, 2024 am 10:06 AM

Regardless of life or work, many people have long been deeply tied to WeChat and will be pulled into various groups at any time. So how many WeChat groups have you joined? You may immediately want to view the group chats in your address book, but only the WeChat groups you have saved in your address book will appear there, and other groups will not be visible. If you want to see all the WeChat groups you have joined, it is very simple: enter your nickname in the search box on the WeChat homepage, then find the group chat section in the search results, and click "More Group Chats" to view all related group chat information. Anyway, I was shocked. There were more than a hundred of them, and the scroll bar on the right became very small. Unfortunately, there is no specific number statistics... This method is also applicable to checking the QQ groups you have joined. PS: Some netizens also provided a trick:

How to view the hot list of Kuaishou Live Companion videos How to view the hot list of Kuaishou Live Companion videos Mar 29, 2024 pm 08:09 PM

Kuaishou Live Companion is not only a powerful live broadcast auxiliary tool, but also a real-time insight platform for hot topics and trends created for broadcasters. Through this function, anchors can quickly capture the content that audiences are most concerned about, and then adjust the live content to make it more in line with the audience's tastes and interests. So how to check the hot video list in the Kuaishou Live Companion app? This tutorial guide will provide you with a detailed introduction to the steps. I hope it can help you. How to view the hot video list on Kuaishou Live Companion? The second step is to click on the daily video hot list. The third step is to check the daily video hot list.

How to view the Amap Help Center_How to view the Amap Help Center How to view the Amap Help Center_How to view the Amap Help Center Apr 01, 2024 pm 05:26 PM

1. We first open the Gaode map. 2. Then click (My) in the lower right corner of the Amap homepage and then click Settings in the upper right corner. 3. Finally, you can see the help center of Amap.

How do I check which groups I have joined? How do I check which groups I have joined? Apr 01, 2024 pm 05:34 PM

WeChat group chat is not only a simple chat platform, but also a communication circle that brings together elites and enthusiastic friends from all walks of life. So today I will teach you how to see how many groups you have added on WeChat and how to save group chats. Usually Users who use WeChat must not miss it. How to check how many groups you have added to WeChat and how to save group chats To check how many groups you have added to WeChat: 1. You can view your group chat window in the WeChat main interface 2. If you have already saved the group chat, you can tap [ Address Book] - [Group Chat] 3. After entering the group chat, you can view the saved group. Save the WeChat group: 1. Select the group you want to save, top right [...] 2. Open in the chat message [Save to address book] 3. On the main WeChat interface, tap [Address Book]-[Group Chat] to view

See all articles