CREATE table in database (including create primary key, foreign key, non-empty column, unique)

CREATE TABLE (including create primary key, foreign key, non-empty column, unique)-PROS-Blog Park http://www.cnblogs.com/CodingArt/articles/1621921.html

****************

Create primary key (three methods)

****************

To create a student table:

The first type:

CREATE TABLE Student

(Sno char (5) primary key,/* Study number *//* You can specify the primary key directly */

Sname char () not null,/* name */

Ssex char (3) Not null,/* sex */

Sage integer Not null,/* age */

Sdept char (15); *//department */

The second type:

CREATE TABLE Student

(Sno char (5) NOT NULL,

Constraint pk_student/* You can specify the primary key name */

Primary KEY (SNO),

Sname char () NOT NULL,/* Non-empty, non-repeatable */

Ssex char (3) NOT NULL,

Sage Integer,

Sdept char (15));

The third type:

To create a curriculum:

CREATE TABLE Course

(CNO char (5),/* Course number */

CNAME char (a) NOT null unique,/* course name, non-empty, not repeatable */

Cpno char (5),/* Pre-course number (required before learning this lesson) */

Ccredit number); */* Credits */

Set the primary key by modifying the table.

ALTER TABLE Course

Add Constraint Pk_course

Primary key (CNO);

*************

Create a foreign key (3 methods)

*************

Create a "student-course-score" table

CREATE TABLE SC

(Sno char (5) constraint fk_student/* First, written in the attribute definition */* can specify the foreign key name, */

References student (SNO),

CNO Char (5),

Foreign key (CNO)/* Can omit foreign key name */

References Course (CNO),

Grade number);

**********

The second Kind

**********

CREATE TABLE SC

(Sno char (5),

Constraint fk_student/* The second type, written outside the attribute definition */

Foreign KEY (SNO)

References student (SNO),

CNO Char (5),

Foreign KEY (CNO)

References Course (CNO),

Grade number);

***********

Third Kind

***********

ALTER TABLE SC

Add Constraint Fk_student

Foreign KEY (SNO)

References student (SNO);

****************************

After you've built the table, add or modify, delete the constraint.

****************************

Use this statement to view all constraints on a table first:

Select Table_name,constraint_name,search_condition,status from User_constraints WHERE table_name=upper (' &TABLE_ Name ');

0. Add NOT NULL constraint

ALTER TABLE student modify sname NOT null;

1. Modify the null constraint

ALTER TABLE student Modify Sname null;/* name can be empty */

2. Increase the PRIMARY KEY constraint

ALTER TABLE student

Add Constraint Pk_student

Primary key (ID);

3. Delete a PRIMARY KEY constraint

Use this statement to view all constraints on a table first:

Select Table_name,constraint_name,search_condition,status from User_constraints WHERE table_name=upper (' &TABLE_ Name ');

This is to remove the constraint "fk_student".

ALTER TABLE drop constraint fk_student;

4. Add FOREIGN KEY constraints

ALTER TABLE SC

Add Constraint Fk_student

Foreign KEY (SNO)

References student (SNO);

5. Delete a FOREIGN KEY constraint

You need to know the name of the foreign key you want to delete.

Use this statement to view all constraints on a table first:

Select Table_name,constraint_name,search_condition,status from User_constraints WHERE table_name=upper (' &TABLE_ Name ');

ALTER TABLE drop constraint fk_student;

6. Add a UNIQUE constraint

ALTER TABLE course modify CNAME unique;

When you add a unique, an index of the same name is created automatically.

7. Cancellation of Unique

ALTER TABLE Course

Drop constraint sys_c005472;

8. Deleting an index

Find the name of the index to delete

Run this statement:

Select Index_name,table_owner,table_name,tablespace_name,status from User_indexes ORDER by TABLE_NAME;

And then run

Drop index sys_coo5472;

Today on a table, for one of the properties, create a new unique constraint,

But tip: Ora-02261:such unique or primary key already exists in the table

The reason is that the property is already primary key.

If the attribute already has a unique constraint, the property cannot be assigned the PRIMARY KEY constraint.

1. A table can have only one primary key, with multiple unique constraints

2. When creating primary key, an index is created at the same time, with the same name as the primary key.

3. When a property is primary key, a NOT NULL constraint is added by default, and the NOT NULL property is canceled when primary key is deleted.

4. A property of a table can set not NULL and PRIMARY KEY constraints at the same time.

2. This creates a new table, where the ID is the column to be set as the primary key.

   The code to create the table is as follows:

   CREATE TABLE Test_tab

   (

   ID number,

   Name VARCHAR2 (30)

   )

4. To set the primary key, you need to use the ALTER TABLE syntax here.

   The code for setting the primary key is as follows:

   ALTER TABLE test_tab ADD CONSTRAINT pk_test_tab PRIMARY key (ID);

6. Here is the explanation:

   ALTER TABLE table_name-----------------------mean to change a table

   Add constraint constraint_name-------------means to constrain/constrain a table

   Primary KEY (column_name)------------------description is the constraint of the primary key, and it is a column.

8. After the implementation of the setup was successful, if not rest assured that you can test it yourself.

   Execute the following code once:

   INSERT into Test_tab (id,name) VALUES (1, ' Test ');

   See already insert success!

10. Once in a repeating execution:

    INSERT into Test_tab (id,name) VALUES (1, ' Test ');

    There is an error at this time:

    Ora-00001:unique constraint (SCOTT. Pk_test_tab) violated

12. OK, the primary key of the table is set up successfully!

    How to set the primary key in the CREATE TABLE statement-CSDN Forum http://bbs.csdn.net/topics/90208943

CREATE table in database (including create primary key, foreign key, non-empty column, unique)