Menu
I would like to restrict a column value in a SQL table. For example, the column values can only be "car" or "bike" or "van". My question is how do you achieve this in SQL, and is it a good idea to do this on the DB side or should I let the application restrict the input.
I also have the intention to add or remove more values in the future, for example, "truck".
The type of Databases I am using are SQLite and MySQL.
461
The syntax for creating a unique constraint using an ALTER TABLE statement in MySQL is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.
Security and Ease of Setup – SQLite vs MySQL SQLite does not have an inbuilt authentication mechanism. The database files can be accessed by anyone. However, MySQL comes with a lot of inbuilt security features.
Here I want to create 2 CHECK constraint before the record insert to the database. ALTER TABLE SubjectEnrollment ADD CONSTRAINT register CHECK (register <= classSize AND register >=0), ADD CONSTRAINT available CHECK (available <= classSize AND available >= 0);
Add a new table containing these means of transport, and make your column a foreign key to that table. New means of transport can be added to the table in future, and your column definition remains the same.
With this construction, I would definitively choose to regulate this at the DB level, rather than that of the application.
88
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
