In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-09-20 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
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.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
The market share of Chrome browser on the desktop has exceeded 70%, and users are complaining about
The world's first 2nm mobile chip: Samsung Exynos 2600 is ready for mass production.According to a r
A US federal judge has ruled that Google can keep its Chrome browser, but it will be prohibited from
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
About us Contact us Product review car news thenatureplanet
More Form oMedia: AutoTimes. Bestcoffee. SL News. Jarebook. Coffee Hunters. Sundaily. Modezone. NNB. Coffee. Game News. FrontStreet. GGAMEN
© 2024 shulou.com SLNews company. All rights reserved.