{"id":1542,"date":"2024-01-04T13:47:04","date_gmt":"2024-01-04T05:47:04","guid":{"rendered":"https:\/\/www.ruianding.com\/blog\/?p=1542"},"modified":"2024-01-04T14:36:58","modified_gmt":"2024-01-04T06:36:58","slug":"mysql-002-tables-keys","status":"publish","type":"post","link":"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/","title":{"rendered":"MySQL-002 Tables & Keys"},"content":{"rendered":"\n

Tables<\/h3>\n\n\n\n

Definition<\/strong>: A table in an SQL database is a collection of related data entries and it consists of columns and rows. Tables are used to hold information about the objects that the database is designed to track.<\/p>\n\n\n\n

EmployeeID<\/th>Name<\/th>Position<\/th>Department<\/th><\/tr><\/thead>
E1001<\/td>Alice<\/td>Salesperson<\/td>Design<\/td><\/tr>
E1002<\/td>Fiona<\/td>Designer<\/td>HR<\/td><\/tr>
E1003<\/td>Charlie<\/td>Engineer<\/td>Admin<\/td><\/tr>
E1004<\/td>Julia<\/td>HR<\/td>Management<\/td><\/tr>
E1005<\/td>George<\/td>Technician<\/td>Tech Support<\/td><\/tr>
E1006<\/td>Ian<\/td>Clerk<\/td>Engineering<\/td><\/tr>
E1007<\/td>Eva<\/td>Accountant<\/td>Sales<\/td><\/tr>
E1008<\/td>Bob<\/td>Marketer<\/td>Marketing<\/td><\/tr>
E1009<\/td>Hannah<\/td>Analyst<\/td>IT<\/td><\/tr>
E1010<\/td>David<\/td>Manager<\/td>Finance<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Columns<\/strong>: Each column in a table represents a specific attribute or field of data. For example, in a table designed to store information about employees, the columns might include EmployeeID<\/code>, Name<\/code>, Position<\/code>, Department<\/code>, etc.<\/p>\n\n\n\n

Rows<\/strong>: Each row in a table represents a single record. Continuing with the employee example, each row would contain the specific details for a single employee, such as their unique ID, name, position, and department.<\/p>\n\n\n\n

\n\n\n\n

Keys<\/h3>\n\n\n\n

Primary Key<\/strong>: Tables often have a primary key<\/strong>, which is a unique identifier<\/strong> for each record (row). This could be a single column (like EmployeeID<\/code>) or a combination of columns. The primary key ensures that each record in the table is unique.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
    \n
  • Surrogate Key<\/strong>: A surrogate key is not derived from the data. In other words, it has no mapping to anything in the real word. It’s typically an artificial key that has no business meaning. For instance, it could be an auto-incrementing number, a globally unique identifier (GUID), or a random unique value.<\/li>\n\n\n\n
  • Natural Key:<\/strong> A natural key is based on one or more pre-existing data attributes naturally found within the dataset. For example, a Social Security Number (SSN) for individuals in the U.S., or a VIN (Vehicle Identification Number) for cars, can be considered natural keys.<\/li>\n<\/ul>\n\n\n\n

    Natural keys contrast with surrogate keys, which are artificially created for the sole purpose of uniquely identifying records in a table and do not have any inherent business meaning. The choice between using a natural key or a surrogate key depends on various factors like the stability of the data, the database schema, and the specific requirements of the application.<\/p>\n\n\n\n

    Foreign Keys<\/strong>: Tables can also have foreign keys, which are columns that link to the primary key of another table, establishing a relationship between the two tables.<\/p>\n\n\n\n

    Data Integrity<\/strong>: Tables enforce data integrity through constraints. These can include primary keys, foreign keys, unique constraints, and check constraints, ensuring the accuracy and consistency of data.<\/p>\n\n\n\n

    Schema<\/strong>: Tables are defined with a schema that dictates the structure of the table – the columns, their data types, and any constraints on the data.<\/p>\n\n\n\n

    Indexing<\/strong>: Tables can have indexes, which are used to speed up the retrieval of rows. An index is created on one or more columns of the table.<\/p>\n\n\n\n

    Relationships<\/strong>: Tables can be related to each other. These relationships are central to the relational database model and can be one-to-one, one-to-many, or many-to-many.<\/p>\n","protected":false},"excerpt":{"rendered":"

    Tables Definition: A table in an SQL database is a collection of related data entries and it consists of columns and rows. Tables are used to hold information about the objects that the database is designed to track. EmployeeID Name Position Department E1001 Alice Salesperson Design E1002 Fiona Designer HR E1003 Charlie Engineer Admin E1004 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[51],"tags":[],"class_list":["post-1542","post","type-post","status-publish","format-standard","hentry","category-mysql"],"yoast_head":"\nMySQL-002 Tables & Keys - \u6781\u7b80IT\uff5cSimpleIT<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL-002 Tables & Keys - \u6781\u7b80IT\uff5cSimpleIT\" \/>\n<meta property=\"og:description\" content=\"Tables Definition: A table in an SQL database is a collection of related data entries and it consists of columns and rows. Tables are used to hold information about the objects that the database is designed to track. EmployeeID Name Position Department E1001 Alice Salesperson Design E1002 Fiona Designer HR E1003 Charlie Engineer Admin E1004 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/\" \/>\n<meta property=\"og:site_name\" content=\"\u6781\u7b80IT\uff5cSimpleIT\" \/>\n<meta property=\"article:published_time\" content=\"2024-01-04T05:47:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-01-04T06:36:58+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2024\/01\/image-3.png\" \/>\n\t<meta property=\"og:image:width\" content=\"926\" \/>\n\t<meta property=\"og:image:height\" content=\"561\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Ruian Ding\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ruian Ding\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/\"},\"author\":{\"name\":\"Ruian Ding\",\"@id\":\"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b\"},\"headline\":\"MySQL-002 Tables & Keys\",\"datePublished\":\"2024-01-04T05:47:04+00:00\",\"dateModified\":\"2024-01-04T06:36:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/\"},\"wordCount\":482,\"publisher\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b\"},\"image\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2024\/01\/image-3.png\",\"articleSection\":[\"MySQL\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/\",\"url\":\"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/\",\"name\":\"MySQL-002 Tables & Keys - \u6781\u7b80IT\uff5cSimpleIT\",\"isPartOf\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2024\/01\/image-3.png\",\"datePublished\":\"2024-01-04T05:47:04+00:00\",\"dateModified\":\"2024-01-04T06:36:58+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/#primaryimage\",\"url\":\"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2024\/01\/image-3.png\",\"contentUrl\":\"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2024\/01\/image-3.png\",\"width\":926,\"height\":561},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.ruianding.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL-002 Tables & Keys\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.ruianding.com\/blog\/#website\",\"url\":\"https:\/\/www.ruianding.com\/blog\/\",\"name\":\"Ruian's Tech Troubleshooting Toolbox\",\"description\":\"Debug the World.\",\"publisher\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b\"},\"alternateName\":\"\u4e01\u777f\u5b89\u7684\u6280\u672f\u5206\u4eab\u535a\u5ba2\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.ruianding.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b\",\"name\":\"Ruian Ding\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2023\/05\/logo.png\",\"contentUrl\":\"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2023\/05\/logo.png\",\"width\":284,\"height\":284,\"caption\":\"Ruian Ding\"},\"logo\":{\"@id\":\"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/image\/\"},\"description\":\"I am currently a Support Specialist at NIO, focusing on cloud-related issues for NIO Power. Previously, at Microsoft Entra ID, I specialized in identity and access management (IAM), including device registration, Windows Hello for Business (WHfB), multi-factor authentication (MFA), and single sign-on (SSO). In addition to my core expertise, I have a strong foundation in Active Directory, Servers, Cloud Computing, Network Administration, and Front-end Web Development. This diverse technical skill set enables me to effectively handle a wide range of challenges in a fast-paced IT environment.\",\"sameAs\":[\"https:\/\/www.ruianding.com\"],\"url\":\"https:\/\/www.ruianding.com\/blog\/author\/ruiand\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL-002 Tables & Keys - \u6781\u7b80IT\uff5cSimpleIT","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/","og_locale":"en_US","og_type":"article","og_title":"MySQL-002 Tables & Keys - \u6781\u7b80IT\uff5cSimpleIT","og_description":"Tables Definition: A table in an SQL database is a collection of related data entries and it consists of columns and rows. Tables are used to hold information about the objects that the database is designed to track. EmployeeID Name Position Department E1001 Alice Salesperson Design E1002 Fiona Designer HR E1003 Charlie Engineer Admin E1004 […]","og_url":"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/","og_site_name":"\u6781\u7b80IT\uff5cSimpleIT","article_published_time":"2024-01-04T05:47:04+00:00","article_modified_time":"2024-01-04T06:36:58+00:00","og_image":[{"width":926,"height":561,"url":"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2024\/01\/image-3.png","type":"image\/png"}],"author":"Ruian Ding","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Ruian Ding","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/#article","isPartOf":{"@id":"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/"},"author":{"name":"Ruian Ding","@id":"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b"},"headline":"MySQL-002 Tables & Keys","datePublished":"2024-01-04T05:47:04+00:00","dateModified":"2024-01-04T06:36:58+00:00","mainEntityOfPage":{"@id":"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/"},"wordCount":482,"publisher":{"@id":"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b"},"image":{"@id":"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/#primaryimage"},"thumbnailUrl":"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2024\/01\/image-3.png","articleSection":["MySQL"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/","url":"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/","name":"MySQL-002 Tables & Keys - \u6781\u7b80IT\uff5cSimpleIT","isPartOf":{"@id":"https:\/\/www.ruianding.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/#primaryimage"},"image":{"@id":"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/#primaryimage"},"thumbnailUrl":"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2024\/01\/image-3.png","datePublished":"2024-01-04T05:47:04+00:00","dateModified":"2024-01-04T06:36:58+00:00","breadcrumb":{"@id":"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/#primaryimage","url":"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2024\/01\/image-3.png","contentUrl":"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2024\/01\/image-3.png","width":926,"height":561},{"@type":"BreadcrumbList","@id":"https:\/\/www.ruianding.com\/blog\/mysql-002-tables-keys\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.ruianding.com\/blog\/"},{"@type":"ListItem","position":2,"name":"MySQL-002 Tables & Keys"}]},{"@type":"WebSite","@id":"https:\/\/www.ruianding.com\/blog\/#website","url":"https:\/\/www.ruianding.com\/blog\/","name":"Ruian's Tech Troubleshooting Toolbox","description":"Debug the World.","publisher":{"@id":"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b"},"alternateName":"\u4e01\u777f\u5b89\u7684\u6280\u672f\u5206\u4eab\u535a\u5ba2","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.ruianding.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/440d88575b7dc819a4cefc8c4199db3b","name":"Ruian Ding","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2023\/05\/logo.png","contentUrl":"https:\/\/www.ruianding.com\/blog\/wp-content\/uploads\/2023\/05\/logo.png","width":284,"height":284,"caption":"Ruian Ding"},"logo":{"@id":"https:\/\/www.ruianding.com\/blog\/#\/schema\/person\/image\/"},"description":"I am currently a Support Specialist at NIO, focusing on cloud-related issues for NIO Power. Previously, at Microsoft Entra ID, I specialized in identity and access management (IAM), including device registration, Windows Hello for Business (WHfB), multi-factor authentication (MFA), and single sign-on (SSO). In addition to my core expertise, I have a strong foundation in Active Directory, Servers, Cloud Computing, Network Administration, and Front-end Web Development. This diverse technical skill set enables me to effectively handle a wide range of challenges in a fast-paced IT environment.","sameAs":["https:\/\/www.ruianding.com"],"url":"https:\/\/www.ruianding.com\/blog\/author\/ruiand\/"}]}},"_links":{"self":[{"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/posts\/1542","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/comments?post=1542"}],"version-history":[{"count":5,"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/posts\/1542\/revisions"}],"predecessor-version":[{"id":1549,"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/posts\/1542\/revisions\/1549"}],"wp:attachment":[{"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/media?parent=1542"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/categories?post=1542"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ruianding.com\/blog\/wp-json\/wp\/v2\/tags?post=1542"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}