Data integrity constraints in SQL

PRIMARY KEY is a set of fields (1 or more), the values of which form a unique combination and are used to uniquely identify a record in the table. Only one such constraint can be created for a table. This constraint is used to enforce the integrity of the entity that is described by the table.

CHECK is used to restrict the set of values that can be placed in a given column. This constraint is used to ensure the integrity of the domain, which is described by the tables in the database.

UNIQUE ensures that there are no duplicates in a column or set of columns.

FOREIGN KEY protects against actions that could break relationships between tables. FOREIGN KEY in one table points to PRIMARY KEY in another. Therefore, this restriction is intended to ensure that there are no FOREIGN KEY entries that do not correspond to PRIMARY KEY entries.

Differences between PRIMARY and UNIQUE constraints

By default, the PRIMARY constraint creates a clustered index on a column, and UNIQUE creates a nonclustered index. Another difference is that PRIMARY does not allow NULL records, while UNIQUE allows one (and in some DBMSs several) NULL records.

Can a value in a column that has a FOREIGN KEY constraint be NULL?

Maybe, if the given column does not have a NOT NULL constraint.

Create an index

The index can be created either with the CREATE INDEX statement:

CREATE INDEX index_name ON table_name (column_name)

or by specifying an integrity constraint as a unique UNIQUE or primary PRIMARY key in the CREATE TABLE statement.

MERGE statement

MERGE allows you to merge data from one table with data from another table. When tables are merged, the condition is checked, and if it is true, then an UPDATE is executed, and if not, an INSERT. At the same time, it is impossible to change the table fields in the UPDATE section, which are used to link two tables.

Difference between DELETE and TRUNCATE statements

DELETE - DML statement, deletes records from the table that meet the WHERE criterion, while triggers, constraints, etc. are involved.

TRUNCATE - DDL statement, deletes a table and creates it anew. Moreover, if there are FOREGIN KEY references to this table or the table is used in replication, then such a table cannot be recreated.

Read also:


Comments

Post a Comment

Popular posts from this blog

Methods for reading XML in Java

Image
DOM (Document Object Model) - object - reads XML, recreating it in memory in the form of an object structure, while the XML document is represented as a set of tags - nodes. Each node can have an unlimited number of child nodes. Each child can also contain several levels of descendants or none at all. Thus, the result is a kind of tree. Pluses: Easy to program. If there are many objects in XML with cross-references to each other, it is enough to go through the document twice: first time create objects without references and fill in the name-object dictionary, the second time - restore links. If an error occurs in XML, a half-created XML structure remains in memory, which will be automatically destroyed. Suitable for both reading and writing. Minuses: Low speed. Wastes a lot of memory. SAX (Simple API for XML) - event-driven - reads an XML document, responding to emerging events (opening or closing tag, string, attribute) by calling the event handlers provided by the appl...
Read more

XML, well-formed XML and valid XML

Image
XML, the eXtensible Markup Language, is a language with a simple formal syntax, well suited for creating and manipulating documents with programs, and at the same time easy to read and create documents by humans. XML is extensible, it does not fix the markup used in documents, and the developer is free to create markup in accordance with the needs of a particular area, being limited only by the syntax rules of the language. DTD A DTD, Document Type Definition, is a predefined set of rules that defines the relationships between elements and attributes. For example, the DTD for HTML says that the DIV tag must be inside the BODY tag and can appear multiple times, TITLE - in HEAD and only once, and SCRIPT - both there and there as many times as you want. A DTD is usually described directly in the document as a formulation line starting with <!DOCTYPE ... > or in a separate file. Difference between well-formed XML and valid XML Depending on the level of compliance, the document...
Read more

ArrayList and LinkedList in Java, memory usage and speed

Image
How much extra memory is needed when calling ArrayList.add()? If there is enough space in the array to accommodate a new element, then no additional memory is required. Otherwise, a new array is created, 1.5 times larger than the existing one (this is true for JDK above 1.7, in earlier versions the size of the increase is different). How much extra memory is allocated when you call LinkedList.add()? One new instance of the nested Node class is created. How much memory to store one byte primitive in LinkedList? Each LinkedList element stores a link to the previous element, the next element, and a data link. private static class Node<E> { E item; Node<E> next; Node<E> prev; //... } For 32-bit systems, each link is 32 bits (4 bytes). The object (header) of the nested Node class itself takes 8 bytes. 4 + 4 + 4 + 8 = 20 bytes, and since the size of each object in Java is a multiple of 8, so we get 24 bytes. A byte primitive occupies 1 by...
Read more