0009 "SQL must know" Note 05-Create and constrain a table

1. CREATE TABLE: Use the CREATE TABLE statement to indicate: Table name (cannot be duplicated with existing table name), column name, data type for each column

CREATE TABLEProduct (prod_idChar(Ten), vend_idChar(Ten), Prod_nameChar(254), Prod_price Number(8,2), Prod_descvarchar( +)  );

2, Constraints. A table in a database differs from Excel in that the data in its tables must be strictly legal, and constraints are rules that govern how database data is inserted or processed.

1. PRIMARY KEY constraint: Each data of the primary key column can uniquely identify the row in which it is located, the primary key value of any two rows is different, the primary key value cannot be empty, so each row has a primary key value, the primary key column is not updated and does not change, and the primary key can be created on one or more columns. Keyword: PRIMARY key

2. FOREIGN KEY constraint: The data for a FOREIGN KEY constraint column must be only the primary key column of another table (or can it be a unique constraint column?). ) in the data. For example, the cust_id of the Orders table must be the value in the cust_id column of the Customers table, and you cannot add a data in the orders.cust_id column that is not in the customers.cust_id column. Keyword: FOREIGN key

3. Unique constraint: Each data for a unique constraint column is unique, and any two rows are not the same. Difference from primary key: A table has only one primary KEY constraint, but there can be multiple unique constraints, a unique constraint can be a null value, a unique constraint column can be modified for reuse, and cannot be used to define a foreign key. For example, the ID card can be the only constraint (of course, there are two of the exact same ID number of the accident). Keyword: UNIQUE

4. Check constraints: Make the values of the CHECK Constraint column conform to a certain condition, such as purchase quantity >0; gender as "male" or "female". Keywords: CHECK (conditional)

5. Non-null constraint: Is NOT NULL, whether the value of a column is allowed to be empty, if not set, the default is null. Note the difference between null and '.

3. Create a constraint when creating a table:

CREATE TABLEProduct (prod_idChar(Ten) not NULL PRIMARY KEY, vend_idChar(Ten) not NULL, Prod_nameChar(254) not NULL, Prod_price Number(8,2) not NULL CHECK(Prod_price>0), Prod_descvarchar( +) not NULL,CONSTRAINTfor_vend_idFOREIGN KEY(vend_id)REFERENCESVendors (vend_id));

Or:

CREATE TABLEProduct (prod_idChar(Ten) not NULL, vend_idChar(Ten) not NULL, Prod_nameChar(254) not NULL, Prod_price Number(8,2) not NULL, Prod_descvarchar( +) not NULL,PRIMARY KEY(prod_id),CHECK(Prod_price>0),CONSTRAINTfor_vend_idFOREIGN KEY(vend_id)REFERENCESVendors (vend_id));

0009 "SQL must know" Note 05-Create and constrain a table