Database Fundamentals-(SQL Server) constraints

Database
definition : Some data files stored on the hard disk

Memory:
Some data that the computer temporarily stores

. Net-sql Server
Php-mysql
Java-oreacl

Data types in the database:
int decimal (length, number of decimal digits)

Bit-Boolean type 1-true 0-false

DateTime-Time Date type

nvarchar (length) Max string
varchar (length)

Text-long text
-------------------------------------------
relational database
Four types of constraints:

1. PRIMARY KEY constraint:
Set a column as the primary key column
Duplicate is not allowed, NULL is not allowed, a table has only one primary key column

2. Foreign KEY constraints: (the most important constraint for establishing relationships between tables and tables)
One of the columns of the current table is constrained by one of the primary key columns/unique columns of the other table
The contents of the column in which the current table is constrained are not allowed to exceed the contents of the column that constrains it

To constrain columns of other tables as primary constraint columns, this column must be a primary key column/unique column/

3, the only constraint:

Set a column to be unique, so that the data for this column cannot be duplicated

4. Check constraint:
Write an expression that constrains the range of values in a column
&&-
|| -OR

------------------------------
Homework:

1. Common Database types

1. IBM's DB2

As a pioneer and pilot in the field of relational databases, IBM completed the prototype of System R systems in 1997, and in 1980 began providing integrated database server--SYSTEM/38, followed by Sql/dsforvse and VMS, Its initial version is closely related to the SYSTEMR research prototype. DB2 ForMVSV1 was launched in 1983. The goal of this release is to provide the simplicity that this new scenario promises, data uncorrelated and user productivity. 1988 DB2 for MVS provides robust online transaction processing (OLTP) support, and distributed database support in 1989 and 1993, respectively, with remote units of work and distributed work units. The recently launched DB2 Universal Database 6.1 is a model of the general-purpose databases and is the first multimedia relational database management system with online capabilities to support a range of platforms including Linux.

2. Oracle

Oracle, formerly known as SDL, was founded in 1977 by Larry Ellison and two other programmers who developed their own blockbuster products and sold them in the market, and in 1979 Oracle introduced the first commercial SQL relational database management system. Oracle is one of the first vendors to develop a relational database with products that support the widest range of operating system platforms. Oracle relational database products are at the forefront of market share.

3. Informix

Informix was established in 1980 to provide professional relational database products for open operating systems such as UNIX. The company's name, Informix, is a combination of information and UNIX. Informix's first relational database product that really supports the SQL language is Informix SE (standardengine). Informixse is the main database product in the computer UNIX environment at that time. It is also the first commercial database product to be ported to Linux.

4. Sybase

Sybase was founded in 1984 and the company name "Sybase" is taken from the combination of "system" and "database". Bob Epstein, one of the founders of Sybase, is the main designer of the Ingres University Edition (relational database model product with SYSTEM/R). The company's first relational database product was the Sybase SQLServer1.0 launched in May 1987. Sybase first put forward the idea of Client/server database architecture, and was the first to implement it in Sybase SQL Server.

5. SQL Server

In 1987, Microsoft and IBM co-developed OS/2,IBM to bind Os/2database Manager in the OS/2 extendededition system it sells, while the Microsoft product line still lacks a database product. To this end, Microsoft has looked to Sybase, signed a cooperation agreement with Sybase, using Sybase technology to develop a relational database based on the OS/2 platform. In 1989, Microsoft released SQL Server version 1.0.

6. PostgreSQL

PostgreSQL is the object of a very full-featured free software-relational database management system (ORDBMS), many of which are the predecessor of many commercial databases today. PostgreSQL first started in the BSD Ingres project. The features of PostgreSQL cover the sql-2/sql-92 and SQL-3. First, it includes support for the world's richest data types, and secondly, PostgreSQL is the only database management system for free software that supports transactions, subqueries, multi-version parallel control systems, and data integrity checks.

7.mySQL

MySQL is a small relational database management system, the developer of the Swedish MySQL AB company. was acquired by Sun Company on January 16, 2008. MySQL is now widely used in small and medium-sized websites on the internet. Because of its small size, fast speed, low total cost of ownership, especially the open source, many small and medium-sized web sites in order to reduce the total cost of ownership of the site chose MySQL as the site database. The web site of MySQL's official website is: www.mysql.com

2, search the characteristics of the relational database

The relational database is a database based on the relational database model, which uses concepts and methods such as set algebra to process data in a database, and is organized into a set of formally descriptive tables in which the essence of the form is the special collection of data items. The data in these tables can be accessed or re-convened in many different ways without the need to reorganize the database tables. The definition of a relational database results in a table of metadata or a formal description of tables, columns, scopes, and constraints. Each table (sometimes referred to as a relationship) contains one or more data types that are represented by columns. Each row contains a unique data entity, which is the kind that is defined by the column. When creating a relational database, you can define the range of possible values for a data column and further constraints that may be applied to that data value.
3. Summary of four constraints

