arizaljava.blogspot.my

MySQL CREATE TABLE with CHECK CONSTRAINT using IN operator MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with IN operator. Example If you want to create a table 'newauthor' with a PRIMARY KEY on a combination of two columns (aut_id,home_city) and checking a limit value for the column country are 'USA','UK' and 'India', the following statement can be used. CREATE TABLE IF NOT EXISTS newauthor ( aut_id varchar ( 8 ) NOT NULL , aut_name varchar ( 50 ) NOT NULL , country varchar ( 25 ) NOT NULL CHECK ( country IN ( 'USA' , 'UK' , 'India' ) ) , home_city varchar ( 25 ) NOT NULL , PRIMARY KEY ( aut_id , home_city ) ) ; Resource:https://www.w3resource.com/mysql/creating-table-advance/constraint.php

Add primary key constraints ALTER   TABLE  Persons ADD   PRIMARY   KEY  (ID); Add not null constraints ALTER TABLE table_name MODIFY column_name datatype NOT NULL; Resource: https://www.tutorialspoint.com/sql/sql-alter-command.htm

Key: (Part of) Primary Key Filled Diamond: NOT NULL Not filled Diamond: NULL Red colored: (Part of) Foreign key Blue lined Diamond: Simple attribute (no key) Can be combined for example:  is a Red colored Key so it's a Primary Key which is also a Foreign Key  is a Yellow (non Red) Key so it's only a Primary Key  is a blue lined filled diamond so it's a NOT NULL simple attribute  is a red colored filled diamond so it's a NOT NULL Foreign Key  is a blue lined not filled diamond so it's a simple attribute which can be NULL  is a red colored not filled diamond so it's a Foreign Key which can be NULL Resource: https://stackoverflow.com/questions/10778561/what-do-the-mysql-workbench-column-icons-mean/28859886#28859886

CREATE   TABLE   table_name  (     column1 datatype   constraint ,     column2 datatype   constraint ,     column3 datatype   constraint ,     .... ); Resource:  https://www.w3schools.com/sql/sql_constraints.asp

Create Table syntax CREATE   TABLE   table_name  (     column1 datatype ,     column2 datatype ,     column3 datatype ,    .... ); Example CREATE   TABLE  Persons (     PersonID int,     LastName varchar( 255 ),     FirstName varchar( 255 ),     Address varchar( 255 ),     City varchar( 255 )  ); Resource:  https://www.w3schools.com/sql/sql_create_table.asp