1. Add primary key
alter table goods add primary key(sid);
2. Add a foreign key
alter table orders add foreign key(goods_id) references goods(sid) on update cascade on delete cascade;
on update cascade: When the referenced row is updated, the referenced row is automatically updated;
on update restrict: The referenced row is prohibited from being updated;
on delete cascade: When the referenced row is deleted, the referenced rows are also deleted together;
on dellete restrict: The referenced line is prohibited from deletion;
3. Delete foreign keys
alter table orders drop constraint orders_goods_id_fkey;
4. Add unique constraints
alter table goods add constraint unique_goods_sid unique(sid);
5. Delete the default value
alter table goods alter column sid drop default;
6. Modify the data type of the field
alter table goods alter column sid type character varying;
7. Rename the field
alter table goods rename column sid to ssid;
Supplementary: The difference between PostgreSQL-Primary Key Constraint and Unique Constraint
I encountered some problems during the index building process, so I searched for basic knowledge on the Internet. I always thought that the only index was the primary key, at least in pg that's not the case.
1) Primary key constraint (PRIMARY KEY)
1) Primary key is used to uniquely identify each record in the table, and one or more columns can be defined as primary keys.
2) It is impossible (or difficult) to update.
3) No two rows on the primary key column have the same value (i.e., repeated values), and empty (NULL) is not allowed.
4) The main body can be used as external body, but the unique index cannot be used.
2) Unique constraints (UNIQUE)
1) Uniqueness constraints are used to limit the uniqueness of data on columns that are not subject to primary key constraints. They are used as an optional means to access a certain row. Multiple uniqueness constraints can be placed on a table.
2) It can be updated as long as it is unique.
3) That is, no two rows in the table are allowed to have the same value on the specified column, and are allowed to be empty (NULL).
4) Multiple unique constraints can be placed on a table.
3) Unique index (INDEX)
Creating a unique index ensures that any attempt to generate duplicate key values will fail.
The difference between uniqueness constraints and primary key constraints:
(1) The unique constraint allows NULL values to exist on this column, while the limitation of primary key constraints is more stringent. Not only does it not allow duplication, but it also does not allow null values.
(2) Clustered and nonclustered indexes can be created when creating uniqueness constraints and primary key constraints, but by default primary key constraints produce clustered indexes, while unique constraints produce nonclustered indexes
Constraints and indexes, the former is used to check the correctness of data, and the latter is used to optimize data query, with different purposes.
Uniqueness constraints differ from unique indexes:
(1) Creating a unique constraint will create a Constraint in Oracle, and also create a unique index corresponding to the constraint.
(2) Creating a unique index will only create a unique index and will not create a Constraint.
In other words, the only constraints are actually achieved by creating a unique index.
There are certain differences between the two when deleting:
When deleting a unique constraint, you can only delete the constraint without deleting the corresponding index, so the corresponding column must be unique. If the unique index is deleted, you can insert an inunique value.
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.