1. CHECK constraint

In a database, a CHECK constraint is a data value or data format that is acceptable in one or some of the columns in a constraint table. For example, you can require that the postcode column of the authors table allow only six-digit ZIP codes to be entered.

A check constraint can be applied to one or more columns, or multiple check constraints can be applied to a column.

When a table is dropped, the CHECK constraint on the table is also removed.

restrictions on CHECK constraints

A check constraint does not accept a value that evaluates to FALSE. Because null values are evaluated as UNKNOWN, if these values exist in an assignment expression, the constraint may be overwritten without effect. For example, suppose you apply a constraint to an int column MyColumn, specifying that MyColumn can only contain a value of 10 (that is, MyColumn = 10). If you insert a value of NULL into MyColumn, the database engine inserts null and does not return an error.

If the condition checked by the CHECK constraint is not FALSE for any row in the table, it returns TRUE. If the table you just created does not have any rows, any CHECK constraints on this table are considered valid. This situation can produce unexpected results, as shown in the following example.

CREATE TABLE checktbl (col1 int, col2 int); Gocreate FUNCTION checkfnctn () RETURNS intas BEGIN DECLARE @retval int SELECT @retval = COUNT (*) from CHECKTBL RETURN @retv Alend; Goalter TABLE checktbladd CONSTRAINT chkrowcount CHECK (dbo. CHECKFNCTN () >= 1); The CHECK constraint that GO adds specifies that the table checktbl must contain at least one row. However, the ALTER table statement succeeds because the table does not contain any rows that can be used to check the condition of this constraint.

The CHECK constraint is not validated when the DELETE statement is executed. Therefore, using a specific type of CHECK constraint to execute a DELETE statement on a table can produce unexpected results. For example, suppose you execute the following statement against a table checktbl.

INSERT into Checktbl VALUES (10, 10)

GO

DELETE checktbl WHERE col1 = 10;

The DELETE statement succeeds even if the CHECK constraint specifies that the table checktbl must contain at least 1 rows.

2. PRIMARY KEY constraint

A PRIMARY KEY constraint defines a primary key in a table to uniquely determine the identifier of each row of data in a table. (Non-null, unique) For example: altertablememberaddconstraintpk_member_member_noprimarykeyclustered ( MEMBER_NO) The primary key column has an unlimited data type, but this column must be unique and not empty. If the table already has a row with a primary key of 1000, you can no longer add a primary key of 1000. You can also set the primary key column to auto-grow columns when manual or program control is not good.

The role of the primary key

A primary key is a flag that identifies a unique row in a table (there are other methods that represent unique rows, such as unique columns).

Primary keys are used to query monotonic data, modify monotonic data, and delete monotonous data.

In general, the table's primary key is set to the self-increment column of the int type, so that it is easy to distinguish the data when programming.

3. Unique constraint

UNIQUE constraint SQL implementation

The unique constraint guarantees that the data in a field or set of fields is unique compared to the data of other rows in the table.

Create a UNIQUE constraint

In Server Explorer, select the table to which you want to add the unique constraint, and then click Open Table Definition from the Database menu.

The table opens in the Table Designer.

From the Table Designer menu, click Indexes/Keys.

In the Indexes/Keys dialog box, click Add.

Click Type in the grid, and then select Unique key from the drop-down list box to the right of the property.

When you save the table, the unique constraint is created in the database.

Creating a unique constraint ensures that no duplicate values are entered in a particular column that does not participate in the primary key. When both unique and primary keys enforce uniqueness, a unique constraint, instead of a primary key constraint, should be attached to the table if the following conditions are true:

1. You want to enforce uniqueness in the combination of columns or columns. You can attach multiple unique constraints to a table, but you can attach only one primary KEY constraint to the table.

2. You want to enforce uniqueness in columns that allow null values. You can attach a unique constraint to a column that allows null values, but you can only attach a PRIMARY KEY constraint to a column that does not allow null values. When attaching a unique constraint to a column that allows null values, make sure that a maximum of one row in the constrained column contains a null value.

A unique constraint is used for the field, which can be used when the main surviving database

4. Foreign key

If the public keyword is the primary key in a relationship, then this common keyword is called the foreign key of the other relationship. Thus, the foreign key represents the correlation between the two relationships. A table with a foreign key of another relationship as the primary key is called the primary table, and a table with a key in it is called the primary table from the table. Foreign keys are also referred to as foreign keywords.

Foreign key (Foreign key)

In other words, if a property set in relationship mode R is not the primary key of R, but the primary key of another relationship R1, the attribute set is the foreign key of the relational mode R, which is usually abbreviated as FK in the database design.

Role

Maintain data consistency, integrity, the primary purpose is to control the data stored in the Foreign key table. Causes the two tables to be associated, and the foreign key can only refer to the values of the columns in the table or use null values.

Soft Road Quotations

A foreign key refers to a primary key in another table.

Database Fundamentals-(SQL Server) constraints