How to create constraints in Oracle

How to create constraints in Oracle, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Several ways for Oracle to create primary key, foreign key and check constraints: (write by RFH)

1. Create constraints when you create a table:

/ / add primary key constraint

Create table userinfo (userid number (20) constraint pk_user primary key, uname varchar (20))

Create table userinfo (userid number (20), unamevarchar (20), constraint pk_user primary key (userid))

/ / add a foreign key constraint

Create table userinfo (useridnumber (20), deptid number (20) referencesdept (deptid))

Create table userinfo (useridnumber (20), deptid number (20), constraint fk_dept foreign key (deptid) references dept (deptid))

/ / add check constraint

Create table userinfo (useridnumber (20), age number (3) constraint ck_age check (age10))

Create table userinfo (useridnumber (20), age number (3), constraint ck_age check (age10))

two。 If you have finished building the table and forgot to add constraints, it doesn't matter, you can add it separately:

/ / add primary key constraint:

Alter table userinfo add constraintpk_user primary key (userid)

/ / add a foreign key constraint:

Alter table userinfo add constraint fk_dept foreign key (deptid) references dept (deptid)

/ / add check constraint

Alter table userinfo add constraint ck_age check (age10)

3 query for constraints in the table:

Desc userinfo

All_constraints contains the constraint information of the table, but it will be scanned.

We use the user_constrains table to obtain the constraints of the current user:

Select constraint_name,table_name,constraint_type from user_constraints

If we want to look at the constraints of a table:

Select constraint_name,table_name,constraint_type from user_constraints where table_name='TABLENAME';//, note that table names should be capitalized.

Similarly, index table USER_INDEXES, sequence table USER_SEQUENCES, trigger table USER_TRIGGERS, stored procedure table USER_PROCEDURES, and, of course, USER_TABLES,VIEW_TABLES, etc. There is a surprising discovery that previously built foreign keys will be added to the index table. They generally have corresponding fields table_name, trigger_name, and so on.

4. We can already create and query constraints ourselves, so what if we want to delete useless constraints?

/ / Delete the primary key

Alter table dept drop primary key

Or

Alter table userinfo drop constraint pk_user

/ / Delete check constraint

Alter table userinfo drop constraint ck_age

/ / Delete foreign key constraint

Alter table userinfo drop constraint fk_dept

Use the alter table statement: (for reference only)

Complete usage:

ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification]... Alter_specification: table_option...

| | ADD [COLUMN] column_definition [FIRST |

| | AFTER col_name]

| | ADD [COLUMN] (column_definition,...) |

| | ADD {INDEX | KEY} [index_name] [index_type] (index_col_name,...) |

| | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

| | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name,...)

| | ADD [FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (index_col_name,...) |

| | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition]

| | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} |

| | CHANGE [COLUMN] old_col_name column_definition [FIRST | AFTERcol_name] |

| | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name] |

| | DROP [COLUMN] col_name |

| | DROP PRIMARY KEY |

| | DROP {INDEX | KEY} index_name |

| | DROP FOREIGN KEYfk_symbol |

| | DISABLE KEYS | ENABLE KEYS | RENAME [TO] new_tbl_name |

| ORDER BY col_name [, col_name].

| | CONVERT TO CHARACTERSET charset_name [COLLATE collation_name] |

| | [DEFAULT] CHARACTERSET charset_name [COLLATE collation_name] |

| | DISCARD TABLESPACE |

| | IMPORT TABLESPACE |

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.