Shulou(Shulou.com)06/02 Report--

Editor to share with you how to use the CONSTRAINT constraint in SQL grammar, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

CONSTRAINT constraint, that is, to constrain the data in the database table to ensure the integrity and validity of the data record.

It is more common to add constraints when creating tables, but in order to organize the records, all the usages are written down here, but you follow the most common key points.

1. Primary key constraint primary key# primary key constraint primary key# after adding primary key the system defaults to the corresponding column adding index to improve retrieval speed # method 1 (single column primary key): id int PRIMARY KEY / / method 1 is to define fields after creating a table method 2 is to create all defined fields after method 2 (single | multi-column primary key): CONSTRAINT pk1 PRIMARY KEY (name) DeptId) / / CONSTRAINT pk1 can omit pk1 is an index name can take # method 3 (add after creating table): ALTER TABLE emp1 ADD PRIMARY KEY (eid) / / can be single-column or multi-column # delete primary key # ALTER TABLE emp1 DROP PRIMARY KEY / / when each table has only one primary key, regardless of single-column primary key or joint primary key # the column whose primary key function is a primary key constraint is unique and not empty 2. Self-growing constraint auto_increment# self-growing constraint auto_increment# is generally used to implement the self-growing constraint of primary keys. Users no longer need to assign values to their databases automatically. By default, when creating tables starting from 1 # id int PRIMARY KEY auto_increment#, you can specify the self-growing initial values / * CREATE TABLE IF NOT EXISTS mydb1.employee (id int, name varchar (20), gender varchar (10), salary double) auto_increment=100. * / # after creating the table, you can add the initial value of self-growth # ALTER TABLE emp2 auto_increment=100#delete, then default self-growth starts from breakpoint # truncate, and then default self-growth starts from 1 (with or without initial value) 3. Non-empty constraint not null# non-empty constraint not null / / the real empty constraint is NULL instead of 'NULL' or' # add constraint # method 1: name varchar (20) not null# method 2: ALTER TABLE emp3 MODIFY name varchar (20) not null (add after creating the table) # delete constraint # ALTER TABLE emp3 MODIFY name varchar (20) 4. Unique constraint unique# unique constraint unique# add constraint / / in mysql NULL is different from any value including its own # method 1: name varchar (20) unique# method 2: ALTER TABLE emp4 ADD CONSTRAINT unique_pn unique (name) (add after creating table) # Delete constraint # ALTER TABLE emp4 DROP INDEX unique_pn / / unique_pn is the constraint name if there is no name, the default is the column name used. Default constraint default# default constraint default# add constraint # method 1: address varchar (20) DEFAULT 'Beijing' # method 2: ALTER TABLE emp5 MODIFY address varchar (20) DEFAULT 'Beijing' (add after creating table) # Delete constraint # ALTER TABLE emp5 MODIFY address varchar (20) DEFAULT NULL6. Zero fill constraint zerofill# Zero fill constraint zerofill (display effect) just add constraint # address varchar (20) zerofill# delete constraint # ALTER TABLE emp6 MODIFY id int above is all the content of this article "how to use CONSTRAINT constraint in SQL Grammar". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.